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.

 

[Simon4s]

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.

Didn't fully read your post. But try alt + numberpad 255 or alt + numberpad 32 to make a space.

 

[pgc01]

Hi

You can check the code for that character.

For ex., if A1 has "John Smith" you know that that's the 5th character and so you can use:

=CODE(MID(A1,5,1))

Then you can use it in your formulas, ex.:

=Trim(LEFT(B638,FIND(CHAR(somecode), B638)))

Remark: if you are getting the text from the cell, maybe it's the html non-breaking space.

Try:

=Trim(LEFT(B638,FIND(CHAR(160), B638)))

if it does not work do as I explained before.

 

[par60056]

The character in there is called a non-breaking space. It can be created using the number pad by holding ALT and typing 0160.

Try replacing the space in your FIND with that.

 

[par60056]

pgc's recommendation of using char(160) is probably a better idea because it will be more obvious what it is.

 

[hthomas]

Here is a link to the sample of the issue:

https://www.dropbox.com/s/3hjbje5l79q47aa/Sample%20-%20Full%20Name%20issue.xlsx?dl=0

 

[hthomas]

Didn't fully read your post. But try alt + numberpad 255 or alt + numberpad 32 to make a space.

With this, I would still have to do that manually for each entry and the database contains over 700 entries.

 

[Simon4s]

With this, I would still have to do that manually for each entry and the database contains over 700 entries.

....
Go click the replace button the top right.
replace " " with alt 032. Click Replace ALL

 

[hthomas]

Hi

You can check the code for that character.

For ex., if A1 has "John Smith" you know that that's the 5th character and so you can use:

=CODE(MID(A1,5,1))

Then you can use it in your formulas, ex.:

=Trim(LEFT(B638,FIND(CHAR(somecode), B638)))

Remark: if you are getting the text from the cell, maybe it's the html non-breaking space.

Try:

=Trim(LEFT(B638,FIND(CHAR(160), B638)))

if it does not work do as I explained before.

I tried this and it does not work. I have attached a sample of the document for viewing.

....
Go click the replace button the top right.
replace " " with alt 032. Click Replace ALL

There is no real space to recognize. That is the issue. I tried that and this message came up:

"Microsoft Excel cannot find the data you're searching for."

 

[Simon4s]

okay... i see what you're saying.
1. Insert a new column inbetween A and B. (now u have a, b , c. In B, type in alt 032.
2. in column D write, = B1&C1
3. select all of column d and copy
4. select column e and paste specials (values)
5. delete B,C,D
Doing this should make a real space