Alternate to A1&B1&c1...&n1

[Eliezer]

Is there a more efficient command to combine many rows of
numbers into one cell other than A1&B1&C1&D1&E1&F1&...?
One way I can think of is to turn everything into text,
use CONCATENATE, and then TRIM if necessary. Is this
possible in Excel? Are there other ways? Thanks in advance
for all help.

 

[Frank Kabel]

Hi
using the operator '&' or CONCATENATE will produce the same results.
AFAIK Excel CONCATENATE can not process array. So a formula like
=CONCATENATE(A1:G1) ist not pssible in Excel.

You may take a look at the free add-in MOREFUNC.xll
(http://longre.free.fr/english). The function MCONCAT can combine text
arrays

HTH
Frank

 

[Niek Otten]

You could use a simple User defined function like this:

Function ConcatNum(a As Range) As String
For Each b In a
ConcatNum = ConcatNum & b
Next
End Function

Copy the text, open the VB Editor (Alt+F11), Insert>Module, paste the
function

You can now use the function with a any range, like =ConcatNum(A1:Z1)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Guest]

That worked! Thanks a bunch!

Eliezer

"Any sufficiently advanced technology is indistinguishable
from magic."