Data-Text to Columns...Delimited

How is the data orgainzed? If it's "Bob Dole" then the delimiter is a
space character. If it's "Dole, Bob" then the delimiter is a comma

You'll need an empty column to the right of the names. Also, if it's a
space delimiter you'll need to do some checking & manual cleanup because of
names such as "Billy Joe Davis"


"drbonne" wrote:

Have downloaded data with first and last names lumped together in one cell.
Is there a way to have excel split the first and last name into two separate
cells?

is there a way to do a folmula to find the space, add and comma and swap
everything before the space with everything after the space.
Jennifer Dolly in A2
Fomula in B2 something to do with =find(" ") and RIGHT & LEFT... etc...

so it becomes Dolly, Jennifer

??

I do the text to columns and then = B2&","&A2, but am hoping to save myself
some time.

"Duke Carey" wrote:

Data-Text to Columns...Delimited

How is the data orgainzed? If it's "Bob Dole" then the delimiter is a
space character. If it's "Dole, Bob" then the delimiter is a comma

You'll need an empty column to the right of the names. Also, if it's a
space delimiter you'll need to do some checking & manual cleanup because of
names such as "Billy Joe Davis"


"drbonne" wrote:

Have downloaded data with first and last names lumped together in one cell.
Is there a way to have excel split the first and last name into two separate
cells?

=MID(A1,FIND(" ",A1)+1,255)&", "&LEFT(A1,FIND(" ",A1))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"jenn" wrote in message
...
is there a way to do a folmula to find the space, add and comma and swap
everything before the space with everything after the space.
Jennifer Dolly in A2
Fomula in B2 something to do with =find(" ") and RIGHT & LEFT... etc...

so it becomes Dolly, Jennifer

??

I do the text to columns and then = B2&","&A2, but am hoping to save
myself
some time.

"Duke Carey" wrote:

Data-Text to Columns...Delimited

How is the data orgainzed? If it's "Bob Dole" then the delimiter is a
space character. If it's "Dole, Bob" then the delimiter is a comma

You'll need an empty column to the right of the names. Also, if it's a
space delimiter you'll need to do some checking & manual cleanup because
of
names such as "Billy Joe Davis"


"drbonne" wrote:

Have downloaded data with first and last names lumped together in one
cell.
Is there a way to have excel split the first and last name into two
separate
cells?

that is the BEST formula I have ever gotten from this site.... thanks A LOT!!!!

"Bob Phillips" wrote:

=MID(A1,FIND(" ",A1)+1,255)&", "&LEFT(A1,FIND(" ",A1))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"jenn" wrote in message
...
is there a way to do a folmula to find the space, add and comma and swap
everything before the space with everything after the space.
Jennifer Dolly in A2
Fomula in B2 something to do with =find(" ") and RIGHT & LEFT... etc...

so it becomes Dolly, Jennifer

??

I do the text to columns and then = B2&","&A2, but am hoping to save
myself
some time.

"Duke Carey" wrote:

Data-Text to Columns...Delimited

How is the data orgainzed? If it's "Bob Dole" then the delimiter is a
space character. If it's "Dole, Bob" then the delimiter is a comma

You'll need an empty column to the right of the names. Also, if it's a
space delimiter you'll need to do some checking & manual cleanup because
of
names such as "Billy Joe Davis"


"drbonne" wrote:

Have downloaded data with first and last names lumped together in one
cell.
Is there a way to have excel split the first and last name into two
separate
cells?

Wow! Worked right the first time, with no tweeking! Thank you very much!!!

Glenn

"Bob Phillips" wrote:

=MID(A1,FIND(" ",A1)+1,255)&", "&LEFT(A1,FIND(" ",A1))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"jenn" wrote in message
...
is there a way to do a folmula to find the space, add and comma and swap
everything before the space with everything after the space.
Jennifer Dolly in A2
Fomula in B2 something to do with =find(" ") and RIGHT & LEFT... etc...

so it becomes Dolly, Jennifer

??

I do the text to columns and then = B2&","&A2, but am hoping to save
myself
some time.

"Duke Carey" wrote:

Data-Text to Columns...Delimited

How is the data orgainzed? If it's "Bob Dole" then the delimiter is a
space character. If it's "Dole, Bob" then the delimiter is a comma

You'll need an empty column to the right of the names. Also, if it's a
space delimiter you'll need to do some checking & manual cleanup because
of
names such as "Billy Joe Davis"


"drbonne" wrote:

Have downloaded data with first and last names lumped together in one
cell.
Is there a way to have excel split the first and last name into two
separate
cells?