How to count text characters from a single cell

[ThrottleWorks]

Hi,

I have cell A1, value is ABC12, I want to count the text characters from this cell.
In the example the anwser will be 3.

Some more examples for the reference.

AB1 = 2
ABC1 = 3
A123 = 1

I tried searching Chandoo & Google for the formula but not able to find.
I remember reading it somewhere but cannot find now .

Can anyone please help me in this.

 

[lohithsriram]

Hi Sachin,

This is purely not mine but have modified the formula built by some brilliance (http://office.microsoft.com/en-us/e...rs-from-alphanumeric-strings-HA001154901.aspx).

This works for your need, but am sure you will get much more simpler & better formulas from all the fellow forum mates. Till then, use this.

Array formula, use it with Ctrl +Shift +Enter
=LEN(A1)-LEN(1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1))))

 

[ThrottleWorks]

Hi Sriram, thanks a lot for the help, this is brllliant formula.

Have a nice day ahead.

P.S. - Though this is not a problem for me, just wanted to share an observation.
Might help others who will read this post.

The formula will work for values such as, sa12, a1, aa1, 1aa.
But it will not work for a1a1, 1a1a.

 

[Xiq]

Hi,

Here is a non-array entry formula + should work for any length of text-string:

=LEN(A1)-SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(OFFSET(A$1,,,LEN(A1))),1)))

 

[ThrottleWorks]

Hi Xiq, thanks a lot for the help, have a nice day ahead, will share the results ASAP.

 

[lohithsriram]

Hello All,
Can someone improvise the above formula to calculate the number or text characters in the cell placed random or repeatedly like

AB1BA = 4
AB2C1 = 3
A1C2B3 = 3

And also formula to extract unique alphabet count like :
AC1BA = 3 (A is repeated )
A2CAAC1 = 2 (A, C is repeated)
Though its not a requirement to be used for my work, but would love to see you guys smoking your brains. :D

 

[Xiq]

...And also formula to extract unique alphabet count like :
AC1BA = 3 (A is repeated )
A2CAAC1 = 2 (A, C is repeated)
Though its not a requirement to be used for my work, but would love to see you guys smoking your brains. :D

@lohithsriram, here you go:

=SUMPRODUCT(--ISNUMBER(FIND(CHAR(ROW($65:$90)),UPPER(A1))))

 

[ThrottleWorks]

Hi, my issue is 100% resolved by Sriram's formula.

Just sharing the results of different formulas with different combinations.

 

[Xiq]

Hi, my issue is 100% resolved by Sriram's formula.

Just sharing the results of different formulas with different combinations.

Ah, you didn't say anything about spaces

 

[ThrottleWorks]

Hi Xiq, thanks for checking the file, my original data did not have spaces, it is a1, a2, a3, a40.... and so on.

There are no spaces, it is only when I was checking the formulas I realized this combination.

 

[Faseeh]

Dear All,

This one is Array formula:

=IF(A1<>"",SUM(ISERROR(VALUE(MID(A1,ROW(A2:INDIRECT("A"&LEN(A1))),1)))*1),"..")

Thanks

 

[ThrottleWorks]

Hi Faseeh, thanks a lot, have a nice day ahead, will share the results ASAP.