Count number of entered data.

countryfan_nt

Well-known Member
Joined
May 19, 2004
Messages
716
Hello friends,

I have Cell A1. I want to a formula that would count the number of letters/numbers entered. Is there such a thing?

Thanks,
Nawaf
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
Pekka,

What do you do if the is a space in-between two groups of letter of numbers, or any combination of letters and numbers?

Like: Pekka Vee. =LEN(A1) counts to 9. :eek:

RAM
 

Pekkavee

Well-known Member
Joined
May 25, 2004
Messages
1,218
RAM said:
Pekka,

What do you do if the is a space in-between two groups of letter of numbers, or any combination of letters and numbers?

Like: Pekka Vee. =LEN(A1) counts to 9. :eek:

RAM

I am a simple man, so I have simple solutions.

There are certainly better ways with VBA and so.

1. Make Data- text to columns - with a space as a delimiter. This way you get rid of spaces.
2. Then CONCATENATE these new column into one column
3. Then use LEN formula.
Työkirja1
ABCD
1PekkaVeePekkaVee8
Taul1


PITUUS = LEN
KETJUTA = CONCATENATE
Sorry for my finish Excel :confused:
Pekka
:eek: :eek: :eek:
 

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862

ADVERTISEMENT

Thanks Pekka,

I'm a simple man too and I don't the answer in my head either. The OP might not want to delete spaces and they might not be aligned to be removed with Data - Text to Columns in one try.

But I'm pretty sure I've seen the solution on this board before. We just need to wait until the right member enters.....

RAM
 

joelnichols

Active Member
Joined
Apr 13, 2004
Messages
384
This will subtract the 1st space, still working on how to do it if there are more than 1 spaces.

=LEN(A1)-COUNT(FIND(" ",A1,1))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

joelnichols said:
This will subtract the 1st space, still working on how to do it if there are more than 1 spaces.

=LEN(A1)-COUNT(FIND(" ",A1,1))

=LEN(SUBSTITUTE(A1," ",""))
 

Pekkavee

Well-known Member
Joined
May 25, 2004
Messages
1,218
Aladin Akyurek said:
=LEN(SUBSTITUTE(A1," ",""))

Nice Aladin :biggrin: :biggrin:

I didn't find SUBSTITUTE in my finish Excel.
It's cool to learn everyday something new.

Thanks. :biggrin:

Pekka
:eek: :eek: :eek:
 

Watch MrExcel Video

Forum statistics

Threads
1,118,289
Messages
5,571,323
Members
412,382
Latest member
Langtn02
Top