A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Is there an opposite function to "Concatenate" in Excel?



 
 
Thread Tools Display Modes
  #1  
Old August 17th 05, 04:25 PM
drbonne
external usenet poster
 
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?
Ads
  #2  
Old August 17th 05, 04:31 PM
DaveB
external usenet poster
 
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  
Old August 17th 05, 04:33 PM
Duke Carey
external usenet poster
 
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  
Old August 17th 05, 04:45 PM
drbonne
external usenet poster
 
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  
Old October 23rd 06, 11:21 PM posted to microsoft.public.excel.worksheet.functions
jenn
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?

  #6  
Old October 24th 06, 12:20 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
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?



  #7  
Old October 24th 06, 08:27 PM posted to microsoft.public.excel.worksheet.functions
jenn
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?

>
>
>

  #8  
Old October 11th 07, 03:59 PM posted to microsoft.public.excel.worksheet.functions
gplant
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?

>
>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 04:19 PM.


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