-1

I need to sperate strings in a Google spreadsheet. The extraction target is a string in [] brackets at the end of the cell. A string in [] brackets at the beginning or in the middle of the cell has to be ignored. Is there a possibility to target the cells from the right side?

The extraction with RegexExtract like

=IFERROR(REGEXEXTRACT(A1;"\[(.*?)\]");"")

works only for cells with one bracket and copies the content of the brackets, I want to split it off.

Otherwise for example

=SPLIT(CHANGE(A1;"[";"^");"^")

does not ignore the irrelvant brackets.

Example

Original Cell Content:

[irrelevant] Lore [irrelevant] Ipsum 123 [relevant String]

I want to have one cell with:

[irrelevant] Lore [irrelevant] Ipsum 123

And another cell with:

[relevant String]

Community
  • 1
  • 1
Gomorra
  • 11
  • 2

2 Answers2

0

You can try this for the second cell:

=IFERROR(REGEXEXTRACT(A1;"[(.*?)]$");"")

For the first cell:

=LEFT(A1, LEN(A1) - LEN(<second cell reference>))

Source: Google spreadsheet: split string and get last element

UPD:

Also there is a hack to get last occurence of something if you can get only the first occurence:

  1. Reverse the string
  2. Find the first occurence in the reversed string
CrafterKolyan
  • 1,042
  • 5
  • 13
0

Not tested, but also try

=regexextract(A1, "(.+?)(\[[^\[]+$)")
JPV
  • 26,499
  • 4
  • 33
  • 48