CODEWITHSUNDEEP
excelfunction
zishan paya
zishan paya

Reputation: 513

Excel Function: How do I concatenate String ignoring comma and NULL values

How do I concatenate below String ignoring comma and NULL values from different cell from A1 to F1.

 NULL   NULL    Welcome  NULL    NaiSen     NULL

My final result should be: Welcome,NaiSen

I tried using below function but got #VALUE!

=CONCATENATE(IF(SEARCH("NULL",A1:F1),"",""))

Upvotes: 1

Views: 2378

Answers (2)

vzczc
vzczc

Reputation: 9380

You could use a user defined VBA function like this

Function concatFunc(v As Range) As String
    Application.Volatile True
    Dim c
    Dim s As String
    For Each c In v
        If IsError(c) = False And CStr(c) <> "," Then
            s = s & CStr(c)
        End If
    Next
    concatFunc = s
End Function

Insert the code in VBA like this: Alt-F11, insert module, paste in the code

Then call the function from the worksheet like this example: =concatFunc(A1:F1)

It then works like any other built-in Excel function.

Upvotes: 1

Mohammed
Mohammed

Reputation: 313

Try the below idea: 

    =CONCATENATE(IF(A1="null","",CONCATENATE(SUBSTITUTE(A1,",",""),",")),
IF(B1="null","",CONCATENATE(SUBSTITUTE(B1,",",""),",")),
IF(C1="null","",CONCATENATE(SUBSTITUTE(C1,",",""),",")),
IF(D1="null","",CONCATENATE(SUBSTITUTE(D1,",",""),",")),
IF(E1="null","",CONCATENATE(SUBSTITUTE(E1,",",""),",")),
IF(F1="null","",SUBSTITUTE(F1,",","")))

Upvotes: 1

Related Questions