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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

John-Charles-HerzbergCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.