Convert Array to String with Excel Formula, is it possible?

I know how this would be done with VBA, but I have to share my workbook, and I have to assume that other people will not be able to enable VBA written by me.

For Example I have following array: { "a","b"} I would like my formula to output ab

Origianlly I was thinking about using CONCATENATE({"a","b"}) but that resulted in a

  • This is too vague. Edit your question to elaborate. Use of code samples, formulas or screen shots would be very beneficial to helping you.
    – CharlieRB
    Oct 21, 2014 at 19:51
  • I provided a simple example. Thanks for the input @CharlieRB
    – sgp667
    Oct 21, 2014 at 19:56
  • 2
    This isn't easy to do with a formula - there is no native function in Excel that can concatenate a range or array - I assume that there might be more than 2 values? Oct 21, 2014 at 20:38
  • Where do you have the array? What format is it in? On the sheet? The result of a formula?
    – Excellll
    Oct 21, 2014 at 20:58
  • Yes its actually a list with couple thousand items, I am getting it as a named range from someone else's workbook.
    – sgp667
    Oct 21, 2014 at 21:02

2 Answers 2


This can be done with formulas however, catting a list of over 1000 strings seems likely to exceed excel limits for strings or give an unwieldy result. Caveat aside.

  • Make a hidden worksheet called Zone or any other name you want.
  • if named range you are getting is called "x" then in the A column put "Count" and then in the cell under it enter =counta(x) ' this gives us the size of the array.
  • name the cell with the actual number "Cnt"
  • then in the c column starting at c3 enter in "Data " this is a header
  • at c4 enter =index(x,1)
  • at c5 and extend past size of possible range =+IF(ROW()>Cnt+3,"",C4&INDEX(x,ROW()-3))

  • at d2 enter "Answer" and at d3 enter =+OFFSET($C$3,Cnt,0)

  • name the d3 cell as Ans and you can use this where you need. possible mod is to add a "," such as c4&","&index(x,row()-3)) use a iferror instead of the if(row()>cnt+3 to mask errors if have copied the formula to 2000 cells, have a test if(cnt>2000,"Error named range too large - Call John Doe", "") to cause a error message to appear if underestimated the size of the named range.

2020 Answer (actually post-2016 answer)

=TEXTJOIN("",FALSE,{"a","b"}) will return "ab"

Old question, but came up high in my search results. Since 2016 Excel has a TEXTJOIN() function which takes a delimiter, an ignore blanks flag, and an array, range, list of cells, list of strings, etc.

For example, the following will return an ordered list of unique values from the range.


You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .