Microsoft Excel Tutorial: Extract Everything After First Digit
**How to Extract Everything in a Cell Starting from the First Digit in Excel!**
In today's video, we tackle a great question from Alex: *How do we extract everything in a cell starting from the first digit?* Alex has a large dataset, and in one of the columns, he needs to separate "what has to be done" from the address where it needs to be done. The tricky part? There's no consistent delimiter to separate these two pieces of information. Today, I'll show you two different solutions to solve this problem—one for those who have access to the latest Excel features and one for those who don't.
To download the workbook from today: Excel Extract Everything After First Digit - Episode 2650 Sample Files - MrExcel Publishing
**Solution #1: REGEXEXTRACT for Microsoft 365 Insiders**
If you're part of the Microsoft 365 Insiders Beta channel, you're in luck! The REGEXEXTRACT function can make this task a breeze. If you're unsure if you're on the Beta channel, head to File, Account, and check under About Excel. If you're not on the Beta channel, you can Google "Join Microsoft 365 Insider Program" and make sure to follow the steps for the Beta, not the Current Channel. Once you have access, you can use REGEXEXTRACT to pull everything after the first digit to the end of the string. Even if you're not familiar with Regex (I’m not!), you can use tools like Copilot to get the pattern you need. With this powerful function, you can get the job done quickly and efficiently.
**Solution #2: A Complicated Formula for Non-Insiders**
Now, if you're not on the Beta channel and can’t use REGEXEXTRACT, I've got a workaround for you, but I'll be honest—it's a bit messy. It involves using the SEQUENCE function to create an array, then running a FIND function for each digit in that array within your target cell. After locating the position of the first digit, you'll use the MID function to extract everything from that point to the end of the string. It's a lengthy process and not the most elegant, but it gets the job done. While this method isn't ideal, it's a viable option for those restricted by their IT department or unable to access the Beta features.
**Crowdsourcing for Better Solutions**
I’m sure there are more efficient solutions out there! If you have a cleaner, more elegant formula, especially one using LAMBDA functions or something with recursion, please share it in the comments below. This community is fantastic at coming up with creative Excel solutions, and I'm confident someone will have a better approach than my second method.
**Thanks for Watching!**
A big thank you to Alex for sending in this challenging question, and thank you to everyone for stopping by. If you enjoyed this video and want to learn more Excel tips and tricks, make sure to hit that like button, subscribe, and click the bell icon for notifications. We’ve got a lot more Excel content coming your way, so stay tuned!
Make sure to check out the comments section for alternative solutions and feel free to share your own ideas! We'll see you next time for another net cast from MrExcel!
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Spreadsheet Merch: Run on Coffee and Excel Formulas Coffee Mug
Table of Contents
(0:00) Problem Statement: Split a cell at the first digit
(0:35) How to join Beta channel
(0:52) Solving with REGEXEXTRACT
(1:09) Using Copilot for Regex pattern
(1:25) Complicated solution if you don't have REGEXEXTRACT
(1:40) Using FIND with SEQUENCE
(2:00) MID of MIN
(2:20) More elegant to use LAMBDA with recursion
(2:35) Excel Merch
#excelhacks #microsoft365 #excelchallenge #excelformula #excelformulasandfunctions #copilot #regex #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these questions:
How to extract text after the first digit in Excel
Excel REGEXEXTRACT function tutorial
Extract everything after the first number in Excel
Advanced Excel text extraction techniques
Excel formula to find first digit in a string
Using SEQUENCE and FIND functions in Excel
Microsoft 365 Insider Beta features for Excel
How to use Regex in Excel for text manipulation
Excel formula to split text and numbers
Tips for extracting data in Excel without delimiters
**How to Extract Everything in a Cell Starting from the First Digit in Excel!**
In today's video, we tackle a great question from Alex: *How do we extract everything in a cell starting from the first digit?* Alex has a large dataset, and in one of the columns, he needs to separate "what has to be done" from the address where it needs to be done. The tricky part? There's no consistent delimiter to separate these two pieces of information. Today, I'll show you two different solutions to solve this problem—one for those who have access to the latest Excel features and one for those who don't.
To download the workbook from today: Excel Extract Everything After First Digit - Episode 2650 Sample Files - MrExcel Publishing
**Solution #1: REGEXEXTRACT for Microsoft 365 Insiders**
If you're part of the Microsoft 365 Insiders Beta channel, you're in luck! The REGEXEXTRACT function can make this task a breeze. If you're unsure if you're on the Beta channel, head to File, Account, and check under About Excel. If you're not on the Beta channel, you can Google "Join Microsoft 365 Insider Program" and make sure to follow the steps for the Beta, not the Current Channel. Once you have access, you can use REGEXEXTRACT to pull everything after the first digit to the end of the string. Even if you're not familiar with Regex (I’m not!), you can use tools like Copilot to get the pattern you need. With this powerful function, you can get the job done quickly and efficiently.
**Solution #2: A Complicated Formula for Non-Insiders**
Now, if you're not on the Beta channel and can’t use REGEXEXTRACT, I've got a workaround for you, but I'll be honest—it's a bit messy. It involves using the SEQUENCE function to create an array, then running a FIND function for each digit in that array within your target cell. After locating the position of the first digit, you'll use the MID function to extract everything from that point to the end of the string. It's a lengthy process and not the most elegant, but it gets the job done. While this method isn't ideal, it's a viable option for those restricted by their IT department or unable to access the Beta features.
**Crowdsourcing for Better Solutions**
I’m sure there are more efficient solutions out there! If you have a cleaner, more elegant formula, especially one using LAMBDA functions or something with recursion, please share it in the comments below. This community is fantastic at coming up with creative Excel solutions, and I'm confident someone will have a better approach than my second method.
**Thanks for Watching!**
A big thank you to Alex for sending in this challenging question, and thank you to everyone for stopping by. If you enjoyed this video and want to learn more Excel tips and tricks, make sure to hit that like button, subscribe, and click the bell icon for notifications. We’ve got a lot more Excel content coming your way, so stay tuned!
Make sure to check out the comments section for alternative solutions and feel free to share your own ideas! We'll see you next time for another net cast from MrExcel!
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Spreadsheet Merch: Run on Coffee and Excel Formulas Coffee Mug
Table of Contents
(0:00) Problem Statement: Split a cell at the first digit
(0:35) How to join Beta channel
(0:52) Solving with REGEXEXTRACT
(1:09) Using Copilot for Regex pattern
(1:25) Complicated solution if you don't have REGEXEXTRACT
(1:40) Using FIND with SEQUENCE
(2:00) MID of MIN
(2:20) More elegant to use LAMBDA with recursion
(2:35) Excel Merch
#excelhacks #microsoft365 #excelchallenge #excelformula #excelformulasandfunctions #copilot #regex #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these questions:
How to extract text after the first digit in Excel
Excel REGEXEXTRACT function tutorial
Extract everything after the first number in Excel
Advanced Excel text extraction techniques
Excel formula to find first digit in a string
Using SEQUENCE and FIND functions in Excel
Microsoft 365 Insider Beta features for Excel
How to use Regex in Excel for text manipulation
Excel formula to split text and numbers
Tips for extracting data in Excel without delimiters
Transcript of the video:
Just down below the video.
If you click Like, that'll make sure that YouTube shows this video to more people.
Thanks. Great question from Alex.
How do we extract everything in the cell starting from the first digit?
Today's question from Alex. He has a large data set.
Here in this column. He has what has to be done and then the address where it has to be done. The problem is he doesn't know how many words are going to be in the “what has to be done” and there's no delimiter between the address and “what has to be done”. Alright, so now there's two solutions that I have to this. I'm sure people watching have something better than the second solution for sure. If you are on Office Insiders at the Beta level.
To find that, you go to File, Account. And right here, you see under About Excel that you're in Beta channel. If you're not just Google, “Join Microsoft 365 Insider Program”. Make sure to follow the steps for Beta and not Current Channel. Then you'll have access to this amazing function, REGEXEXTRACT. So from DD, I want everything after that first digit to the end of the string. And that gets us exactly what we need.
It's an amazing new function. If you don't know Regex - neither do I!
Just go to copilot.microsoft.com. Ask for the “RegeX pattern to extract from the first digit to the end of the string”. And they give you the backslash d, which matches any digit. Period, Asterisk matches any character.
It works perfectly. All right, so if you're on Microsoft 365 Insiders, we're done. If not, oh God, my solution is too freaking complicated. I'm sure someone watching this has something better. So check the YouTube comments below. I started off with the SEQUENCE function, one row, 10 columns, starting from zero. That gives me all of the digits here in a single array. And then for each of those items in the array, I want to do a FIND in D3. So I want to look for a 1.
I want to look for a two… That tells me where it's found.
Or I get a #VALUE! if there is no 2 found at all. And then I want the MINimum of all of those numbers. I have to replace the IFERROR with a really large number in this case, 999999. And once I know where that first digit is, then it's the MID of D5 starting at that first digit. Really ugly.
And especially once you know the REGEXEXTRACT, it's definitely better to have that.
But you might be in a company and your IT department won't let you install the Beta yet.
So this would be one way around it. Check the YouTube comments below.
I'm sure there's a great LAMBDA function, something with recursion, something that would be a little bit more elegant than what I have here. Well, hey, I want to thank Alex for sending that question in and I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
Hey, just below the video, click the Store icon. We have a whole bunch of new mugs and shirts professionally designed. All kinds of great fun slogans for you or your favorite Exceller. If you like these videos, please down below, Like, Subscribe and Ring the Bell. Feel free to post any questions or comments down in the comments below.
If you click Like, that'll make sure that YouTube shows this video to more people.
Thanks. Great question from Alex.
How do we extract everything in the cell starting from the first digit?
Today's question from Alex. He has a large data set.
Here in this column. He has what has to be done and then the address where it has to be done. The problem is he doesn't know how many words are going to be in the “what has to be done” and there's no delimiter between the address and “what has to be done”. Alright, so now there's two solutions that I have to this. I'm sure people watching have something better than the second solution for sure. If you are on Office Insiders at the Beta level.
To find that, you go to File, Account. And right here, you see under About Excel that you're in Beta channel. If you're not just Google, “Join Microsoft 365 Insider Program”. Make sure to follow the steps for Beta and not Current Channel. Then you'll have access to this amazing function, REGEXEXTRACT. So from DD, I want everything after that first digit to the end of the string. And that gets us exactly what we need.
It's an amazing new function. If you don't know Regex - neither do I!
Just go to copilot.microsoft.com. Ask for the “RegeX pattern to extract from the first digit to the end of the string”. And they give you the backslash d, which matches any digit. Period, Asterisk matches any character.
It works perfectly. All right, so if you're on Microsoft 365 Insiders, we're done. If not, oh God, my solution is too freaking complicated. I'm sure someone watching this has something better. So check the YouTube comments below. I started off with the SEQUENCE function, one row, 10 columns, starting from zero. That gives me all of the digits here in a single array. And then for each of those items in the array, I want to do a FIND in D3. So I want to look for a 1.
I want to look for a two… That tells me where it's found.
Or I get a #VALUE! if there is no 2 found at all. And then I want the MINimum of all of those numbers. I have to replace the IFERROR with a really large number in this case, 999999. And once I know where that first digit is, then it's the MID of D5 starting at that first digit. Really ugly.
And especially once you know the REGEXEXTRACT, it's definitely better to have that.
But you might be in a company and your IT department won't let you install the Beta yet.
So this would be one way around it. Check the YouTube comments below.
I'm sure there's a great LAMBDA function, something with recursion, something that would be a little bit more elegant than what I have here. Well, hey, I want to thank Alex for sending that question in and I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
Hey, just below the video, click the Store icon. We have a whole bunch of new mugs and shirts professionally designed. All kinds of great fun slogans for you or your favorite Exceller. If you like these videos, please down below, Like, Subscribe and Ring the Bell. Feel free to post any questions or comments down in the comments below.
