Conditional Concatenate inserting or not inserting underscores depending on cell value

I'm a beginner on excel and I'm trying to get the following result:

    Col1   Col2   Col3   Col4
    John   Jim    Tom    Sarah
    Ann    Betty  blank  blank
    Lee    Sue    George blank

What I want:

    John_Jim_Tom_Sarah
    Ann_Betty
    Lee_Sue_George

What I'm getting:

    John_Jim_Tom_Sarah
    Ann_Betty__
    Lee_Sue_George_

Do someone know how to solve it?

Luca

Posted 2018-09-27T15:51:48.953

Reputation: 11

You should be able to use something like IF(ISBLANK(A1,"","_"&A1)) to return the underscore only if A1 is not blank. – Worthwelle – 2018-09-27T16:00:17.257

1What version of Excel do you have? If it's a newer version, you can use TEXTJOIN() – BruceWayne – 2018-09-27T16:00:21.357

Answers

Use TEXTJOIN:

=TEXTJOIN("_",TRUE,A2:D2)

If you do not have TEXTJOIN you will need to do something like this:

=MID(IF(A2<>"","_"&A2,"")&IF(B2<>"","_"&B2,"")&IF(C2<>"","_"&C2,"")&IF(D2<>"","_"&D2,""),2,99)

Scott Craner

Posted 2018-09-27T15:51:48.953

Reputation: 16 128

Here's a link to a UDF for TextJoin :D – BruceWayne – 2018-09-27T16:04:10.100

1@BruceWayne hey, I recognize that. :) – Scott Craner – 2018-09-27T16:04:53.873

It worked perfectly with TEXT.JOIN – Luca – 2018-09-28T09:44:22.533

IF you do not have the option of using TEXTJOIN you can use a mixture of CONCATENATE, LEN & LEFT:

Assuming your data starts in A2 use:

=LEFT(CONCATENATE(A2,"_",B2,"_",C2,"_",D2),LEN(CONCATENATE(A2,"_",B2,"_",C2,"_",D2))-(4-COUNTA(A2:D2)))

Its basically doing the same as TEXTJOIN then calculating the length of the string, and misusing off the characters to the right for each blank cell in the range, which would be the unwanted underscores.

PeterH

Posted 2018-09-27T15:51:48.953

Reputation: 5 346