• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

CONCATENATE --> 108 cell contents

Hi Gurus,

Greetings for the day.

In current project need to concatenate contents of 108 cells into single content separated by ";" I tried with formula ==> CONCATENATE(A2 : DD2&";") but getting "#VALUE" error.

Please guide me on the missing point in above.

Vignesh V
 
If you have Office 365 or Excel 2019, you can use TEXTJOIN function, something like this :

=TEXTJOIN(";",,A2: DD2)

Otherwise you need helper columns to concatenate 108 cell contents

Regards
 
If you have Office 365 or Excel 2019, you can use TEXTJOIN function, something like this :

=TEXTJOIN(";",,A2: DD2)

Otherwise you need helper columns to concatenate 108 cell contents

Regards
Hi

Thanks a lot.This worked.

One query is that i have cell values as 00 [zero zero] when above formula is applied cell shows only one zero and numbers like 1,2 need to be shown as 01,02 same to be shown in the TEXTJOIN also is this possible?

Vignesh V
 
The CONCATENATE is a valid, but pointless, formula when I enter it. The problem with CONCATENATE was that it never concatenated the terms of an array. CONCAT overcomes that limitation but, since you are using an array operation to insert separators, you will have an additional final separator.

To control the number formats prior to concatenation, you could use the TEXT function. The number format allows individual formats for positive, negative and zero values.

= TEXTJOIN( "; ", FALSE, TEXT(range, "00.00;-00.00;00") )

You could even use the number format to introduce the separator if you so choose
= CONCAT( TEXT(range, "00.00\; ;-00.00\; ;00\; ") )

postscripts
1. The problem with your first formula is probably that it needs array entry to avoid implicit intersection
2. I have shown comma "," parameter separators which you may need to change to semi-colons ";".
 
Last edited:
@bosco_yip
Why the INDEX(array,0)? The function makes no difference when I implement it (you usually have a reason!).

@Vignesh Veerasamy
I misread your 1,2 as a continental locale for a decimal rather than two integers. This reduces my formulae to
= TEXTJOIN( "; ", FALSE, TEXT(range, "00") )
or
= CONCAT( TEXT(range, "00\; ") )
much the same as Bosco's.
 
@Peter Bartholomew

1] I am using Excel 2019

This formula : =TEXTJOIN(";",,TEXT(range,"00")) need array entry

And, this formula =TEXTJOIN(";",,INDEX(TEXT(range,"00"),0)) normal entry

2] I know you are using Office 365. So, =TEXTJOIN(";",,TEXT(range,"00")) normal entry is alright !!

Regards
 
@bosco_yip
Thanks for the explanation; I am clearly losing my grip on 'traditional' Excel!
It probably doesn't help that I had schooled myself to use CSE as the default way to commit a formula even in Office 2010. I bought a 365 licence specifically to get improved array functionality (I had been bitching about the lousy user-experience offered by traditional array formulas for years).

What troubles me now is that MS does not seem to be considering making Office2019 users an 'offer they can't refuse' to update to access the initial tranche of array functionality. Development techniques on the two versions are so different that our community is heading towards an unbridgeable fork and MS using the new functionality to force a move to a subscription model does not treat users with due respect.
 
Back
Top