5

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 '14 at 19:51
  • I provided a simple example. Thanks for the input @CharlieRB – sgp667 Oct 21 '14 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 '14 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 '14 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 '14 at 21:02
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|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.