Home > database >  Google sheets - Combine rows from one column, until a specific character is detected
Google sheets - Combine rows from one column, until a specific character is detected

Time:12-22

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 
    ) 
  ) 
  & """"  
)
  • Related