[Tcassi]

I've been given an import file that contains first & last name in one cell...ugh! Is there a formula that would do the opposite of Concatenate - split the two names into different cells where the space occurs? I've been searching the excel formula files & just can't find anything.

Thanks for your help!

[Elaine Tucker]

Highlight the column with the name
click on the Data option in the menu bar
Select Text to Columns
Select the fixed field with with space
click next
using the "window shade" click there and then highlight an empty colum in your worksheet
click finish
This wil add two columns that have each name in a column

Before: Column A = Elaine Tucker

after
Column A = Elaine Tucker
Column B = Elaine
Column C = Tucker

[Karenw]

Text to columns will do that, though it's a little messy if, say, some have middle initials and some don't.

http://office.microsoft.com/en-us/as...itCellContents

[Tcassi]

Thanks to both of you! I modified Elaine's suggestion slightly - it wouldn't work the first time. But by choosing Delimited & checking the Space box - it worked!

You gals are fantastic - just saved me a hours worth of work

[Elaine Tucker]

Quote:

Originally Posted by Tcassi

Thanks to both of you! I modified Elaine's suggestion slightly - it wouldn't work the first time. But by choosing Delimited & checking the Space box - it worked!

You gals are fantastic - just saved me a hours worth of work

OOOHH I'm sorry! I was going on memory!

[RobertWild]

Generally works, but will need to be manually checked for double-barelled names.

Eg:
Van (Fred Van Bloggs)
Van Der (Fred Van Der Bloggs)
De (Fred De Bloggs)
O (Fred O Bloggs (subnote: typo - someone forgets the " ' "))

[SimonKoppel]

There's an excellent freeware utility we've downloaded called Namechop. It'll split up first name and surname, but will also recognise the vast majority of prefixes and suffixes, and copes with a mixture of different styles in a single input file - so, you can give it a file containing John Smith, Mrs Mary Jones, A Jackson, Mrs Miggins, Andrew Williams Esq., and it will split all of these up into the correct columns.

It's not infallible - in particular, we've found that it won't pick up on some of the rarer British prefixes like The Right Honourable - but it gets it right at least 95% of the time.

[Melissa Siobhan]

Quote:

Originally Posted by SimonKoppel

There's an excellent freeware utility we've downloaded called Namechop. It'll split up first name and surname, but will also recognise the vast majority of prefixes and suffixes, and copes with a mixture of different styles in a single input file - so, you can give it a file containing John Smith, Mrs Mary Jones, A Jackson, Mrs Miggins, Andrew Williams Esq., and it will split all of these up into the correct columns.

It's not infallible - in particular, we've found that it won't pick up on some of the rarer British prefixes like The Right Honourable - but it gets it right at least 95% of the time.

NameChop rocks! I have had it for over a year and I love it. Don't need to use it that often because I usually can make people give me the data in the correct format but occasionally it is just not available that way. Even if you don't need it today I suggest you get it and save it for the day you need it. Because I guarantee when you need it you won't have time to research - what was that utility again?

[SimonKoppel]

Melissa, I think we've only got it because you recommended it on the BB support forums a few months back. So, thanks!