Counting the number of letters in a cell

**beefycj5** · 06-01-2005, 03:13 PM

Can Excel count the numbers of letters in a cell that has mixed letters and numbers? Can you restrict =LEN to only letters (or only numbers)?

IE - ABC123
# of letters = 3

Thanks,
Adam

**MartinShort** · 06-01-2005, 03:20 PM

Are they always in the same format Adam? ie 3 letters, 3 numbers. If they are try a combination of the LEFT and LEN functions.

ie =LEN(LEFT(A1)) etc.

If the pattern is irregular, yes you can do it, but you would need to write a small macro. To help you, we would also need more precise rules. ie What would you want to extract if the cell contained A23BGF44?

Martin

**Ron Coderre** · 06-01-2005, 03:28 PM

See if this works for you:

=LEN(A1)-SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))

It should accommodate any combination of text and numbers.

Regards,
Ron