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?

Yes, there is an opposite function to Concatenate in Excel called "Text to Columns". This function can be used to split data that is combined in one cell into separate cells.

Here are the steps to use Text to Columns to split first and last names into separate cells:

1. Select the column that contains the combined names.
2. Click on the "Data" tab in the ribbon at the top of the screen.
3. Click on the "Text to Columns" button in the "Data Tools" section.
4. In the "Convert Text to Columns Wizard" that appears, select "Delimited" and click "Next".
5. In the next screen, select the delimiter that separates the first and last names. This could be a space, comma, or another character. Preview the results in the "Data preview" section to ensure the names are split correctly.
6. Click "Next" and choose the format for the two columns that will be created. You can choose "General" for both if you're not sure.
7. Click "Finish" to split the names into separate columns.

After completing these steps, you should have two columns with the first and last names separated. You can then delete the original column with the combined names if it's no longer needed.

Are the fields separated by a common value, like a comma? For example:

Smith, Joe
Doe, John

If so, you can use the Data-Text to Columns feature. Choose "Delimited"
and on the next screen choose whatever value separates the first and last
name.
--
Regards,

DavidB


"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?

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?

Guys, thanks very much!

"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?