Need custom function to combine cells in Excel 2010

I need a function where I can give a range such as A2:D2
and have it combine them the text values.
I would also like an option to add a divider.

Example of Custom Function NeededCombine.xlsx
LVL 1
Alex CampbellAsked:
Who is Participating?
 
Mike in ITConnect With a Mentor IT System AdministratorCommented:
You will have to create your own function in VBA like this:

Function Combine(rng As Range, Sp As String)
Dim cell As Range
Dim Result

For Each cell In rng
    If Sp <> "" Then
        If cell.Value <> "" Then
            Result = Result & Sp & cell.Value
        End If
        Combine = Right(Result, Len(Result) - 1)
    Else
        If cell.Value <> "" Then
            Combine = Combine & cell.Value
        End If
    End If
Next cell
End Function

Open in new window


Then you use it just like a regular function. Mine needs 2 inputs so it would be used like this:
=combine(A9:D9,"")

Open in new window

or this
=combine(A7:D7,"/")

Open in new window


This will work even if it is multiple rows as well as multiple columns.
0
 
MacroShadowCommented:
Try this:
Public Function CustomCombine(rng As Range, Optional strDivider As String) As String
    Dim cell As Range
    Dim strTemp As String
    If rng.Rows.Count = 1 Then
        For Each cell In rng.Cells
            strTemp = strTemp & cell.Value & strDivider
        Next
    Else
        ' more than 1 row, don't proccess
    End If
    CustomCombine = strTemp
End Function

Open in new window

0
 
Alex CampbellAuthor Commented:
Great, thanks!
0
All Courses

From novice to tech pro — start learning today.