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: 8 Responses to “Learning To Use Regular Expressions In Numbers”

    Gene
    3 weeks 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 weeks 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 weeks 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 weeks 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 weeks 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 weeks 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 weeks ago

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

    Philip M
    3 weeks 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.

Leave a New Comment Related to "Learning To Use Regular Expressions In Numbers"

:
:
:
0/500 (500 character limit -- please state your comment succinctly and do not try to get around this limit by posting two comments)