Need a variation (or alternative formula) to the "Concatenate" formula for this problem

[zicitron]

Hi all,

I have an Excel 2010 worksheet which contains numbers in Cell A1 to Z1. I need to merge all the numbers in those cells into one single cell but each number need to be separated by the "|" sign.

I know the Concatenate formula [=concatenate(A1,"|",B1,"|",C1.....] can do this but it will be tedious to write, given I am merging a large number of cells. Is there a variation to this formula that can perform this task? Or may be a different formula?

[Norie]

Why not try this in B2,

Please Login or Register  to view this content.

and in C2 copied across to Z2.

Please Login or Register  to view this content.

[Jonmo1]

You could do this..

In B2 put
=A1&"|"&B1

Then in C2 and filled right as far as needed..
=B2&"|"&C1

[Fotis1991]

tigeravatar has an excellent solution here.