'Code' or 'Function' to concatenate into single cell ?

[Maverick27]

Hi - anyone care to share a piece of code (or function) to "concatenate" the data in cell cols. from A to L into a single cell M that should read as below. It should also have commas, inverted commas & brackets.

('01','OSR00001','25W B22 GLS LAMP FROSTED','OSGL25B22','735','1617','Frosted','Pin','B22','-','-','25')

Worksheet sample link:
http://imageshack.us/photo/my-images/69/excelnz.jpg/

Thanks

 

[VoG]

Try

Function Concat(r As Range) As String
Dim c As Range
Concat = "("
For Each c In r
    Concat = Concat & "'" & c.Value & "'" & ","
Next c
Concat = Left(Concat, Len(Concat) - 1)
Concat = Concat & ")"
End Function

Use like

=concat(A1:L1)

 

[Maverick27]

Thanks - is it a Macro or do i insert it as piece of code ?

Have a look @ snapshot of the worksheet.
http://imageshack.us/photo/my-images/69/excelnz.jpg/

Do you mind giving me the exact code?

Thanks.

 

[VoG]

That is the exact code. Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu then paste in the code. Press ALT + Q to close the code window.

On the worksheet enter a formula like

=concat(A1:L1)

 

[Maverick27]

Working beautifully - Thanks.

But how do i show it as a 'TEXT' in the cell ? See link below.
http://imageshack.us/photo/my-images/33/excel2q.jpg/

 

[VoG]

Yes.

 

[Maverick27]

But how do i show it as a 'TEXT' in the cell ? See link below.
http://imageshack.us/photo/my-images/33/excel2q.jpg/

 

[VoG]

I don't know what you mean. My results

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	A	B	C	D	E	F	G	H	I	J	K	L	('A','B','C','D','E','F','G','H','I','J','K','L')
Sheet2

There is little point posting links to images. Try using Excel Jeanie to post a shot of your sheet that can be copied and pasted into Excel as data.

 

[Maverick27]

What i'm simply saying is that when i place cursor in a cell of COL M , i see the formula.
But, i want to see the TEXT itself.

 

[VoG]

Select the values in column M and copy them. Edit > Paste Special, tick Values and click OK.