Author: Oscar Cronquist Article last updated on March 27, 2019

This article is for Excel users that don't have the latest Excel version or can't or don't want to use VBA code. The image above shows you data in column B and C. I want to concatenate adjacent value to size "M" into cell F4. They are "CD", "IJ" and "OP", in the picture below.

I highly recommend using the TEXTJOIN function if you bought the Office 365 subscription or the user-defined function demonstrated here: Lookup and return multiple values concatenated into one cell It is what the CONCATENATE function should have been from the beginning.

Instructions

  1. Copy (CTRL + c) this formula:
    =TRANSPOSE(IF(B3:B11=F3,C3:C11,""))
  2. Double click on cell F4
  3. Paste (Ctrl + v) formula to cell F4
  4. Select the entire formula in the formula bar
  5. Press Function key F9 and the formula is converted to an array of constants:
    ={"","CD","","","IJ","","","OP",""}
  6. Delete the equal sign = in the formula bar and then press Enter
    {"","CD","","","IJ","","","OP",""}
  7. Select cell F4
  8. I am now going to delete all empty characters in the array, press CTRL + H to open "Search and Replace" Dialog box
  9. Type in Find what: "", and nothing in Replace with:
  10. Click on "Replace" button
  11. Type in Find what: ,"" and nothing in Replace with:
  12. Click "Replace" button once again
  13. Click Close button

Add all values in array

The final thing to do is to use the concatenate function to add all values into one text string.

  1. Double click on cell F4
  2. Delete the curly brackets from the formula: {}
  3. Add this: =CONCATENATE(
  4. and then an ending parentheses )
  5. Press Enter

Add delimiting character

Perhaps you want a delimiting character between values, right after you have converted the formula to an array of constants (step 5, above), do this:

  1. The array looks like this: ={"","CD","","","IJ","","","OP",""}
  2. Add an ampersand and then a delimiting character with quotation marks.
    ={"","CD","","","IJ","","","OP",""}&"|"
  3. Select the formula and press function key F9, the formula now looks like this:
    ={"|","CD|","|","|","IJ|","|","|","OP|","|"}
    The ampersand has added the delimiting character to all values in the array.
  4. Continue with step 6 above. Tip! To delete empty values in array, Search and Replace with these values "|", and ,"|"

Download Excel file through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.
* You will also get a weekly newsletter, unsubscribe anytime!