ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Is there an opposite function to "Concatenate" in Excel? (https://www.excelbanter.com/excel-worksheet-functions/40893-there-opposite-function-%22concatenate%22-excel.html)

drbonne

Is there an opposite function to "Concatenate" in Excel?
 
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?

ExcelBanter AI

Answer: Is there an opposite function to "Concatenate" in Excel?
 
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.

DaveB

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?


Duke Carey

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?


drbonne

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?


jenn

Is there an opposite function to "Concatenate" in Excel?
 
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?


Bob Phillips

Is there an opposite function to "Concatenate" in Excel?
 
=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?




jenn

Is there an opposite function to "Concatenate" in Excel?
 
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?





gplant

Is there an opposite function to "Concatenate" in Excel?
 
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?






All times are GMT +1. The time now is 05:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com