Fake space between First and Last Name in Excel

[hthomas]

I have a spreadsheet that I had send to me one of our sites that we use for User Access. It was exported straight from the program. I am having trouble in the formatting of the spreadsheet in order to make it searchable and to use VLOOKUP. In the Full Name column, the first and last name look like they are separated by a space " " but it does not work that way when I try to split them into First Name and Last Name columns. It is almost like it is a fake space. There are also some entries that have a double space between first and last name and the below formulas work on them but still leave the "fake space" in the separated text. I have tried many ways to make it a true space between the words but nothing has worked and none of my researching to find a solution to this problem has been found. Can anyone help me with figuring this out so I don't have to continue to delete the "fake space" and add a real space between the words?

These are the formulas I have used to split up the column, but they do not work for all entries:

=Trim(LEFT(B638,FIND(" ", B638)))
=Trim(RIGHT(B638,LEN(B638)-FIND(" ",B638)))

Any help is much appreciated.

 

[par60056]

Using the "=Code" formula that pgc showed and the sample that you put on dropbox, the character in between is char(202)

So the 2 formulas you originally posted should become:
=Trim(LEFT(B638,FIND(char(202), B638)))
=Trim(RIGHT(B638,LEN(B638)-FIND(char(202),B638)))