Excel Extract Everything After First Digit - Episode 2650

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.

**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!

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

[DimEarly]

Here's another option - also requires 365 (but not beta channel), and not as elegant as regex:
=TEXTAFTER(A1, TEXTBEFORE(A1, SEQUENCE(10, , 0)))
SEQUENCE gives all the digits (sequence of 10 starting from 0), TEXTBEFORE pulls out everything before that, and TEXTAFTER pulls out everything after that, which is what you're looking for.

(It's much messier to get everything after *but including* the first digit with these functions... after but excluding wouldn't take two calls, but it's not what you need today...)

[JoseAntonioMorato]

In the event that you want to extract only the digits, I couldn't find any regex pattern that would do this, so I created the following formula: =MAP(D2:D20, LAMBDA(x, CONCAT(TEXTSPLIT(x, TEXTSPLIT(x, SEQUENCE(10, , 0), , 1), , 1)))) 🤗

or if you prefer the initial digits separated from the final digits: 🤗

[ajbdbdude]

My first attempt.

=IFERROR(MID(A2, MATCH(1, --ISNUMBER(VALUE(MID(A2, SEQUENCE(1, LEN(A2)), 1))), 0), LEN(A2)), "")

Still convoluted, but different.

[GeertDelmulle]

REGEX rules here, for sure!
NB: in Power Query there is a UI functionality that allows you to split on transition from alphanumerical to number, so that could be handy, too (all versions of Excel).

[aremoteforu]

What about doing it once and using ctrl E?

[christopherhazel]

I'm thinking something like Mid(string, Sequence(1, len(string), 1)=value(of same) to find numbers and then go from there.

[AlThePal]

Did you try doing a python function that could start slicing before the first number since they all start with a number? Or even if not python a Regular expression REGEX ?

[billhladik]

Another hack relying on bitand() telling us if its argument is a number or not.

=MID(A1, MATCH(0, BITAND(MID(A1, SEQUENCE(99), 1), 0), 0), 99)

If you bitand() each character with 0 you get a 0 if that character was a digit. Fining the first 0 tells you where to split the string.

[erikguzik]

ok, still not great but it works to find the location of the first number and return the rest of the string. Again sure there is a better way, but pulled out the formula used a while back to get the number out of a string, modified it to get the rest of the string.

=MID(A2, FIND(TOROW(VALUE(FILTERXML("<ALL><NUM>"&SUBSTITUTE(A2, " ", "</NUM><NUM>")&"</NUM></ALL>", "//NUM")), 2), A2), 9999)