Search code examples
regexgoogle-sheetscombinations

Extract two words regardless of order In Google Sheets

I have a Google Sheets table with input in column A, and I'd want to achieve this result using REGEXEXTRACT.

Desired result:

Input Output
Stock OutNew21554 - Shirt - Red New | Stock Out
NewStock Out54872 - Shirt - Green New | Stock Out

This is what I attempted.

01

=ArrayFormula(REGEXEXTRACT(A1:A2, "[(Stock Out)|(New)]+"))
Input Output
Stock OutNew21554 - Shirt - Red Stock OutNew
NewStock Out54872 - Shirt - Green NewStock Out

02

=ArrayFormula(REGEXEXTRACT(A2:A3, "(Stock Out)|(New)+"))
Input Output
Stock OutNew21554 - Shirt - Red Stock Out
NewStock Out54872 - Shirt - Green
Solution

  • Use two instances of regexextract() in an { array expression }, wrapped in iferror():

    =arrayformula( iferror( 
  { 
    regexextract(A2:A3, "New"), 
    regexextract(A2:A3, "Stock Out") 
  } 
) )