don\'t show the commas in cells where there is nothing to concatenate I have a s

Question

don't show the commas in cells where there is nothing to concatenate

I have a spreadsheet that concatenates values pulled in froom another spreadsheet (it's addresses). The address info is in separate columns in the original spreadsheet; I can't change this. Forr certain rows, there will be nothing returned (ie no addresses info in the original spreadsheet). For those rows, I want nothing to show, but the commas that are part of the concatenate formula are showing.

Is there an easy way for theese commas not to shoow?

Thanks!!!

Explanation / Answer

Activate the workbook with the formulas.

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module.

Copy the following code into the module:

Function Concat(rng As Range, Optional sep As String = ", ") As String
    Dim ret As String
    Dim cel As Range
    For Each cel In rng
        If cel.Value <> "" Then
            ret = ret & sep & cel.Value
        End If
    Next cel
    If ret <> "" Then
        ret = Mid(ret, Len(sep) + 1)
    End If
    Concat = ret
End Function

Switch back to Excel.

Let's say that you have a formulaa that concatenatees A2, B2, C2, D2 and E2 from Sheet2.

Change the formula to

=Concat(Sheet2!A2:E2)

This formula can be filled down.

Make sure that you save the workbook aas a macro-enabled workbbook (.xlsm or .xlsb)

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.