Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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.

    Hope the above is helpful,

    Andrew

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    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. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Count specific character in cell

    Incompetent fingers. In the above with "Mr. Billy Bob Cohen", =NumChars(A1," ") correctly returns 3.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    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. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Legare Coleman

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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?
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 2 12 LastLast

Posting Permissions

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