Find the last occurrence of a character in a string

I have a list of names. I want to separate out the last name from this list. The list contains names in the following configurations.
1. A Billards
2. A A Billards
3. A & B Billards

I need to find the last occurrence of a space character in the text string of the name.

I have tried various solutions. I added a User Defined Module that reversed the character string so I could then find the first occurrence and then re reverse it. Problem was that when I saved my UDM 2 things happened. 1. The excel file reverted to an earlier version and I lost all the work I was doing. 2. when I went to use the UDM again, I got a #NAME error that I was not able to correct. Probably there was some security setting on my computer that prevents UDM's from working and I don't know how to correct that setting.

Is there a simpler way to do this?

I also tried the Find and Substitute functions but I have not been able to get them to work properly.

I would prefer to use existing excel functions and avoid VBA script.
Scruples89Asked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
REPT(" ",99) just generates a string of 99 spaces - SUBSTITUTE function then replaces every space in your original data with that string of 99 spaces....and RIGHT function then takes the last 99 characters from the result.....so as long as the last word in your result doesn't contain more than 99 characters the result of that will be many spaces followed by the last word - the initial TRIM function "trims" off those spaces leaving you with the last word only.

Lets assume you put that formula in C2 then for the rest of the data use this formula in B2

=TRIM(LEFT(TRIM(A2),LEN(TRIM(A2))-LEN(C2)))

Both formulas will also cope with any additional spaces you may have in the data

regards, barry
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I'm guessing there's a more elegant way to pull this off, but the below works in Acces VBA:

Public Function fn_last_position(sPassedString as String, sSearchString as string) as Integer

fn_last_position = Len(sPassedString) - InStr(1, StrReverse(sPassedString), sSearchString) + 1

End Function

-- To test
?fn_last_position("A & B Billards", " ")
   6
0
 
TazDevil1674Commented:
If those values are in Column A, type the following in B & C

b - =LEFT(A3,MAX((MID(A3,ROW($1:$70),1)=" ")*ROW($1:$70)))
c - =RIGHT(A3, LEN(A3) - LEN(B3))

For the Formula in B - its and Array Forumula, when you type it you need to press Ctrl+Shift+Enter to get it to work, not just Enter

Example
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Scruples89Author Commented:
What happens if I have 10,000 name records I need to process? all of them are in rows.
0
 
Martin LissOlder than dirtCommented:
In VBA you can use intPos = InStrRev(<string>, <string to match>)

It looks left to right.
0
 
Saqib Husain, SyedEngineerCommented:
Try these non-array formulas
=REPLACE(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),9999,"")
=REPLACE(A1,1,LEN(B1)+1,"")
0
 
TazDevil1674Commented:
If you input the formula as I suggested, you can drag/fill down/copy+paste the formula down and it works correctly

As long as the one you copy from is entered correctly
0
 
barry houdiniCommented:
Assuming data in A2 you can get everything after the last space with this formula in B2:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),99))

Put cursor on the bottom right of B2 until you see a black "+" - that's the "fill handle" - double-click and the formula will fill down as far as you have continuous data in the adjacent column

regards, barry
0
 
Scruples89Author Commented:
Hi Barry, Thanks! How does this formula work? What is the REPT function and how does it generate the result?
0
 
Scruples89Author Commented:
Also, if I want to get the text preceding the last name into a Given Names Field. How do I do that?
0
 
hnasrConnect With a Mentor Commented:
I go with barryhoudini!

Explanation of formula for 9 characters:
B1=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",9)),9))

Representing " " as ^ for clarity
TRIM             Right                              Substitue                                       TRIM                  String
 Billards <== ^Billards<==   A^^^^^^^^^&^^^^^^^^^B^^^^^^^^^Billards <== A & B Billards <== A & B Billards

Begging of string:

C1=TRIM(SUBSTITUTE(A1,B1,""))

To copy formulas:
Split sheet vertical/horizontal
Show start cell in one pane, and end cell in other pane
Copy formula
Select range
Paste
0
 
Scruples89Author Commented:
Okay that is looking good, I will give it a try and see how it works.
0
 
hnasrCommented:
I see that barryhoudini posted an explanation while I was figuring a simple pictorial one.
0
 
Scruples89Author Commented:
Thanks for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.