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!

Improve this question

1 Answer 1

Reset to default
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"))))
Improve this answer
4
  • 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, 2019 at 17:24
  • Yes absolutely. I think there are multiples ways to do this, I'll try and edit this answer
    – pjmg
    Aug 13, 2019 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, 2019 at 18:47
  • That worked, thanks so much for your help Paul! I really appreciate it!!
    – Brian L
    Aug 13, 2019 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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