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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
pgc's recommendation of using char(160) is probably a better idea because it will be more obvious what it is.
 
Upvote 0
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
 
Upvote 0
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."
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top