Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
drbonne
 
Posts: n/a
Default 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?
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
DaveB
 
Posts: n/a
Default

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?

  #4   Report Post  
Duke Carey
 
Posts: n/a
Default

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?

  #5   Report Post  
drbonne
 
Posts: n/a
Default

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?



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 107
Default 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?

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 380
Default 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?



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 107
Default 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?




  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default 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?




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel option to store trendline's coefficients in cells for use Miguel Saldana Charts and Charting in Excel 9 June 20th 05 08:45 PM
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM
clock Wildman Excel Worksheet Functions 2 April 26th 05 10:31 AM
Need opposite of excel function CONCATENATE Adam Excel Discussion (Misc queries) 3 March 5th 05 09:59 PM
Concatenate Function will not work Chuck W Excel Discussion (Misc queries) 4 February 1st 05 12:41 AM


All times are GMT +1. The time now is 07:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"