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

 

[Mr_Blue1993]

Hello Mark, try this formula

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

It doesn't seem to work in excel 2016, i keep getting errors.

 

[Rick Rothstein]

It doesn't seem to work in excel 2016, i keep getting errors.

Saying "I keep getting errors" tells us nothing. What are the errors you get? Show us hat is in the cell being referenced (A1 in the formula that was posted) when you get those errors.

 

[Mr_Blue1993]

Saying "I keep getting errors" tells us nothing. What are the errors you get? Show us hat is in the cell being referenced (A1 in the formula that was posted) when you get those errors.

I put my sentence in A1

"Iams Delights wet turkey&duck jelly 85gr" and i need to get the 85gr

<tbody>
</tbody>

So i put the formula in A2 then i immidiate get "There's a problem with this formula Not trying to type a formula? etc...."
When i click OK, The first A1 (in the formula) is highlighted. saying he is expecting text there if i read it right (well that is what A1 is for i guess)

 

[Rick Rothstein]

I put my sentence in A1

"Iams Delights wet turkey&duck jelly 85gr" and i need to get the 85gr

<tbody>
</tbody>

So i put the formula in A2 then i immidiate get "There's a problem with this formula Not trying to type a formula? etc...."
When i click OK, The first A1 (in the formula) is highlighted. saying he is expecting text there if i read it right (well that is what A1 is for i guess)

I cannot reproduce that error using the text you posted; however, I seem to be picking up a possible trailing character which could interfere with the formula (it would not produce an error though), but I cannot be sure if that character is actually in your cell's text or if, instead, it is coming from when I copy the text from the forum's comment window. Can you post a copy of your workbook with the non-working text in A1 at a file-sharing site (dropbox maybe) and post a link to it so we can download it and examine the problem directly?

 

[Mr_Blue1993]

I cannot reproduce that error using the text you posted; however, I seem to be picking up a possible trailing character which could interfere with the formula (it would not produce an error though), but I cannot be sure if that character is actually in your cell's text or if, instead, it is coming from when I copy the text from the forum's comment window. Can you post a copy of your workbook with the non-working text in A1 at a file-sharing site (dropbox maybe) and post a link to it so we can download it and examine the problem directly?

You can find the file here:
https://www.dropbox.com/sh/h6f2qjpmm4sap0h/AAABTs88ddg9EVEZrcOEAX6Ca?dl=0

 

[Rick Rothstein]

You can find the file here:
https://www.dropbox.com/sh/h6f2qjpmm4sap0h/AAABTs88ddg9EVEZrcOEAX6Ca?dl=0

I am sorry, but I cannot reproduce the problem you originally wrote about... the formula works fine for me on both tabs that had information on them. Now, I am using XL2010, not XL2016, but I can tell you there is nothing in that formula that would be sensitive to the version of Excel it is used on.

Try an experiment with me. I know you have it on your hard drive, but ignore that file and download the file you posted to your hard drive in a different folder from your original file and then open that newly downloaded file into Excel and try the formula on its cells... does the formula now work or does it still produce errors for you? What I am trying to do is see if your existing file is corrupted in some way... if the new file works, my suspicion (unless someone comes along with a better idea) is that you original file became corrupted and should be discarded (after you copy the information from it to a new file, of course).

 

[Peter_SSs]

I am sorry, but I cannot reproduce the problem you originally wrote about... the formula works fine for me on both tabs that had information on them. Now, I am using XL2010, not XL2016, but I can tell you there is nothing in that formula that would be sensitive to the version of Excel it is used on.

I agree and I have downloaded the sample file and tested in Excel 2016. The formula works fine.

 

[Marcelo Branco]

Are you using an English version of Excel? Does it uses semicolon as argument separator instead of comma?
See if this works

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

M.

 

[Rick Rothstein]

Are you using an English version of Excel? Does it uses semicolon as argument separator instead of comma?
See if this works

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

Oh, good thought (I never remember that possibility)!

 

[Mr_Blue1993]

Are you using an English version of Excel? Does it uses semicolon as argument separator instead of comma?
See if this works

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

M.

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.