Help needed with CONCATENATE Function.

[Chris Mitchell]

I have a column of data where each cell contains a unique 8 digit number.



I want to be able to put all of these numbers into a single string, with
each number being separated by a ',', e.g. xxxxxxxx,yyyyyyyy,zzzzzzzz



I know I could put a ',' in an empty cell, say C5 and use

=CONCATENATE(A1,C5,A2,C5,A3,C5....)



But with 112 rows this would be time consuming and would bust the 30 text
strings that the CONCATENATE function allows.



I'm sure there must be an easier way to do this but don't know what it is.
Any suggestions gratefully received.

 

[Pete_UK]

Instead of the function, use the concatenation operator, &:

=A1&C5&A2&C5&A3&C5....

still tedious ...

If any of your numbers have leading zeros and are not text values,
then you will have to use:

TEXT(cell,"00000000")

to preserve them as 8 digits.

Hope this helps.

Pete

 

[Guest]

Use a helper column to concatenate a "," onto the end of each of your numbers,
then in F1 type the cell where you want the big concatenation to be.....
then highlight the cells with the number-comma values and fire this
macro.......

Sub ConcatenateSelection()
Dim rng As Range
Dim strConcat As String
For Each rng In Selection
strConcat = strConcat & rng.Text
Next
Range(Range("f1").Value) = strConcat
Range(Range("f1").Value).Select
End Sub

The big concatenated string will be put in whatever cell address you put in F1

Vaya con Dios,
Chuck, CABGx3

 

[Chris Mitchell]

Thanks Chuck, this will be another learning curve for me as I'm not up on
macros so have to learn how to include the text given.

Should 'f1' in the macro be 'F1', i.e. a reference to cell F1 or does the
case make no difference?

 

[Guest]

The case of the "F" does not matter, except for appearance............
See here for help getting started with macros.......

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Vaya con Dios,
Chuck, CABGx3

 

[Rick Rothstein \(MVP - VB\)]

I have a column of data where each cell contains a unique 8 digit number.

I want to be able to put all of these numbers into a single string, with
each number being separated by a ',', e.g. xxxxxxxx,yyyyyyyy,zzzzzzzz

I know I could put a ',' in an empty cell, say C5 and use

=CONCATENATE(A1,C5,A2,C5,A3,C5....)

But with 112 rows this would be time consuming and would bust the 30 text
strings that the CONCATENATE function allows.

Using a helper column (say column B), put this in B1..

=TEXT(A1,"00000000")

and put this in B2...

=B1&","&TEXT(A2,"00000000")

and copy it down as far as needed.

Rick

 

[Chris Mitchell]

Thanks Rick, does exactly what it says on the tin.