Get started with Regex (in Excel)

Рет қаралды 6,504

Күн бұрын

There are three new functions coming to Excel for regular expressions (regex). Regex has been available in Excel before through VBA or Python, but this is the first time it's accessible as a native grid function.
So I thought it would be a good time to go over some basics of what a regular expression is and how to use them. This video is mostly just the first part - what kind of patterns are possible. I'll add another one shortly to show how you can use them for extracting, filtering, replacing, and rearranging.
You can get the file I used in the video here:
1drv.ms/x/s!Aryypvyx4xIO9WkCz...
And I'll be adding a few more videos to this playlist over the next few days:
• Regex in Excel
Useful links:
Regex101: regex101.com/
Regex syntax 'cheat sheet': developer.mozilla.org/en-US/d...
Function announcement: insider.microsoft365.com/en-u... (note there are links to pages with additional detail on each function)
Sections
00:00 Introduction & resources
03:53 Basic patterns
08:45 Repetition and wildcards
18:52 Capture groups

Пікірлер: 36

@DingusBatus Ай бұрын

The more I see about REGEX the more interested I get in it. Very interesting and very powerful looking.😀 A very interesting breakdown on how it all works.😀

@DimEarly Ай бұрын

I agree! It's much more powerful than you realize at first...

@excelrobot Ай бұрын

Good stuff! That took some of the mystery out of RegEx for me. Looking forward to Extract/Replace!

@nemoyatpeace Ай бұрын

I've been wanting to learn REGEX for a long time. This was a GREAT start and looking forward to more!

@DimEarly Ай бұрын

Glad you liked it! I just recorded the second, and I'll probably do a couple more next week...

@w2tty Ай бұрын

I found this very helpful. Thank you.

@Michael_Alaska Ай бұрын

Wow! And the hits just keep on coming.

@ExcelHechoFacil Ай бұрын

Paréntesis triangulares en HTML . "In my mind" This was great.

@DimEarly Ай бұрын

Soy muy malo editando videos, ¡así que siempre obtendrás mi auténtico flujo de conciencia! 😂 (Por lo general, intento escribir mis respuestas yo mismo para practicar mi español, ¡pero esta requirió el Traductor de Google!) _____________________________________________________ I'm very bad at editing videos, so you always get my authentic stream of consciousness! 😂 (I usually try to write my answers myself to practice my Spanish, but this one required Google Translate!)

@littlecandyschannel 21 сағат бұрын

Great video! thanks so much for the explanation... May I ask how to extract the date using REGEX? The date in a string can be in different pattern and languages, for example 4-19-2024, 4/19/2024, 4.19.2024, 19.4.2024, 4月19曰2024年, Apr 19, 2024, 19th April 2024.... (Note: 月 means month, 日 means Day, and 年 means year)

@iandobson8846 Ай бұрын

Thanks for this. I've used REGEX in Google Sheets before but this is the first time I've had someone explaining what it all means. Very helpful.

@iandobson8846 Ай бұрын

And don't worry about period and Zee, we've all been exposed to Americanisms for long enough now. Do they know it the other way around though? 😂

@DimEarly Ай бұрын

Thanks, glad to hear it was helpful! (And no, Americans have no idea that there's another way, don't dream of telling them! 😂)

@salahaldeen7924 Ай бұрын

Thank you from Muscat

@GregHingsbergen Ай бұрын

Thanks Dim. I've used regex some, but always got fuzzy once capture groups come into play. Very helpful.!

@DimEarly Ай бұрын

I'm still wrapping my head around the differences between (i) a pattern with X before Y before Z, (ii) the same pattern, but with Y captured, and (iii) the same pattern, but with X as a look-back and Z as a look-ahead. It's quite subtle, and depends on whether you're just matching a pattern, extracting, or replacing. I'll dive into that some more next week! : )

@ExcelHechoFacil Ай бұрын

Muchas gracias Diarmuid por hacernos llegar la información. Estaba esperando este video. Mi Office 365 Excel aun no carga las nuevas funciones. Seguiré esperando.

@DimEarly Ай бұрын

Ojala que no tienes que esperar demasiado! 🤞🏻

@daXcel7448 Ай бұрын

Thanks for the video!. How this is different from flash fill?.

@AdiCristea Ай бұрын

"obviously because computer programmers are not human beings" 😃😃😃 Great video, thanks!

@iandobson8846 Ай бұрын

Made me literally LOL. 😂

@vernonmonter6743 28 күн бұрын

You need to turn the sound volume up for people who can't hear well.

@dimitrijesandic7823 Ай бұрын

\w also matches _ (underscore). Nice video! Waiting for lookahead :)!

@DimEarly Ай бұрын

Thanks for keeping me honest! : ) I'm just uploading the next video now, but had to push look-ahead / look-back back another day because there was too much to say...

@LiannaGerrish Ай бұрын

Awesome summary. What are some past eSports cases where regex would be useful? Maybe poker or tic tac toe.

@DimEarly Ай бұрын

I can't think of a lot where it would give a real edge (although I also haven't looked at lots of them since thinking about this, so maybe...). I think because the cases are designed by Excel users who usually have an Excel solution in mind, there's not much that goes outside what you might do with Excel text functions. The one I can think of where it would be a big help is for a couple of cases (Peter's laundry one, and the voting one from the '22 semi-finals) that had lists of items of different lengths that weren't delimited. So you might need to interpret ABCDEF as (A)(BC)(D)(EF). That's quite hard to do in 'old' Excel (I think the best strategy was to find and replace all the longer codes with single characters), but easy with Regex: =REGEXEXTRACT("ABCDEF","(A|BC|D|EF)",1) It even works if the patterns overlap (e.g. I think there was a B and a YB in the voting one), as long as you list the patterns from longest to shortest in the regex code. It's like magic! : )

@LiannaGerrish Ай бұрын

@@DimEarly Cool thanks I'll check out the voting one. It's a good alternative to mass replacing actual case data, that always makes me nervous. 😅

@DimEarly Ай бұрын

@@LiannaGerrish Yes! I remember having to undo a whole section of workings once to get the raw data back because I hadn't kept a copy 😬

@KO1967 Ай бұрын

Thanks. Maybe someone can help me out here. Your example was password must contain 1 number (0-9) ... 1 uppercase letters ... 1 lowercase letters ... 1 non-alpha numeric number. What the hell is a non-alpha numeric number? Aren't all numbers numeric and how is this different that the first part (1 number).

@VictorAdarve Ай бұрын

Maybe he meant symbols?

@DimEarly Ай бұрын

I assume it meant 'non-alpha-numeric' (i.e. neither a letter nor number, a.k.a. special characters). That's also what the last positive lookahead condition tests for: (?=.*[^\w\d\s:]) matches any character except a letter, number, or space (or underscore or colon, for some reason...).

@DDD-fw6nt Ай бұрын

Finally...

@ziggle314 Ай бұрын

I have wanted regex in standard Excel literally for decades.

@DimEarly Ай бұрын

I never used it much before, but when I started exploring Python last year it really made me want it (that, and list comprehension... maybe it's on the roadmap!).