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),””))
But if you’re not using Excel 2016 (and using prior versions such as Excel 2013/2010/2007/2003), you need to use a longer and more complicated formula (you can find that formula here:
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
Free Excel Course –
Paid Online Training –
Best Excel Books:
⚙️ Gear I Recommend:
Camera –
Screen Recorder – techsmith.z6rjha.net/26D9Q
USB Mic –
Wireless Mic:
Lighting –
Subscribe to get awesome Excel Tips every week:
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial
