Reverse Concatenate

K

Kate Was Here

Hi there
Does anyone know a function which is the reverse of concatenate. I have a
field which is first name and last name and I'd like them in separate fields.
Is there a way of doing it?
Any help gratefully received.
Kate x
 
G

Guest

Hi

Have a look at Data/Text To Columns. As long as the format is fairly
standard, this will do the job. You could do it using formulas, but it's
worth checking out the above first.
 
K

Kate Was Here

Brilliant.
Thank you!

Andy B said:
Hi

Have a look at Data/Text To Columns. As long as the format is fairly
standard, this will do the job. You could do it using formulas, but it's
worth checking out the above first.
Click to expand...
 
S

Steven S

Hi Andy,
I'm also trying to do something similar--split a field called Contact that
has Title First_Name Last_Name into three separate fields. However mine is a
little more complicated because the Contact field is messy (sometimes Title
exists, sometimes it's only Last Name, etc). Could you explain how you can
do this with formulas, as you mentioned in your post?

Thanks for your help.
-Steven S
 
D

David McRitchie

Hi Steven,
You have to have a character that separates the three fields,
if you don't you have to do it by sight and that does not work well
with a programmed approach.

If you have commas then you can use Text to Columns.
Since you want to use formulas the following might work depending
on your actual data, and mainly that you have commas to separate.

A1: title, first_name, last_name
A2: Chief Engineer, John, Petty
A3: Owner, Ron, de Bruin

The easiest solution would be to use some preexisting code

Code for ExtractElement at http://www.j-walk.com/ss/excel/tips/tip32.htm
or other solutions at/near: http://www.mvps.org/dmcritchie/excel/strings.htm#nthWord

If the first name and last name have no spaces, but the title might, you
can use the SepLastTerm macro on my join.htm webpage.
 
L

LHSallwasser

Hello David:
Thank you very much for the URL to the Excel tips site. The CountOccurences
function was exactly what I was looking for.
Best regards,
LHollister Sallwasser
 
Top