google-sheetsre2

RE2 expression to extract string up to the sequence " :" if present

I'm using the google sheets function REGEXEXTRACT() to search a string and return that string back with everything from " :" and after removed, if that string is present.

Example inputs:

Sydney Distr Center
Port Macquarie
Port Macquarie : Port Macquarie Display
Taree
Taree : Taree Display

Desired output:

Sydney Distr Center
Port Macquarie
Port Macquarie
Taree
Taree

The closest I got so far was using this expression:

^[^\s:]+

However, many of the inputs contain spaces that I want to keep, and this ended up returning:

Sydney
Port
Port
Taree
Taree

I need it to return all the text before it encounters \s: in sequence, and also if it doesn't. Any suggestions?

Solution

  • You can try one of following formulas:

    REGEXEXTRACT

    =REGEXEXTRACT(A1&" :","(.+?) :")

    REGEXREPLACE

    =REGEXREPLACE(A1,"(.+) :.+","$1")

    Or without regex:

    =INDEX(SPLIT(A1," :",),1)