6

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

Improve this question
5
  • 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?
    – barry houdini
    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

Reset to default
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.
Improve this answer
1

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.

=TEXTJOIN(", ",FALSE,SORT(UNIQUE(A1:A100)))

Improve this answer

You must log in to answer this question.

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