I'm trying to join 30 individual text stings (each in a different cell, so concatenate might work) into one cell to facilitate printing. My goal is for the final cell to contain all of the original strings, each separated by a line return, with extra space from empty cells eliminated (to print as part of a report). I'm also working with Excel 2008 for the mac which no longer supports macros (ARGH!!).
Any thoughts would be appreciated!
Would something like the below work ?
Assumes you don't have ^ in any of your 30 strings...
Below is obviously limited to show just 3 cells (else we'd be putting up a LONG formula), in essence for each cell you want to replace the ... with the remaining cells where for each remaining cell you have:
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(A1," ","^")&" "&SUBSTITUTE(A2," ","^")&" "&SUBSTITUTE(A3," ","^")...)," ",CHAR(10)&CHAR(13)),"^"," ")
where Ax is the cell reference of the cell you're concatenating.&" "&SUBSTITUTE(Ax," ","^")
You will need to ensure result cell is set to have Wrap Text on... and obviously set the height of the row accordingly.
I think that might work... I don't have 2008 I'm afraid so I can't test...
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks