Finding last occurrence of character in string?

Posted by Mike Fleuette on September 17, 2001 8:38 PM

Is there a an easy way to find the last occurrence of a particular character in a text string? Specifically, I have a column that has user names in it and I need to break it out into first and last names (in two different columns).

For example, "Mike Fleuette" would become "Mike" and "Fleuette"; "Mike X. Fleuette" would become "Mike X." and "Fleuette"; "Mike and Michelle Fleuette" would become "Mike and Michelle" and "Fleuette".

Any thoughts? If there is a way to find the last space, then it is easy enough to do with RIGHT(), LEFT() and LEN()...

Thanks!

Posted by Aladin Akyurek on September 17, 2001 10:21 PM

Last Name and the Rest

Mike,

Assuming user names to be in A from A1 on.

[1]
In B1 enter: =RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

[2]
In C1 enter: =LEFT(A1,LEN(A1)-LEN(B1)-1)

[3] (An alternative to the formula in [1])
In B1 enter: =reversetext(LEFT(reversetext(A1),SEARCH(" ",reversetext(A1))-1))

Select B1:C1 and copy down as far as needed.

The formula in [1] requires that the char "@" does not occur in input names.

The alternative formula in [3] requires that you add the following user-defined function to your workbook.

Function REVERSETEXT(text) As String
'
' Returns its argument, reversed
' J. Walkenbach
'
Dim TextLen As Integer
Dim i As Integer
TextLen = Len(text)
For i = TextLen To 1 Step -1
REVERSETEXT = REVERSETEXT & Mid(text, i, 1)
Next i
End Function

In order to add the above UDF to your workbook, follow the steps below.

Open your workbook where you want to have this UDF.
Close all other workbooks, if any.
Activate Tools|Macro|Visual Basic Editor.
ACTIVATE INSERT|MODULE.
Go to the Project-VBA Project window/pane.
DOUBLE CLICK ON "Module1".
You'll get a pop window with things like: General and Declarations.
Copy the UDF code and paste it in the open space that you see on the latter window with the (code) bit in the title.
Activate File|Close and Return to Microsoft Excel.

Aladin

Posted by Mike Fleuette on September 17, 2001 10:24 PM

SOLVED THIS MYSELF -

if original name is in a1, and first/last name will go into b1 and c1, respectively, then

b1: {=LEFT(A1,MAX((MID(A1,ROW($1:$70),1)=" ")*ROW($1:$70)))} (CSE)
c1: =RIGHT(A1, LEN(A1) - LEN(B1))

Hope this helps someone else...

Posted by Aladin Akyurek on September 17, 2001 10:28 PM

Mike: No need for an array formula, see my post. (NT)

Posted by Mike Fleuette on September 18, 2001 8:06 AM

Re: Last Name and the Rest

Returns its argument, reversed J. Walkenbach

THANKS!

As I understand it, the last substitute counts the number of spaces (in this case) in the string, the first substitute replaces the last occurrence with some unusual character, and SEARCH finds that unusual character - BRILLIANT!