Multiple Cell Concatenation

Sometime a task like Concatenation frustrates you because you would like to concatenate a range, and it takes lot of time doing those repetitive tasks. Well, here’s a way to get it simple. The procedure illustrated here allows you to choose the input range, concatenation character, and the output range. If you use the function, then you can enter the optional concatenate character (by default it is a comma (,)), and the input range. – Selva V Pasupathy

Public Sub ConCatwChar()
Dim sChar2bAdded As String, rngRng2bCated As Range, sOutput As String, rngTarget As Range, c As Range
On Error GoTo ConCatwChar_Error
‘You could use this line to return the concatenated string in this cell
‘Set rngTarget = ActiveCell

Set rngRng2bCated = Application.InputBox(prompt:=”Select the range you’d like to concatenate with a charcter”, _
                    Title:=”Select Range”, Type:=8)
                    
If rngRng2bCated Is Nothing Then Exit Sub
                    
‘You could use this line to set the default to a comma and remove the inputbox line
‘sChar2bAdded = “,”
sChar2bAdded = InputBox(“Enter the character you’d like to add between other cells”, “Enter Character”, “,”)

Set rngTarget = Application.InputBox(prompt:=”Select the range you’d like the output”, _
                    Title:=”Select Range”, Type:=8)

For Each c In rngRng2bCated
    sOutput = sOutput & c.Value & sChar2bAdded
Next c

sOutput = Left(sOutput, Len(sOutput) – Len(sChar2bAdded))
rngTarget = sOutput

On Error GoTo 0
   Exit Sub
  
ConCatwChar_Error:
    MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure ConCatwChar”
End Sub

 

Advertisements

1 Comment »

  1. […] Multiple Cell Concatenation […]

RSS feed for comments on this post · TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: