Regex Pattern for Inconsistent Data in Google Sheets

Other Questions Google Sheets
1

I need assistance with creating a regex pattern for Google Sheets that can handle varying string formats. For instance, given the following input string within a cell:

30m DEVO-67, DEVO-68
1h DEVO-69, DEVO-70
2h DEVO-75

The desired result should be:

DEVO-67, DEVO-68, DEVO-69, DEVO-70, DEVO-75

The goal is to extract all occurrences of ‘DEVO’ followed by a dash and a number from any provided text. I attempted this regex but it was unsuccessful:

=REGEXREPLACE(B1, "[0-9]+\S+\s+DEVO-(\d+).*", "DEVO - $1")
2

You could also use =ARRAYFORMULA(TEXTJOIN(", ", TRUE, IF(ISNUMBER(SEARCH("DEVO-", A:A)), REGEXEXTRACT(A:A, "DEVO-[0-9]+"), ""))) to automatically scan and compile the results into one cell. this leaves out blanks and works well for larger datasets! good luck!

3

hey ExcitedGamer85, try using =REGEXEXTRACT(A1,"DEVO-[0-9]+") wrapped in TEXTJOIN(", ", TRUE, SPLIT(...)). This way you’ll extract all DEVO instances and join them into a single cell without duplicates. hope it helps!