Comma Delimited Merging..

vampcatt · Mar 10th, 2002, 03:59 PM

I have a couple text files that are comma delimited that I have imported into excel.

My problem is that a handful of the rows had "extra" commas.. resulting in extra columns.

example:

San Jose, CA, shirts, orange, green, blue,

In that case, I'd want to merge "orange, green, blue" into one cell.

Is there a function to perhaps highlight the columns side by side and merge them all into ONE cell "orange green blue"

Thank you in advance for your help.

Matt · Mar 10th, 2002, 04:05 PM

Once the data has been imported, you can use the concatenate formula,

e.g.,

=CONCATENATE(A1,",",B1,",",C1)

where A1 to C1 contain your colours

HTH

Matt

[ This Message was edited by: Matt on 2002-03-10 15:06 ]

Jack in the UK · Mar 10th, 2002, 04:21 PM

Hi ==

Worth a look at :CONCATENATE should do as you ask..

=CONCATENATE(Rng1, " ",Rng2, " ",Rng3)

Use the wizard if needs be can be any rng (range but single cell at a time thou) will just string them together for you.

vampcatt · Mar 10th, 2002, 06:32 PM

I tried the concatenate

"e.g.,

=CONCATENATE(A1,",",B1,",",C1) "

BUT.. I dont wish to have the seperate cells in the document anymore.

That does work to merge the cells, but then I wind up with

color color color , color, color, color
I only want the "color color color" in there.. if I remove the sperate cells, then of course excel doesnt have that data to reference to.

Is there no way to "merge" them without having them still there to reference ?

Skebo · Mar 10th, 2002, 08:26 PM

You can do the =CONCATENATE(A1,",",B1,",",C1)
and then copy the values from the concatenate to another column using 'paste special' with the values option set.

Mar 10th, 2002, 04:21 PM	#3
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	Hi == Worth a look at :CONCATENATE should do as you ask.. =CONCATENATE(Rng1, " ",Rng2, " ",Rng3) Use the wizard if needs be can be any rng (range but single cell at a time thou) will just string them together for you. __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 10th, 2002, 03:59 PM	#1
vampcatt New Member Join Date: Feb 2002 Location: State? Insanity! :) Posts: 26	I have a couple text files that are comma delimited that I have imported into excel. My problem is that a handful of the rows had "extra" commas.. resulting in extra columns. example: San Jose, CA, shirts, orange, green, blue, In that case, I'd want to merge "orange, green, blue" into one cell. Is there a function to perhaps highlight the columns side by side and merge them all into ONE cell "orange green blue" Thank you in advance for your help.

Mar 10th, 2002, 04:05 PM	#2
Matt Board Regular Join Date: Feb 2002 Location: England Posts: 212	Once the data has been imported, you can use the concatenate formula, e.g., =CONCATENATE(A1,",",B1,",",C1) where A1 to C1 contain your colours HTH Matt [ This Message was edited by: Matt on 2002-03-10 15:06 ]

Mar 10th, 2002, 06:32 PM	#4
vampcatt New Member Join Date: Feb 2002 Location: State? Insanity! :) Posts: 26	I tried the concatenate "e.g., =CONCATENATE(A1,",",B1,",",C1) " BUT.. I dont wish to have the seperate cells in the document anymore. That does work to merge the cells, but then I wind up with color color color , color, color, color I only want the "color color color" in there.. if I remove the sperate cells, then of course excel doesnt have that data to reference to. Is there no way to "merge" them without having them still there to reference ?

Mar 10th, 2002, 08:26 PM	#5
Skebo New Member Join Date: Feb 2002 Posts: 20	You can do the =CONCATENATE(A1,",",B1,",",C1) and then copy the values from the concatenate to another column using 'paste special' with the values option set.