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.

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

0

Scruples89Author Commented:

What happens if I have 10,000 name records I need to process? all of them are in rows.

0

Squarespaceâ€™s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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?

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

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.

Public Function fn_last_position(sPassedSt

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

End Function

-- To test

?fn_last_position("A & B Billards", " ")

6