Counting characters

[LiAD]

Good morning,

I have a list of inputs which are mixed between text, spaces, numbers and
symbols and i would like a function to count the number of letters in a
string.

fred smith+346785 result required - 10
(counts from f to t)
jonathon tate 56789321 - 13
(j to e)
bill +21988762 - 4

What is the best function to use for this?

Thanks
LiAD

 

[Gary''s Student]

Try this UDF:

Public Function AlphaCount(rng As Range) As Integer
Dim sStr As String, i As Long, sStr1 As String
Dim sChar As String
AlphaCount = 0
sStr = rng.Value
For i = 1 To Len(sStr)
sChar = Mid(sStr, i, 1)
If sChar Like "[a-z]" Then
AlphaCount = AlphaCount + 1
End If
Next
End Function

 

[Mike H]

Try

=LEN(TRIM((LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9,"+"},A1&"0123456789+"))-1))))

Mike

 

[Ron Rosenfeld]

Good morning,

I have a list of inputs which are mixed between text, spaces, numbers and
symbols and i would like a function to count the number of letters in a
string.

fred smith+346785 result required - 10
(counts from f to t)
jonathon tate 56789321 - 13
(j to e)
bill +21988762 - 4

What is the best function to use for this?

Thanks
LiAD

There are some inconsistencies in your descriptions.

It seems you also want to count the number of spaces if they are between two
strings of letters. Otherwise your first two counts would be incorrect.

It is not clear why, with fred smith, you only want to count from f to t, and
not from f to s. If you count from f to s, and include the two spaces between
fred and smit, then your result of 10 is correct. But why are you omitting the
"h" (i.e. what rule are you using).

If the two spaces between fred and smith is a typo, and if the exclusion of the
"h" at the end of smith is also a typo, and if the letters will always precede
the non-letters, then Mike H.'s formula solution will work OK.

Gary's student's solution will work if you really only wanted to count letters
(e.g. [A-Za-z])

If your requirements are different, then you should clarify your question.
--ron

 

[Shane Devenshire]

Hi,

And if your input looks like "Fred 123 Smith" or "Fred Smith 123 - John
Adams" or "1234 - Shane - 56789" or "Shane - 12134"? How do you want to
handle it. Or do you know this never happens.

For handling the last one above, that is a special charactor other than just
+ you can use this array formula

=MAX((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>64)*ROW(INDIRECT("1:"&LEN(A1))))

This will not handle all other "special" characters, but will handle some.
If you know that the name always ends with a lowercase letter you can change
the 64 to 96 and handle a few other special characters. - To make it an array
press Shift+Ctrl+Enter to enter it.

 

[Shane Devenshire]

Hi,

For what its worth you can shorten Mike's formula to

=LEN(TRIM((LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9,"+"},A1&"0123456789+"))-1))))

because case sensitivity is not important in a non-alpha search.