Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting characters
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting characters
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 -- Gary''s Student - gsnu200837 "LiAD" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting characters
Try
=LEN(TRIM((LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9 ,"+"},A1&"0123456789+"))-1)))) Mike "LiAD" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting characters
On Tue, 10 Mar 2009 01:12:02 -0700, LiAD
wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting characters
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "LiAD" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting characters
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "LiAD" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting characters in Excel | Excel Discussion (Misc queries) | |||
Counting Characters | Excel Worksheet Functions | |||
counting characters in a cell | Excel Discussion (Misc queries) | |||
Counting characters | Excel Discussion (Misc queries) | |||
counting characters in a line | Excel Discussion (Misc queries) |