How to split a string to the left and right of a delimiter in Google Sheets

0
votes

I have text that I would like to split to the right and left using a delimiter. The text looks something like this:

This is text 10 : 12 More Text Some1 14 : 10 MoreText

Output should be:

This is text | 10 | 12 | More Text
Some1 | 14 | 1 | MoreText

I have this formula but it doesn't work as it's supposed to.

=ARRAYFORMULA(IF(C2:C="","",(SPLIT(REGEXREPLACE(C2:C,"\s*(\d+)\s*","|$1|"),"|"))))

Link to Spreedsheet - https://docs.google.com/spreadsheets/d/1EmL4kzCGxRbwvNJntwMokqgt8yjjAqnZuUidTbZe6Z8/edit?usp=sharing

For example, it outputs the result of the second row above as Some | 1 | 14 | 10 | MoreText when 1 is part of the Some1 text. I just want the formula to split the text 4 spaces from the colon.

Please help me fix this, will appreciate.

1
google-sheets
Are you looking to use ":" as delimiter? or space (" ")? Or commas? You seem to be wanting multiple split operations with different delimiters in one. – Aerials

1 Answers

1
votes

How about this modification?

In this modification, I modified the regex and replace value of REGEXREPLACE from your formula.

Modified formula:

=ARRAYFORMULA(IF(A1:A="","",(SPLIT(REGEXREPLACE(A1:A,"(\d+) : (\d+)","|$1|$2|"),"|"))))
  • In this case, it supposes that the initial value of your sample value is put to the cell "A1:A9".

Result:

enter image description here

  • The values of "A11:A12" are your sample input values in your question.

Note:

  • I thought that Myeam 4 5 Team D U18 in your sample might be Myeam  74 15  Team D U18.

Reference:

Added:

In order to remove the space of each cell, how about the following formulas?

Sample formula 1:

=ARRAYFORMULA(IF(A1:A="","",(TRIM(SPLIT(REGEXREPLACE(A1:A,"(\d+) : (\d+)","|$1|$2|"),"|")))))

Sample formula 2:

When I saw your sample input values, I confirmed that 00A0 is used as the space. So if your other sample values have it, please use the following sample formula.

=ARRAYFORMULA(IF(A1:A="","",(TRIM(SPLIT(SUBSTITUTE(REGEXREPLACE(A1:A,"(\d+) : (\d+)","|$1|$2|")," "," "),"|")))))

Reference: