Combining data in columns

[Jezza25]

Hi,

Need to combine data from multiple columns together witha comma between each column. The formula I have is

=AB4&", "&AC4&", "&AD4&", "&AE4

The creates a result for the first line of Apple, Pear, Grape, Banana

however not all columns have all 4 columns populated, the data does move left to right but if only the first 2 columns are populated I get Apple, Pear,,,

I am seeking a formula so that after the last name I have something in it does not continue adding commas

ta

Jezza

 

[Joe4]

Is there always guaranteed to be at least one entry, and will it always appear in column AB?

If column AB will always have an entry, this should do what you want:

=AB4 & IF(AC4<>"",", " & AC4,"")  & IF(AD4<>"",", " & AD4,"") & IF(AE4<>"",", " & AE4,"")

 

[DRSteele]

If you are using Excel 2016 you should use function TEXTJOIN, which has an argument suppressing the addition of the character (like a comma) when a cell in the array is blank.

see here: https://www.youtube.com/watch?v=qALXjobSAMA

 

[Weazel]

if you don't have 2016.....

=SUBSTITUTE(TRIM(AB4&" "&AC4&" "&AD4&" "&AE4)," ",", ")

if you do....

=TEXTJOIN(", ",1,AB4:AE4)

 

[Rick Rothstein]

if you don't have 2016.....

=SUBSTITUTE(TRIM(AB4&" "&AC4&" "&AD4&" "&AE4)," ",", ")

What if a cell contains more than one word (for example "Gala Apple")?

Is there always guaranteed to be at least one entry, and will it always appear in column AB?

If column AB will always have an entry, this should do what you want:

=AB4 & IF(AC4<>"",", " & AC4,"")  & IF(AD4<>"",", " & AD4,"") & IF(AE4<>"",", " & AE4,"")

And if not...

=MID(IF(AB4<>"",", "&AB4,"")&IF(AC4<>"",", "&AC4,"")&IF(AD4<>"",", "&AD4,"")&IF(AE4<>"",", "&AE4,""),2,999)

 

[Weazel]

What if a cell contains more than one word (for example "Gala Apple")?

Well, its a few years old but I think the solutions it may still apply if there are multiple words in a single cell....

http://www.mrexcel.com/forum/excel-...ded-excel-newbie-**remove-last-comma-sum.html

 

[My Aswer Is This]

If you would like a Vba solution try this script

Your Quote:
"Need to combine data from multiple columns together witha comma between each column"
You did not say how many columns
With this script
It combines all the data in four columns into column "A" with a comma between each value
It continues to do this till it reaches the last filled cell in column "A"
You can change the number of columns by changing the value in the script marked in red
This script could be modified to accomplish this by combining all columns in one row depending on number of columns filled in that row. If you wanted that feature let me know.

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim b As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
    
        For b = 2 To[COLOR="#FF0000"] 4[/COLOR]
            If Cells(i, b).Value <> "" Then
            Cells(i, 1).Value = Cells(i, 1) & "," & Cells(i, b).Value
            End If
        Next
    Next
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Well, its a few years old but I think the solutions it may still apply if there are multiple words in a single cell....

http://www.mrexcel.com/forum/excel-...ded-excel-newbie-**remove-last-comma-sum.html

Did you see the formula I posted at the bottom of Message #5 yet?

 

[Joe4]

[COLOR=#333333]=MID(IF(AB4<>"",", "&AB4,"")&IF(AC4<>"",", "&AC4,"")&IF(AD4<>"",", "&AD4,"")&IF(AE4<>"",", "&AE4,""),2,999)[/COLOR]

You know, I tried the other way, trying to figure out how to drop the two right-most values first, but that doesn't work so well when you don't know the length.
I cannot believe I didn't think to go the other way, and just drop the first few characters...