Forum Discussion
Transform several rows into one muli-line cell
PeterBartholomew1 Very many thanks for your quick reply. I'm using 365, so your solution should have the best chance.
I always feel uncomfortable pasting code of which I have absolutely no understanding, so I'll first search for some Idiot's basic VBA guide to gain a bit more confidence. But I know the sooner I do this, the better (and I'll keep you posted....)
Meanwhile -- just a thought -- I have some basic understanding of regular expressions, which don't seem to be handled directly in Excel, so I was wondering whether, if I open the sheet in LibreOffice (whose search/replace includes fussy but useable RegX) there might be a way to achieve my desired result within a framework I can more easily get my head around.
In addition to clearly being a VBA Master, are you also a RegX Wizard?
First, I had better address a misconception! Believe it or not, the formula I used was not VBA; it was a regular Excel formula, entered directly to a worksheet cell!
Riny_van_Eekelen implemented the formula as well as his Power Query solution, so you might like to take a look at his workbook.
I have some experience of RegEx either from VBA scripting library (accessed as a UDF) or through the use of Charles Williams's paid FastExcel add-in. Despite that I would describe it as somewhat outside my comfort zone!
- PeterBartholomew1, Riny_van_Eekelen, SergeiBaklan, Willy Lau, amit_bhola:
If you'll excuse the Harry Potter reference, I feel like a new student at Hogwarts College suddenly finding himself overawed in the presence of powerful Wizard Masters, and I'm amazed and grateful for the time and trouble they are prepared to take to help.
I use often use spreadsheets (I started with Supercalc on CP/M), but only in the most superficial way, with very little formula use, so unfortunately I'm totally out of my depth with the solutions presented.
I had never heard of Power Query, UDF etc., and didn't even know that to view an Excel formula properly indented, all I had to do was drag the bottom of the formula bar down.)
It will take me quite a while to begin to assimilate what lies behind the unfamiliar spells the Wizards are suggesting, so I'm just taking this opportunity to thank you all.
PeterBartholomew1: I'm not sure where to paste your formula -- if I paste it into the next free column to the right in my sheet, I just get #NAME?. Right now, the formula is just a mystery black box to me, but I don't understand why it doesn't directly reference any column letter or heading.You can paste the formula anywhere you wish to place the result. I do not use relative references so the placement makes no difference except to the aesthetics. What you are missing is that the words Album, Track, and Artist are defined Names that refer to columns of your table [set by using Name Manger or the Name box to the left of the formula bar].
Riny_van_Eekelen implemented my formula as well as the PQ solution but (sensibly) chose to convert your source data into a Table in order to use structured references.