Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Concatenating Values from a Variable Number of Cells.

# Concatenating Values from a Variable Number of Cells

Written by Allen Wyatt (last updated December 15, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365

Pam has two columns of data. In column A there are simple identifiers, such as A, B, C, etc. In column B there are a series of integer values. She can sort the data by the identifier and, secondarily, by the integer values. Now she wants, in column C, to have a formula that will concatenate all the integer values for a particular identifier. Thus, if A1:A4 all contain the identifier A, then in cell C1 she would like to have all the values in B1:B4 concatenated and divided by commas, such as "11, 17, 19, 25". Since the number of rows for each identifier can be different, Pam isn't sure how to go about the concatenation.

The easiest way to accomplish this is to use a macro, which can be created as a user-defined function. Here's an example:

```Function CatSame(c As Range) As String
Application.Volatile
sTemp = ""
iCurCol = c.Column
If iCurCol = 3 Then
If c.Row = 1 Then
sLast = ""
Else
sLast = c.Offset(-1, -2)
End If
If c.Offset(0, -2) <> sLast Then
J = 0
Do
sTemp = sTemp & ", " & c.Offset(J, -1)
J = J + 1
Loop While c.Offset(J, -2) = c.Offset(J - 1, -2)
sTemp = Right(sTemp, Len(sTemp) - 2)
End If
End If
CatSame = sTemp
End Function
```

This function basically takes a value that is passed to it (a cell reference) and verifies that the cell reference is for column C. If it is, then it starts to concatenate values from column B based on the values in column A. It only returns the string of concatenated values if the value in column A is different than the value in the row above it. Assuming your identifiers are in column A and your values to be concatenated are in column B, you could place the following in column C:

```=CatSame(C1)
```

Copy this down as far as necessary in column C and you end up with exactly what Pam wanted.

A more versatile function would be one that would function somewhat like VLOOKUP but bring back a concatenated list of values that match whatever you are looking up. Consider the following function:

```Function VLookupAll(vValue, rngAll As Range, _
iCol As Integer, Optional sSep As String = ", ")
Dim rCell As Range
Dim rng As Range
On Error GoTo ErrHandler

Application.Volatile
Set rng = Intersect(rngAll, rngAll.Columns(1))
For Each rCell In rng
If rCell.Value = vValue Then _
VLookupAll = VLookupAll & sSep & _
rCell.Offset(0, iCol).Value
Next rCell

If VLookupAll = "" Then
VLookupAll = CVErr(xlErrNA)
Else
VLookupAll = Right(VLookupAll, Len(VLookupAll) - Len(sSep))
End If
ErrHandler:
If Err.Number <> 0 Then VLookupAll = CVErr(xlErrValue)
End Function
```

This function takes up to four arguments. The first is the value you want to match in your lookup. In Pam's instance, this would be the identifier you want, such as A, B, or C. The second argument is the range of cells in which to look for the matches (column A in this case). The third argument is an offset (from the range in the second argument) that represents the values you want concatenated. You can use the function in this manner:

```=VLookupAll("B",A1:A99,1)
```

If you want to specify a different delimiter between values, you can do it using the optional fourth argument. For instance, the following returns a string where a dash separates each value:

```=VLookupAll("B",A1:A99,1,"-")
```

The solutions so far have focused on using macros. The reason for this is relatively simple: There isn't a formula-based solution that can do what Pam needs. Using nested IF statements to evaluate what is in column A won't work well because you are limited in how deeply IF statements can be nested.

You could use a formula and an intermediate result if you don't mind having the concatenated values be at the last instance of an identifier in column A. Start by putting this formula in cell C1:

```=B1
```

This formula should go into cell C2:

```=IF(A2=A1,C1 & ", " & B2, B2)
```

Copy this formula down as many rows as necessary. What you end up with is an increasingly long series of concatenated values in column C, with the longest in each run being on the same row as the last sequential identifier in column A. You can then put the following in all the applicable cells of column D:

```=IF(LEN(C2)>LEN(C1),"",C1)
```

This formula only displays the longest strings from column C, which is what Pam needed to begin with.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9199) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Concatenating Values from a Variable Number of Cells.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Deleting Worksheets in a Macro

Processing workbooks using a macro often involves the possible creation and subsequent deletion of worksheets. When it ...

Discover More

Files Opening Slowly If Many Files Exist

Managing large numbers of documents in Word can lead to some interesting challenges. One potential challenge is that your ...

Discover More

Replacing Hidden Text

Word allows you to format text so it can be easily hidden from view and from printing. If you want to convert the hidden ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

##### More ExcelTips (ribbon)

Checking for Duplicate Rows Based on a Range of Columns

When working with data in Excel, you might want to figure out which rows of data represent duplicates of other rows. If ...

Discover More

Starting Out Formulas

When you enter a formula from the keyboard, Excel only knows it is a formula if you start it with an equal sign. You can ...

Discover More

Returning Least-Significant Digits

Do you ever have a need to return just a few digits out of a number? This tip shows different formulas you can use to ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. Youâ€™ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 7?

2019-12-29 08:40:29

Peter Atherton

If you do not have The TEXTJOIN function in your version you can use my version at
https://excelribbon.tips.net/T004629_Ways_to_Concatenate_Values.html

2019-12-28 19:52:25

Andy

Another way which is similar to Allen's last method above is to use the TEXTJOIN function - it's ideal for concatenating a range of cells. According to https://support.office.com/en-us/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c, TEXTJOIN is available for Excel for Office 365, Excel for Office 365 for Mac, Excel for the web, Excel 2019, and Excel 2019 for Mac.

The suggested setup is shown at https://imgur.com/a/dSQ1ZZ4.

It uses a helper column which can be hidden.

It also doesn't require the list to be sorted by category.

##### This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.