Thread: Count specific character in cell

1. Count specific character in cell

I feel sure there's an easy way to do this but can't see the tree for the forest. My daughter had an Excel sheet with a column with names such as:

"Billy Bob Cohen"
"Bob Apple, Jr."
"Miss Sunny Daisy"
"Mr. and Mrs. Joe Brown"

and I said, sure, it'll be easy to pull the last names out for an alpha sort. It wasn't. Is there a formulaic way to extract only the digits after the last space? Including a little if(not(iserror(find(",")),...,...) start two spaces back calculation, which I did manage to get working in my feeble efforts.

2. Re: Count specific character in cell

John,

I think a formulaic method might be a bit convoluted but the following UDF might offer some assistance
<pre>Function LastWord(text As String) As String
Dim TempText As String
Dim n As Integer
Do
n = n + 1
TempText = Left(Right(text, n), 1)
Loop Until TempText = " "
LastWord = Right(text, n - 1)
End Function</pre>

then =LastWord(A1) should give the rightmost word of an expression in A1

I have just noticed the "Jr" bit, and that scenario is not covered, but perhaps a manual intervention will take care of if there are not many of them.

Andrew C

3. Re: Count specific character in cell

Perfect, thank you Andrew! <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15> <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

BTW:

=lastword(IF(ISERROR(FIND(", Jr.",A1)),A1,REPLACE(A1,FIND(", Jr.",A1),5,"")))

... works perfectly to handle ", Jr.". Still need to do some cleanup to make sure those 300 (!) wedding guest names are all handled consistently.

4. Re: Count specific character in cell

This is what I originally had in mind when I first posted on this topic, and you pretty much showed me how to go about it. It may not be pretty, but then neither am I. Any suggestions, especially for incorrect argument protection?

Function NumChars(ArgText As String, CountChar As String) As Integer
Application.Volatile True
Dim TempText As String
For counter = 1 To Len(ArgText)
TempText = Mid(ArgText, counter, Len(CountChar))
If TempText = CountChar Then NumChars = NumChars + 1
Next counter
End Function

intended use where contents of A1: "Mr. Billy Bob Cohen"
=NumChars(A1," ") returns 4
=NumChars(A1,"B") returns 2
=NumChars(A1,"b") returns 1

5. Re: Count specific character in cell

Hi John,

What your function is doing is counting the number of times a given substring occurs in the main string. In order to parse the string, position is more important than number of occurrences. However as a function to count the number of times a character occurs it is fine.

If you replaced If TempText = CountChar Then NumChars = NumChars + 1 with If TempText = CountChar Then NumChars = Counter, you would have the position of the last occurrence of the passed substring, so that you could combine this function with RIGHT and LEN to return the substring starting after the last " " in your case. The following formula would return Cohen if Mr. Billy Bob Cohen was in cell A1. I suspect there might be a rogue space after Cohen's name in your list. : =RIGHT(A1,(LEN(A1)- NumChars(A1," ")))
I am assuming always that your intention was to extract the surname. However the function you came up with could have it's uses and you should keep it.

Andrew

6. Re: Count specific character in cell

Hi John,
This is probably too late for your original purpose, but just for reference you could also extract the last word using something like:
Public Function LastWord(strInput As String) As String
LastWord = Mid(strInput, InStrRev(strInput, " ") + 1, Len(strInput))
End Function
FWIW.

7. Re: Count specific character in cell

John,

Not exactly sure if your code will choke, but you might want to consider what happens under the following conditions - assuming I understood your code correctly.

Suppose ArgText is 10 chars
CountChar is 2 chars (or any number bigger than 1; which were all your examples)

Your loop will go for counter=1 to 10
at each iteration, you examine the position in ArgText starting at counter.
So for counter=1, you examine ArgText starting at 1 for positions 1 and 2 (Len(CountChar))
...
for counter=10, you're examining ArgText at positions 10 and 11. Isn't this a problem since ArgText is only 10 chars long? Will VBA complain?

Fred
[The frequency of my posts...is non deterministic]

8. Re: Count specific character in cell

I did not think about it, but the function appears to survive. If A1 contains "Mr. Billy Bob Cohen", which is 19 chars, =Numchars(<cell>,"ll") correctly returns 1 without puking.

9. Re: Count specific character in cell

Incompetent fingers. In the above with "Mr. Billy Bob Cohen", =NumChars(A1," ") correctly returns 3.

10. Re: Count specific character in cell

John,

Just got your reply altho it does not seem to have hit the lounge yet. So I'm replying to the post with your proposed procedure.

There are 2 concerns:
1. what will excel do when you examine chars 10 and 11. Some languages will complain about "array bounds" conditions. However, from what you said, apparently nothing. which leads to...
2. what is excel looking at, if anything, on the 10th time thru the loop? Char #10 and ??? Suppose, using your example of looking for "ll" that char 10=l. Since we don't know, might it be looking at something that, randomly, could also be an "l" and give incorrect results? or does it look at an "end of string" mark as the 11th char so this should not be a problem (I know that hex'00' is sometimes used as an end of string indicator)?

Fred

11. Re: Count specific character in cell

I don't think I can answer your question why, but if the target cell contains a single character, and the argument to be counted contains 11 characters, the function returns zero. This is intended as a UDF, not intended for use as functioning code, and since the string length is not set (Dim Argtext as String * n), the String default is 64k characters, much longer than the 1024 char limit in an Excel Cell, I assume VBA happily reads any nulls in excess of the arguments' actual length.

12. Re: Count specific character in cell

The situation you are describing does not seem to bother VBA or indeed Excel. Enter a word in A1, e.g. test string (11 characters). In B1 enter =Mid(a1,1,100). If it does anything at all it either truncates the string or pads it with null values. In the NumChar Function enter <pre> Debug.Print counter; TempText; NumChars; Len(TempText)</pre>

before Next Counter and cause the code to be executed using different values, including ones that might cause problems, and see the output in the VBA Immediate window.

Andrew

13. Re: Count specific character in cell

I don't know if this would help, and I am probably too late anyhow, but here is something else to look at. Select the cells that contain the names, and then select "Text to columns" from the Data menu. In the Wizard dialog boxes, select Delimited and Space as the delimiter. This will split the names up into individual words in ajoining columns.

14. Re: Count specific character in cell

"InStrRev" doesn't seem to be a valid procedure in XL97 ... where can I get more info on it?

15. Re: Count specific character in cell

Thanks. The source text has variable numbers of words, but I expect I could have used this approach with a nested formula to search for the last non blank cell in each row of the parsed columns.

Page 1 of 2 12 Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•