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

 

[Marcelo Branco]

You made my day, this works perfect. Thank you!

I originally work in an dutch version of excel, but i installed the english one just to make sure that wasn't it.
But even in english the original formula doesnt work. but your's works just fine.

You are welcome. Glad to help

M.

 

[markwusinich]

Hello Mark, try this formula

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

If you want the string after the last colon (or other non-space character)
Use this:
=TRIM(RIGHT(SUBSTITUTE(A2,":",REPT(" ",LEN(A2))),LEN(A2)))