Concatenate a Range of Strings in Excel

Concatenate Range

Most of us have used the Concatenate() function in Excel. It is a neat function that saves you the trouble of keying in “&” between the range addresses. The only thing that would make this awesome would be the ability to concatenate an entire range. That would be way more useful than having to comma separate each cell that you want to concatenate.

Consequently, I wrote this function that will concatenate all the cells in a specified range. It also has the option to include a “separator” text and it ignores blank cells.

'Function to Concatenate the values stored in every Cell of a Range
'Author     : Ejaz Ahmed
'Date       : 14 November 2013
'Website    :
Function ConcRange(ByRef WhichRange As Range, _
            Optional ByVal Seperator As String = vbNullString) As Variant

'Begin by setting the ConcRange to a blank string
ConcRange = vbNullString

'Declare a Range object to use in the For Each Loop
Dim rngCell As Range

'For Each loop concatenates all the cells in the range
For Each rngCell In WhichRange
    If ConcRange = vbNullString Then
        If Not rngCell.Value = vbNullString Then
            ConcRange = CStr(rngCell.Value2)
        End If
        If Not rngCell.Value = vbNullString Then
            ConcRange = ConcRange & Seperator & CStr(rngCell.Value2)
        End If
    End If
Next rngCell

End Function

Below is screenshot of a spreadsheet illustrating the use of this Functions:

Concatenate Range
Example : Concatenate Range

Published by Ejaz

I specialized in actuarial science in my masters’ program and I am determined to become a qualified actuary. I invest extra time and effort into creating intuitive spreadsheet applications that improve productivity at the workplace. I also enjoy spending my leisure time creating digital art; I publish my designs on my art in this blog named Designecdotes. Most of the people who have dramatically influenced my life were enthusiastic academicians. I believe that the greatest gift in life is having a nurturing mentor, and I would like to be that for someone. I owe it to my future mentees to be the best at what I do, and I consciously direct my endeavors at gathering what I consider essential traits of a seasoned mentor.

If you liked it, let me know. If you didn't make sure you let me know!

Fill in your details below or click an icon to log in: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: