# Count number of entered data.

#### countryfan_nt

##### Well-known Member
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

### 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.

=LEN(A1)

Pekka

#### RAM

##### Well-known Member
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.

RAM

#### Pekkavee

##### Well-known Member
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.

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
Pekka

#### RAM

##### Well-known Member

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
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))

##### MrExcel MVP

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," ",""))

#### joelnichols

##### Active Member
Beat to the punch again by the gifted ones. Very nice.

#### Pekkavee

##### Well-known Member
=LEN(SUBSTITUTE(A1," ",""))

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

Thanks.

Pekka

Replies
6
Views
47
Replies
1
Views
33
Replies
10
Views
324
Replies
6
Views
50
Replies
7
Views
86