## Concatenate cell values

*Article updated on March 12, 2018*

**Arielle asks: **

i have a formula in row a from A1:Z1 that displays "" if there is an error. I need to then take that row and combine or concatenate all the cells into one cell and have each cell separated by " / ". I only want the " / " to display if there is text in the cell, not the "". Let me know if this is possible!

ex. A1: AA B1: BB C1: D1: ------> Z1:

(C1-Z1 display a blank ("") or [iferror(,"")])

A3: AA / BB

NOT>> A3: AA / BB / / / / / /.... /

*You can find her question here:*

*http://www.get-digital-help.com/2009/04/08/merge-two-lists/comment-page-1/#comment-4893*

**Answer #1:** No, vba is required. VBA Join function

**Answer #2:**

**Formula in B1:**

=A1 + Enter

**Formula in B2:**

=IF(A2<>"", B1&"/"&A2, B1) + Enter.

Copy formula in cell b2 and paste it down as far as needed.

**Answer #3**

- Select a cell, example cell D3.
- Type: =IF(A1:A30<>"", A1:A30&"/", "") in formula bar or cell.
- Press F9.
- Type
*CONCATENATE(TRANSPOSE(*in front of all characters in formula bar. - Type
*))*after all characters in formula bar. - Remove curly brackets {} in formula bar.
- Press Enter.

Cell D3 now contains: AA/BB/CC/DD/EE/FF/GG/HH/II/JJ/KK/LL/MM/NN/OO/PP/QQ/RR/SS/TT/UU/VV/XX/YY/ZZ/

### Download excel file

Concatenate cells.xlsx

(Excel 2007 Workbook *.xlsx)

**Functions in this article:**

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### 6 Responses to “Concatenate cell values”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Thanks so much!!!

If anyone is interested in a VBA solution, here is a function I have posted to the newsgroups in the past. It will take the cells in a single column, or the cells in a single row, and concatenate their content placing an optional delimiter between the non-empty cell values (the default delimiter when none is specified is the empty string).

Function JoinString(varRange As Range, Optional varDelimiter As String) As String

With WorksheetFunction

If varRange.Columns.Count = 1 Then

JoinString = .Trim(Join(.Transpose(varRange.Value), varDelimiter)) 'Join down

Else

JoinString = .Trim(Join(.Index(varRange.Value, 1, 0), varDelimiter)) 'Join across

End If

JoinString = Replace(Replace(.Trim(Replace(Replace(JoinString, " ", Chr(1)), _

varDelimiter, " ")), " ", varDelimiter), Chr(1), " ")

End With

End Function

Actually, I posted the wrong formula. This is the one I should have posted as it is more flexible as you can specify a single range (row, column OR rectangular) or several discontiguous ranges (each of which can be a row, column or rectangular). However, this function requires you to specify the delimiter. If you want to specify no delimiter, use the empty string ("") or just omit it; however, you must include the argument delimiting comma.

Rick Rothstein (MVP - Excel),

Thanks for your contribution!!

Hi,

this is GREAT...

when I try answer #3 above, it returns an error. it think it's because a1:a30&"/" [F9] returns the string of values with a ";" in between instead of a comma, and the CONCATENATE function doesn't recognize it?

Also, I would REALLY like to return the list of results from a VLOOKUP function that has multiple matches in a list...

Thanks so much for being he Oscar!

SARAH,

you are right! Try this:

=TRANSPOSE(IF(A1:A30<>"", A1:A30&"/", ""))

I believe it depends on regional settings.

Thank you for commenting!