Help needed--Formulas Not Working When I fill Them Down

I have attached a link to a worksheet. In it, I simply want to fill the formula in Cells B2 and C2 down the columns. However, when I do this, the formula is not working as if it is not recognizing the spaces between the words as spaces.

I have tried to use the CLEAN function, and the SUBSTITUTE Function in case the spaces are hard spaces instead of regular spaces.

Does anyone know why this might be happening?

Thank you!


https://onedrive.live.com/redir?res...252&authkey=!AKPGRyKJ6aIykpQ&ithint=file,xlsx

CynTV said:

However, when I do this, the formula is not working as if it is not recognizing the spaces between the words as spaces.

Click to expand...

If I understand you correctly, you want to extract the last word from A to C column,
then try

in the B2 set formula
in the C2 set formula
copy down

The issue is actually 2 invisible characters at the end of the names, a space and ascii character 160

The attached file puts what the names should be into column J then works with that.

The formula in column C was adapted from BradC's reply to a question at stackoverflow.

navic said:

If I understand you correctly, you want to extract the last word from A to C column,
then try

in the B2 set formula

Code:

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

in the C2 set formula

Code:

=MID(A2,FIND("",A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),2)))+1,256)

copy down

Click to expand...

Thank you for taking a look at this for me. The formulas do not appear to be working.

It looks like it is an issue of non breaking spaces and I found that the following will work. I am learning, so if you have any feedback or if you feel as if I could have done something differently, please let me know. Thanks, again!

Placed in In C2
=TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A2,CHAR(160)," "))," ",REPT(" ",LEN(A2))),LEN(A2)))

Placed in B2
=LEFT(A2,LEN(A2)-LEN(C2)-1)

CynTV said:

The formulas do not appear to be working.

Click to expand...

OK, the fact is that I was not properly understood. I thought that you want to return the last name if the text contains three words.
The formula given by @NoS is functional
with helper H column
without helper column
But it does not matter, you found simple a solution

@ navic

She didn't actually "find it".

She's a cross poster who doesn't post in the appropriate manner.

Thank you, navic. I appreciate your help. Thank you for taking time to reply to my post and for the clarification.

Also, Thank you NoS. I am relatively new to forum posting and I was not familiar with the term "cross-posting" so I looked it up and came across this article http://www.excelguru.ca/content.php?184 I posted this question in two forums as there have been times in which no one has replied to a post. I thought it was a good idea, but from the article I see why this can be problematic I will adhere to the processes in the article. Thanks for the heads up and for posting the link to my previous post, I appreciate it.

Best regards,

CynTV