CODEWITHSUNDEEP
regexgoogle-sheetssplit
ASE
ASE

Reputation: 145

How can I extract specific patterns from a string?

I currently have a dataset filled with the following pattern:

enter image description here

My goal is to get each value into a different cell.

enter image description here

I have tried with the following formula, but it's not yielded the results I am looking for.

=SPLIT(D8,"[Stock]",FALSE,FALSE)

I would appreciate any guidance on how I can get to the ideal output, using Google Sheets.

Thank you in advance!

Upvotes: 0

Views: 68

Answers (3)

player0
player0

Reputation: 1

use:

=INDEX(TRIM(IFNA(SPLIT(D8:D; ","))))

Upvotes: 0

Erik Tyler
Erik Tyler

Reputation: 9355

I will assume here from your post that your original data runs D8:D.

If you want to retain [Stock] in each entry, try the following in the Row-8 cell of a column that is otherwise empty from Row 8 downward:

=ArrayFormula(IF(D8:D="",,TRIM(SPLIT(REGEXREPLACE(D8:D&"~","(\[Stock\]).","$1~"),"~",1,1))))

If you don't want to retain [Stock] in each entry, use this version:

=ArrayFormula(IF(D8:D="",,TRIM(SPLIT(REGEXREPLACE(D8:D&"~","\[Stock\].","~"),"~",1,1))))

These formulas don't function based on using any punctuation at all as markers. They also assure that you don't wind up with blank (and therefore unusable) cells interspersed for ending SPLITs.

Upvotes: 1

idfurw
idfurw

Reputation: 5852

  1. , only used in the separator
=ARRAYFORMULA(SPLIT(D8:D,", ",FALSE))
  1. , used also in each string ([stock] will be replaced)
=ARRAYFORMULA(SPLIT(D8:D," [Stock], ",FALSE))
  1. , used also in each string ([stock] will not be replaced)
=ArrayFormula(SPLIT(REGEXREPLACE(M9:M11,"(\[Stock\]), ","$1♦"),"♦"))

Upvotes: 1

Related Questions