Search code examples
stringgoogle-sheetssubstringdelimiter

I need a single Regex pattern which can help me extract the specified substrings from the given strings when using REGEXEXTRACT in Google Sheets

I have a set of three strings as shown below:

GarnetUniversity_Zeus_Cronos_Greece_675|GoNow-New-Image-0S
SnoxUniversity_Dionysus_Zeus_Crete-50_450|GoNow-New-Image-DS
OwlUniversity_Athena_Zeus_Athens3476_675|GoNow-New-Image-0S

I was told to find a regex pattern that can extract the given substrings as follows:

Greece
Crete-50
Athens3476

I first tried using the pattern "(\w+-\w+)" and the results came out as follows:

#N/A
Dionysus_Zeus_Crete-50
#N/A

Then I tried using the pattern "(.*)" and this was the result:

Zeus_Cronos_Greece
Dionysus_Zeus_Crete-50
Athena_Zeus_Athens3476

I tried several more attempts but none have given me the exact value of the substrings that I am looking for. Can anyone give me a regex pattern that can give me the exact substrings that I had initially specified.

Solution

  • Here's another option:

    =REGEXEXTRACT(A1,"(?:(.*)_){4}")

    Or without REGEX:

    =INDEX(SPLIT(A1,"_"),,4)