Combine data from columns into single cell [SOLVED]

[IKHAN]

Hello ,


Can somebody help with this data, CSV Sheet contains data in multiple columns with spaces tabs and blank columns , needing to combine all data in single cell without any spaces or tabs.


macro needed -sample file attached , usually my data runs A1 TO AU5000


https://hotfile.com/dl/225770243/302b429/SAMPLE.csv.html

 

[Sajan]

Hello IKHAN,

Please check the following recent post to see if the techniques described there would help you. If not, let us know.


http://chandoo.org/forums/topic/add-texts-of-one-or-more-rows-in-to-one-rows-satisfies-a-specified-condition


Cheers,

Sajan.

 

[IKHAN]

sajan - I need to join data of complete row into single cell eliminating any spaces or tabs


example :


A1 B1 C1 D1 E1 F1


APPLE GRAPES ;DELTA1 BLANK :: "D2


output reqd :


In COLUMN A1


AppleGRAPES;DELTA1::"D2

 

[Faseeh]

IKHAN,


This link might also help:


http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/


Regards

 

[IKHAN]

Aaah...iam not good with excel...if somebody cld provide a macro to join/merge data from 2 or more columns, tet separated by "_"


column A Column B Column C


APPLE ORANGE GRAPES


OUTPUT IN COLUMN A


Column A


APPle_orange_grapes

 

[SirJB7]

Hi, IKHAN!


Your uploaded file at 1st post isn't related at all with this topic, is it? No apples, no oranges, not even a banana. It looks like this:

-----

SENDER                                                                                                                                                                                                                                                                ;SENDER2                                                                                                                                                                                                                                                                ;FIVE                             ;ResultOUT;Average    ;1.5s       ;05s        ;10s        ;15s        ;30s        ;60s        ;LESS1m     ,,,,,

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------,,,,,

bw=aaaa.bbbb.cccc, RU=USER5,RT=ARCHUsers, ST=USERS2, LC=USERS3, FT=PPI                                                                                                                                                                          ;2.16.840.1.113883.3.59.1:5678                                                                                                                                                                                                                                          ;PPASD                       ;          ;         45;          3;          3;          0;          0;          0;          2;          0`

-----


So let focus on your last post. Assuming you data is in columns A:C and that you want the output in column A, then it's derived that columns B:C should be deleted.


Formula solution:

a) In D2 type: =A2$&B2&C2

b) Select column D, copy, keep selection as selected, paste special values

c) Select columns A:C, delete

This is the solution recommended for a quick & dirty & once (or a few times use)


Macro solution:

-----

[pre]Option Explicit

Sub ThreeStoogesAndDArtagnan()
' constants
' declarations
Dim I As Integer
' start
' process
With ActiveSheet
I = 2
Do Until .Cells(I, 1).Value = "" And .Cells(I, 2).Value = "" And .Cells(I, 3).Value = ""
.Cells(I, 1).Value = .Cells(I, 1).Value & .Cells(I, 2).Value & .Cells(I, 3).Value
I = I + 1
Loop
Range(.Columns(2), .Columns(3)).Delete
End With
' end
Beep
End Sub

[/pre]
-----


Hope it helps.


Just advise if any issue.


Regards!

 

[Sajan]

Hello SirJB7,

Interesting name for your subroutine... It is more creative than the conventional "foo"!


Regards,

Sajan.

 

[SirJB7]

@Sajan

Hi!

I did not do it... (me dixit too!)

Regards!

 

[Hui]

Ikhan

Have you had a read of the Concat post

http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

Make sure you scan the comments as there are some gems in there as well

 

[IKHAN]

Thank you SirJB7 - Hui ..it worked

 

[SirJB7]

Hi, IKHAN!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!