• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 92
  • Last Modified:

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
0
Alex Campbell
Asked:
Alex Campbell
1 Solution
 
Mike in ITIT 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now