Excel Text to Columns - Can't Split

I have an Excel file that is downloaded from our database software (MindBody). I need to split a name field into lastname/firstname and I can't get it to work. I've tried just about everything from moving the data into/out of Access, putting into a Word table, saving as CSV, etc., etc. The cell type is 'l' but there's something funky going on. Please help me! I'm attaching a file with just a few of the names. Thanks much, Jani
Book4.xls
BassoonAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
John-Charles-HerzbergConnect With a Mentor Commented:
The problem you are having is the space is not a space but a special symbol.  Just copy the space between the names and go to find/replace and past it into the find section and put a space in the replace section.  Replace all and you are set.
0
 
Rory ArchibaldCommented:
Do the Text To Columns as normal, and in the Delimiter section, choose Other, then click in the adjacent textbox, hold the Alt key and type 0160 on the number keypad and release Alt, then press Finish.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
BassoonAuthor Commented:
So I tried this and get a 'Value' error. For some reason the space between the first name last name is not being recognized as a space. I've 'replaced' in other spreadsheets a 'space' with another character but it doesn't work on this one! Any other thoughts or did I not do the formula correct from the site you provided?
0
 
Rory ArchibaldCommented:
If you want to do it with formulas then you need
FIND(CHAR(160),
rather than
 FIND(" ",
in your formula. Or just use Text To Columns as I mentioned.
0
 
magentoCommented:
Basson,

Yes , use the space correctly (" ")
0
 
BassoonAuthor Commented:
Thank you so much for the quick response. This worked perfectly (now if I can just remember it!). Other solutions may work also but I did not try them since this one was quick to do.
0
All Courses

From novice to tech pro — start learning today.