Fake space between First and Last Name in Excel

hthomas

New Member
Joined
Oct 6, 2014
Messages
5
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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Simon4s

Board Regular
Joined
Sep 22, 2014
Messages
143
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,840
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

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
  1. 2011
  2. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
  1. 2011
  2. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

hthomas

New Member
Joined
Oct 6, 2014
Messages
5

ADVERTISEMENT

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

Board Regular
Joined
Sep 22, 2014
Messages
143
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

New Member
Joined
Oct 6, 2014
Messages
5
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

Board Regular
Joined
Sep 22, 2014
Messages
143
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,165
Messages
5,527,172
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top