Search code examples
regexif-statementgoogle-sheetsgoogle-sheets-formulatextjoin

Find a partial string in a cell and return exact value using a formula?

I need some help on this. I would like to find two different partial strings in a cell using a formula. For example - if cell (A1) contains "Staples A-12345 Georgia, USA" or other cell may contain only "g345" or "g100, g000" or other times it contains both A-12345 g345 in a cell as an example.

My goal is to return a value of "GXXX" or if not present, use the "A-XXXXX".

Search A1 cell for partial text of "A-" or "G". (The "A-, must contain 7 characters" and "G, must contain 4 characters.)

  • If true, C1 to return value of GXXX or if that is not present, use the other one.
  • If it contains multiple codes (GXXX, GXXX) then return both values separated with a comma
  • If it contains both "A-" and "G" in a cell, grab only the "G" code.
  • If cell blank, return blank value.
  • If cell value does not contain both "GXXX" or "A-XXXXX", copy and return the same value.

I am currently using this formula. I am unable to display the actual string.

=IFS(
ISNUMBER(SEARCH("*A-*",A1)),"TRUE",
ISNUMBER(SEARCH("*G*",A1)),"TRUE")

I got confused and stuck on this.Your time and help is greatly appreciated. Thank you.

Solution

  • use:

    =IFNA(IF(A1="",,
 IF(REGEXMATCH(A1, "(g\d{3}).*(g\d{3})|(g\d{3})"), TEXTJOIN(", ", 1, 
  REGEXEXTRACT(A1, "(g\d{3}).*(g\d{3})|(g\d{3})")), REGEXEXTRACT(A1, "A-\d{5}"))), A1)

    0