Extract Numbers from a String in Excel | Using Formula and VBA

Want to extract the numeric part or the text part from a string in Excel? In this video, I will show you how to extract numbers from a text string in Excel (and vice-versa, extract text from a string in Excel).

The video covers the following topics:
-- Extract Numbers/Text from String in Excel (using TEXTJOIN)
-- Extract Numbers from String in Excel (using VBA)
-- Extract Text from a String in Excel (using VBA)

You can use any of the methods covered to separate text and numbers in Excel.

If you're using Excel 2016, you can use the new TEXTJOIN formula which makes it a lot easier to extract numbers from a string. The below formula extracts the numbers from a string in a cell in Excel:

=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))

And in case you are fine using VBA, you can easily create a custom User Defined Formula that takes the cell reference as the input and extracts the number part from a string. It's easy and neat with VBA.

Personally, I prefer using the VBA method to separate Numbers and Text in Excel as it only requires the work once and can be used over and over again.

Get numeric part in excel

⚙️ Gear I Recommend:

Note: Some of these links here are affiliate links!

#Excel #ExcelTips #ExcelTutorial

[uncleyen]

dame, these formula just so smart and awesome. and super useful. you are rock Thanks so much

[joemomma]

Thank you so much i keep watching your videos all the time.

[Stargeon]

I am trying to synthesize daily discharge data from a river for the last 10 years and the raw data cell values all have letters at the THIS SAVED ME. THANK YOU.

[Jim]

Thank you - this is the ONLY formula that worked after hours of searching. Note to other users, CTRL-SHIFT-ENTER just adds =Arrayformula( to the formula which is ESSENTIAL for it to work. I was looking a set of prices big and small which are prefixed with odd symbols like ^~ instead of $, £ etc. Anyway it worked - phew. The old huge LEN formula worked on another set of data but not this one even though identical?! Happy to send you $5 if you get in touch.

[corduroy]

Guy, you are a GENIUS! Thank you so much!

[Ashrafhaa]

This Video is very helpful for both VBA and Even the Excel Functions as well, Thanks for the Creator

[jaredfromtexas]

Good video. But this seems like a task for which Excel/Microsoft should create a specialized function. A user shouldn't have to create something so complex in order to achieve something fairly simple.

[CarlosLopez-xirq]

Thanks so much Bro! I used your VBA code in access to extract numbers with dots, adapted it very sligtly and it works soooo perfectly! You're a crack!

[nadermounir]

Great tutorial especially the VBA one. Amazing 👏

[anilkumar-pghi]

Hello Sir,
Thank you very much,
If cell has more then 25 character, which code need to use?

[LOVStudios]

I'm subscribing off of this single experience! You are the shit. Let me see what else you have.

[TheVaibhav]

great trick special VBA part, thanks!!

[sharifulhridoy]

you are Awesome and thanks for free teaching

[surajbhardwaj]

Awesome bro... But to reach here need more practise for me...

[khalidstark]

Thanks !!! this video helped me a lot and saved my time

[andrewwest]

Amazing, thanks for this, worked a treat

[khanyisolesedi]

This is great straight to the point, are you able do same thing on SQLite?

[Rkeev]

Great video I wish you would do one that sums all numbers in a cell even if the cell contains text values along with numbers. Another way is to use what you’ve shown is use one of your formulas then use substitute function in the next cell over and reference your cell with the formula and it would return the cell value not included in your formula result.

[Dev_Bartwal]

Very osm Bansal sir
Keep sharing yur Knowledge with us.
Thanks a ton

[adamtson]

1:22 i think it is not for number of rows but for the number of characters in the cell.