Author: Oscar Cronquist Article last 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:

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

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


Download excel file

Concatenate cells.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE