Concatenate cell values in excel
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
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
August 23rd, 2010 at 9:41 pm
Thanks so much!!!
August 25th, 2010 at 2:17 am
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
August 25th, 2010 at 2:36 am
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.
Function ConCat(Delimiter As Variant, ParamArray _
CellRanges() As Variant) As String
Dim Cell As Range, Area As Variant
If IsMissing(Delimiter) Then Delimiter = ""
For Each Area In CellRanges
If TypeName(Area) = "Range" Then
For Each Cell In Area
If Len(Cell.Value) Then ConCat = _
ConCat & Delimiter & Cell.Value
Next
Else
ConCat = ConCat & Delimiter & Area
End If
Next
ConCat = Mid(ConCat, Len(Delimiter) + 1)
End Function
August 25th, 2010 at 6:08 am
Rick Rothstein (MVP - Excel),
Thanks for your contribution!!