looking for alternatives to the CONCAT function

[btparkhe1920]

Dear All,

Please suggest

alternatives to the CONCAT function

IFERROR(CONCAT(OFFSET(B5,0,0,L5,1)),"")),"")


I need alternatives to the CONCAT function for Office 13.

[zbor]

TEXTJOIN is better than CONCAT or CONCATENATE

Try this I5 (without helper column):

=IF(A5="","",TEXTJOIN(" ",TRUE,OFFSET(B5,0,0,IFERROR(MATCH(FALSE(),ISBLANK($A6:$A$10000),0),10000))))

[AliGW]

TEXTJOIN isn't in Excel 2013, though - see the last line of the opening post.

[zbor]

Ah, ok..

CONCAT will return the same:

=IF(A5="","",TRIM(CONCAT(OFFSET(B5,0,0,IFERROR(MATCH(FALSE(),ISBLANK($A6:$A$10000),0),10000))&" ")))

Don't know how to replace it then

[Glenn Kennedy]

What is the MAXIMUM number of rows that will need to be concatenated. This allows for 4. You can see the pattern, if you need to add more.


=IF(H5<>"",B5&IF(H6="",B6,"")&IF(H7="",B7,"")&IF(H8="",B8,""),"")

[btparkhe1920]

Thank you very much

[MrShorty]

Are you limited to Excel? Google sheets and Gnumeric both provide a TEXTJOIN() type of function.

Are you limited to native Excel functions? VBA has a built in Join() function that is very similar to CONCAT() and TEXTJOIN(), so it is usually pretty straightforward to code a UDF that will do the same thing.