asked on

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

Rory Archibald

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.

magento

Hi ,

Check this.

http://www.bluemoosetech.com/microsoft-excel-functions.php?jid=32&title=Separating%20First%20and%20Last%20Names%20in%20Microsoft%20Excel

Bassoon

ASKER

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?

ASKER CERTIFIED SOLUTION

John-Charles-Herzberg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rory Archibald

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.

magento

Basson,

Yes , use the space correctly (" ")

Bassoon

ASKER

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.