Learning To Use Regular Expressions In Numbers

The latest version of Numbers allows you to use regular expressions, also called regex, in formulas. Regex is very powerful if you can master it. You can use them to match patterns in text to perform otherwise difficult or impossible things in your spreadsheets.

Here is a ZIP file containing the Numbers document with all of the examples: 2224NumbersRegexExamples.zip.

Comments: 9 Responses to “Learning To Use Regular Expressions In Numbers”

    3 years ago

    Any idea as to why Apple didn't put it in Numbers Help. I thought for sure that they would include it in Help. I searched for regex. But I find the help in most Apple apps to be wanting. Thanks for your video.

    3 years ago

    Gene: It is in the help. It is formula-related, so you'll find it there in the right sidebar when you are typing formulas. Also when you look in the Functions list. Like here: https://support.apple.com/guide/functions/regex-ffac65c37db4/web

    Rudy Rugebregt
    3 years ago

    Thanks for posting the zip file with the samples. Not being a programmer, I didn't have the skills to do this kind of file manipulation. Being able to see the 'source code' helps me spot typos vs. logic errors. This is very helpful and is just what I need to get started.

    I would also be interested in using this capability to search/change text files. I have many years of plain text files and I would like to be able to search and find specific words or strings of text.

    3 years ago

    Rudy: You can use regular expressions in the Terminal on text files, using a tool called grep. But if you are not a programmer, it is tough to use. At least one text editor app (BBEdit) uses regex in searches though.

    Tim Aaronson
    3 years ago

    A finer point with your "Count duplicate words" example: \w is equivalent to [a-zA-Z_0-9]
    i.e. it DOES include numbers and the underscore character.
    This is an 'artifact' of what was allowed for unix file names.

    Philip M
    3 years ago

    There seem to be issues with the REGEX.EXTRACT capture groups argument. For a start, this is in the doc:

    If 0 is returned, an array of all capture groups will be returned.

    I think they mean "if 0 is given" but I am stumped by exactly what the difference is between indexing using this argument or with INDEX. Less typing?

    But more importantly, I can't get 0 to work? No matter what I do the return value from specifying 0 for capture groups is always 0 and not an array. Any ideas?

    3 years ago

    Philip: Not sure. You'll just have to keep experimenting.

    Philip M
    3 years ago

    I just discovered an undocumented goodie that could substantially improve functionality and performance with the new REGEX features. REGEX.EXTRACT will take REGEX as an argument type for its second argument in addition to a sting argument—i.e. REGEX_EXTRACT("abccdef",REGEX("^(.).(C+)...$)",1,2) works and returns "CC" as expected. This allows the REGEX to be stored in a cell permitting repeated extracts without the overhead of parsing and compiling the same regular expression.

    Cary Gavant
    2 years ago

    Thanks for you comments on REGEX.EXTRACT groups and your discovery of the undocumented secret. I could not for the life of me extract a string between two groups. I knew the syntax was correct by using regex testers. After I looked at your last comment, I simply took my REGEX.EXTRACT string and enclosed it inside REGEX. I wish there was more room to show the details.But if REGEX.EXTRACT(Source,Regular expression) does not work, try REGEX.EXTRACT(Source,REGEX(Regular expression)).

Comments Closed.