Lets say i have column A
A
"|---|----|--|-|----|-------|--
--|----|--|"
|-----| | | | |------
----------|--------------
-----|----| |"
And i want to make it like this:
B
|---|----|--|-|----|-------|----|----|--|"
|-----| | | | |-----------------------------------|----| | |"
So if B is the correct form(where " marks the end of the wanted row and the beginning of the next) but i have the information separated into smaller rows like column A, how can i convert column A to look like B, can it be made with formula which converts all information into one row until it sees " and then goes on a new row and repeats the process until it reaches the bottom for the column? I tried functions like JOIN, CONCAT, TEXTJOIN but i can't make it work. I also checked for options in Notepad but couldn't find a solution. Also tried with Excel. I'm open to any ideas.
CodePudding user response:
Use string manipulation, like this:
=arrayformula(
transpose(
split(
join("", A2:A),
"""", false, true
)
)
& """"
)