Get started with Regex (in Excel)
Вставка
- Опубліковано 23 лип 2024
- 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
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.😀
I agree! It's much more powerful than you realize at first...
Good stuff! That took some of the mystery out of RegEx for me. Looking forward to Extract/Replace!
I've been wanting to learn REGEX for a long time. This was a GREAT start and looking forward to more!
Glad you liked it! I just recorded the second, and I'll probably do a couple more next week...
Wow! And the hits just keep on coming.
I found this very helpful. Thank you.
Thank you from Muscat
Paréntesis triangulares en HTML . "In my mind" This was great.
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!)
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.
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? 😂
Thanks, glad to hear it was helpful!
(And no, Americans have no idea that there's another way, don't dream of telling them! 😂)
Thanks Dim. I've used regex some, but always got fuzzy once capture groups come into play. Very helpful.!
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! : )
Thanks for the video!. How this is different from flash fill?.
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.
Ojala que no tienes que esperar demasiado! 🤞🏻
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)
If your date is entered in free text in an unknown language, it will be somewhere between challenging and impossible to parse it (challenging if you ‘just’ have to find the words for each month in dozens of languages, impossible if you have a date like 3/4/2024 which will mean different things depending on the culture of the person who entered it). I would say you’d be much better off trying to have tighter input control than deal with garbage data using regex!
If there are a finite number of possible patterns *AND* they are mutually exclusive (unlike, say, dd/mm/yyyy and mm/dd/yyyy) then you could write a regex to match and extract each one, but it could be very messy if the number of possible patterns is large.
Awesome summary. What are some past eSports cases where regex would be useful? Maybe poker or tic tac toe.
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! : )
@@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. 😅
@@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 😬
"obviously because computer programmers are not human beings" 😃😃😃 Great video, thanks!
Made me literally LOL. 😂
\w also matches _ (underscore). Nice video! Waiting for lookahead :)!
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...
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).
Maybe he meant symbols?
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...).
You need to turn the sound volume up for people who can't hear well.
Finally...
I have wanted regex in standard Excel literally for decades.
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!).