1

Longtime learner, first time poster here!

I was wondering if anyone knew how to extract specific data points (phrases) from a Google Sheets cell and echo them in another cell.

enter image description here

For instance, in my example I have Vehicle year/make/model/trim etc. All in my B column. I would like to be able to extract for instance the trim into it's own column.
So for instance I would need the formula to search for XL, XLT, and STX and if one of those is found, then echo that exact phrase out in another cell.

I would and can post a few things I have tried, but I honestly haven't gotten very close yet. Mostly ended up echoing out the complete cell value or #NA.

Also please note that the format for the original cell might change so an ideal solution would not rely on the information being sandwiched between two "known variables" but instead simply search for specific variables and echo those when found.

Any help would be much appreciated!

0

The functions you're looking for are REGEXMATCH combined with OR and IF.
So IF will test three conditions (with OR) and then if OR returns a TRUE boolean, copy the cell's content, else do whatever.

=IF(or(REGEXMATCH(A1,"XL"),REGEXMATCH(A1,"XLT"),REGEXMATCH(A1,"STX")),A1,"no match")

Here's a way to do it with only one formula

ArrayFormula(IFS(ArrayFormula(REGEXMATCH(A1:A5,"XL")),A1:A5,ArrayFormula(REGEXMATCH(A1:A5,"XLT")),A1:A5,ArrayFormula(REGEXMATCH(A1:A5,"STX")),A1:A5))

EDIT :
Here's a way to return the tested key :

       =ArrayFormula(
IF(ArrayFormula(REGEXMATCH(A2:A6,"XL")),"XL ",)
&IF(ArrayFormula(REGEXMATCH(A2:A6,"XLT ")),"XLT",)
&IF(ArrayFormula(REGEXMATCH(A2:A6,"STX")),"STX",)
) 

There's a condition for each "key" then it retuns it in case of a match. I used & to concatenate the result if case of multiples keys inside the data.

Example : "Sample with XL and XLT" will return "XL XLT"

Edit 2 :

Same thing with Regextract to get rid of IF.

=ArrayFormula(ArrayFormula(iferror(REGEXEXTRACT(A2:A6,"XL")))&"-"&ArrayFormula(iferror(REGEXEXTRACT(A2:A6,"XLT")))&"-"&ArrayFormula(iferror(REGEXEXTRACT(A2:A6,"STX"))))
  • Hey Paul, Thanks so much for your answer. So I just tried this and it looks to say essentially if=TRUE, copy entire cell. Would it be possible to essentially say if=TRUE, copy the variable that made it true (in this case like "XL" or "STX" as opposed to the entire contents of the cell? Thanks so much! – Brian L Aug 13 at 17:24
  • Yes absolutely. I think there are multiples ways to do this, I'll try and edit this answer – pjmg Aug 13 at 18:30
  • @BrianL, I just edited the answer. This formula is ok when you have a small amount of variables/keys but can be boring to edit if you have a lot to test. – pjmg Aug 13 at 18:47
  • That worked, thanks so much for your help Paul! I really appreciate it!! – Brian L Aug 13 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.