Copy and Paste function

[TamIam]

Good day!

I need a formula that I can use to take values in one row but different
columns and merge them together into one line with the pipe symbol (|)
separating the column values, for example:

Column:
A B C D E F

100 141 154 160 175 182

Becomes
Column
A B C D E F
100|141|154|160|175|185

I have been using the "&" function, but I can't seem to get the formula right.

 

[Teethless mama]

=CONCATENATE(A1,"|",B1,"|",C1,"|",D1,"|",E1,"|",F1)

 

[TamIam]

Thank you - is there anyway I can do this without typing the individual cell
addresses? Perhaps by autofilling a command that you know of?

 

[Gord Dibben]

You can use this UDF which ignores blank cells in the selected range.

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & "|"
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is: =concatrange(A1:F32)

If you're not familiar with VBA and macros/functions, see David McRitchie's site
for more on "getting started".

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

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

In a cell enter the formula as shown above in Usage is:


Gord Dibben MS Excel MVP