Reply
 
LinkBack Thread Tools Display Modes
  #1   Report Post  
Old August 17th 05, 04:25 PM
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  
Old August 17th 05, 04:31 PM
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?

  #3   Report Post  
Old August 17th 05, 04:33 PM
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?

  #4   Report Post  
Old August 17th 05, 04:45 PM
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?

  #5   Report Post  
Old October 23rd 06, 11:21 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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?



  #6   Report Post  
Old October 24th 06, 12:20 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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?



  #7   Report Post  
Old October 24th 06, 08:27 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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?




  #8   Report Post  
Old October 11th 07, 03:59 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
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
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 08:59 PM
Concatenate Function will not work Chuck W Excel Discussion (Misc queries) 4 January 31st 05 11:41 PM


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

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017