Find text after last space in string

[Mark77]

Hi everyone

I wonder if you can help with this issue, I receive data about a name in one column, a sample of this data is as follows:

Column A
Row 1 Mr M A One
Row 2 Mr Mark A Two
Row 3 Mrs Maria Angela Three
Row 4 Miss Four

Each row of the above appears in one cell and I need to split this out into Title and Surname. I have a function to find the title from the above:

=LEFT(A1,FIND(" ",A1&" "))

This finds the information before the first space, I would like to know if it is possible to do the same to find the information after the last space. It sounds simple but as you can see from the above there are not a set number of spaces for each row and I cannot see how to find the last space where the number is variable.

Please could you help?

Thanks

Mark

 

[barry houdini]

Hello Mark, try this formula

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

 

[G2K]

Hi Mark,

You can use following function to extract Title and Surname from given string.

Function TitleSurname(TS As String) As String
Dim Str As Variant
Str = Split(TS, " ")
If UBound(Str) < 2 Then MsgBox "No Surname": Exit Function
       TitleSurname = Str(LBound(Str)) & " " & Str(UBound(Str))
End Function

Hope it helps

 

[G2K]

or you can use this -

=LEFT(A1,FIND(" ",A1)) &" "&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

HTH

 

[Mark77]

Thank you for your fast replies, all three work perfectly and as decribed.

Can't thank you all enough

Best regards

Mark

 

[Walpy]

Is there a way to do this for a large range of cells ('Time Card'!G8:G679), then count the number of times it would come up with "185"?

 

[Rick Rothstein]

Is there a way to do this for a large range of cells ('Time Card'!G8:G679), then count the number of times it would come up with "185"?

If you really do not need the split out values after the last space for anything else, you can elimate doing that step and count the entries directly from the original range...

=COUNTIF('Time Card'!G8:G679,"* 185")

If you have the 185 value stored in a cell (rather than hard-coding into the formula), say it is stored in 'Time Card'!G1, then the formula would look like this...

=COUNTIF('Time Card'!G8:G679,"* "&'Time Card'!G1)

 

[Walpy]

Thanks so much Rick!

 

[hitbid]

Hello Mark, try this formula

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

Trying to understand what is going on here. It works. It finds the word to the far right no matter how many spaces. But I walk through, and when you do the REPT part, I stutter step.

So substitute A1, where there are spaces, with the LEN of A1...so if LEN is 22, replace each space with 22 spaces?

Yes. Fine.

Now find the word with RIGHT, 22 spaces.....ok, so this is telling me that because we inserted 22 spaces each time, that it is now only possible to pull back the far most word to the right, because only space exists in front of it right?
Then Trim it and we are done.

I think I talked that out correctly. I don't like to see cell formulas where I don't understand what is going on. Am I right?

 

[Eric W]

Good job, you sussed it out correctly.