![\"Extract](\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2017\/08\/Extract-Number-from-String-in-Excel-Data.png\")
<\/figure><\/div>\n\n\n\nThe method you choose will also depend on the version of Excel you\u2019re using:<\/p>\n\n\n\n
- For versions prior to Excel 2016, you need to use slightly longer formulas<\/li>
- For Excel 2016, you can use the newly introduced TEXTJOIN function<\/li>
- VBA method can be used in all the versions of Excel<\/li><\/ul>\n\n\n\n
Extract Numbers from String in Excel (Formula for Excel 2016)<\/h2>\n\n\n\n
This formula will work only in Excel 2016 as it uses the newly introduced TEXTJOIN function.<\/p>\n\n\n\n
Also, this formula can extract the numbers that are at the beginning, end or middle of the text string.Note that the TEXTJOIN formula covered in this section would give you all the numeric characters together. For example, if the text is \u201cThe price of 10 tickets is USD 200\u201d, it will give you 10200 as the result.<\/p>\n\n\n\n
Suppose you have the dataset as shown below and you want to extract the numbers from the strings in each cell:<\/p>\n\n\n\n
Below is the formula that will give you numeric part from a string in Excel.<\/p>\n\n\n\n
![\"\"](\"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2021\/04\/WeChat-Image_20210412111831.png\")
<\/figure>\n\n\n\n=TEXTJOIN(\"\",TRUE,IFERROR((MID(A2,ROW(INDIRECT(\"1:\"&LEN(A2))),1)*1),\"\"))<\/pre>\n\n\n\n
![\"Formula](\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2017\/08\/Formula-to-get-all-number-from-a-string.png\")
<\/figure><\/div>\n\n\n\nThis is an array formula, so you need to use \u2018Control + Shift + Enter<\/strong>\u2018 instead of using Enter.<\/p>\n\n\n\n
In case there are no numbers in the text string, this formula would return a blank (empty string).<\/p>\n\n\n\n
How does this formula work?<\/strong><\/p>\n\n\n\n
Let me break this formula and try and explain how it works:<\/p>\n\n\n\n
- ROW(INDIRECT(\u201c1:\u201d&LEN(A2))) \u2013 this part of the formula would give a series of numbers starting from one. The LEN function in the formula returns the total number of characters in the string. In the case of \u201cThe cost is USD 100\u201d, it will return 19. The formulas would thus become ROW(INDIRECT(\u201c1:19\u201d). The ROW function will then return a series of numbers \u2013 {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}<\/li>
- (MID(A2,ROW(INDIRECT(\u201c1:\u201d&LEN(A2))),1)*1) \u2013 This part of the formula would return an array of #VALUE! errors or numbers based on the string. All the text characters in the string become #VALUE! errors and all numerical values stay as-is. This happens as we have multiplied the MID function with 1.<\/li>
- IFERROR((MID(A2,ROW(INDIRECT(\u201c1:\u201d&LEN(A2))),1)*1),\u201d\u201d) \u2013 When IFERROR function is used, it would remove all the #VALUE! errors and only the numbers would remain. The output of this part would look like this \u2013 {\u201c\u201d;\u201d\u201d;\u201d\u201d;\u201d\u201d;\u201d\u201d;\u201d\u201d;\u201d\u201d;\u201d\u201d;\u201d\u201d;\u201d\u201d;\u201d\u201d;\u201d\u201d;\u201d\u201d;\u201d\u201d;\u201d\u201d;\u201d\u201d;1;0;0}<\/li>
- =TEXTJOIN(\u201c\u201d,TRUE,IFERROR((MID(A2,ROW(INDIRECT(\u201c1:\u201d&LEN(A2))),1)*1),\u201d\u201d)) \u2013 The TEXTJOIN function now simply combines the string characters that remains (which are the numbers only) and ignores the empty string.<\/li><\/ul>\n\n\n\n
Pro Tip:<\/strong> If you want to check the output of a part of the formula, select the cell, press F2 to get into the edit mode, select the part of the formula for which you want the output and press F9. You will instantly see the result. And then remember to either press Control + Z or hit the Escape key. DO NOT hit the enter key.<\/p>\n\n\n\n
![\"\"](\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/source\/rev-029371b\/wp-content\/uploads\/2015\/09\/Download-File.png\")
<\/a><\/figure><\/div>\n\n\n\nDownload the Example File<\/strong><\/p>\n\n\n\n
You can also use the same logic to extract the text part from an alphanumeric string. Below is the formula that would get the text part from the string:<\/p>\n\n\n\n
=TEXTJOIN(\"\",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT(\"1:\"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT(\"1:\"&LEN(A2))),1),\"\"))<\/pre>\n\n\n\n
A minor change in this formula is that IF function is used to check if the array we get from MID function are errors or not. If it\u2019s an error, it keeps the value else it replaces it with a blank.<\/p>\n\n\n\n
Then TEXTJOIN is used to combine all the text characters.Caution<\/strong>: While this formula works great, it uses a volatile function (the INDIRECT function). This means that in case you use this with a huge dataset, it may take some time to give you the results. It\u2019s best to create a backup before you use this formula in Excel.<\/p>\n\n\n\n
Extract Numbers from String in Excel (for Excel 2013\/2010\/2007)<\/h2>\n\n\n\n
If you have Excel 2013. 2010. or 2007, you can not use the TEXTJOIN formula, so you will have to use a complicated formula to get this done.<\/p>\n\n\n\n
Suppose you have a dataset as shown below and you want to extract all the numbers in the string in each cell.<\/p>\n\n\n\n
<\/figure><\/div>\n\n\n\nThe below formula will get this done:<\/p>\n\n\n\n
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {\"0\",\"1\",\"2\",\"3\",\"4\",\"5\",\"6\",\"7\",\"8\",\"9\"}, \"\")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT(\"$1:$\"&LEN(A2))),1))* ROW(INDIRECT(\"$1:$\"&LEN(A2))),0), ROW(INDIRECT(\"$1:$\"&LEN(A2))))+1,1) * 10^ROW(INDIRECT(\"$1:$\"&LEN(A2)))\/10),\"\")<\/pre>\n\n\n\nIn case there is no number in the text string, this formula would return blank (empty string).<\/p>\n\n\n\n
Although this is an array formula, you don\u2019t need<\/strong> to use \u2018Control-Shift-Enter\u2019 to use this. A simple enter works for this formula.<\/p>\n\n\n\n
Again, this formula will extract all the numbers in the string no matter the position. For example, if the text is \u201cThe price of 10 tickets is USD 200\u201d, it will give you 10200 as the result.Caution<\/strong>: While this formula works great, it uses a volatile function (the INDIRECT function). This means that in case you use this with a huge dataset, it may take some time to give you the results. It\u2019s best to create a backup before you use this formula in Excel.<\/p>\n\n\n\n
Separate Text and Numbers in Excel Using VBA<\/h2>\n\n\n\n
If separating text and numbers (or extracting numbers from the text) is something you have to often, you can also use the VBA method.<\/p>\n\n\n\n
All you need to do is use a simple VBA code to create a custom User Defined Function (UDF) in Excel, and then instead of using long and complicated formulas, use that VBA formula.<\/p>\n\n\n\n
Let me show you how to create two formulas in VBA \u2013 one to extract numbers and one to extract text from a string.<\/p>\n\n\n\n
Extract Numbers from String in Excel (using VBA)<\/h3>\n\n\n\n
In this part, I will show you how to create the custom function to get only the numeric part from a string.<\/p>\n\n\n\n
Below is the VBA code we will use to create this custom function:<\/p>\n\n\n\n
Function GetNumeric(CellRef As String)\nDim StringLength As Integer\nStringLength = Len(CellRef)\nFor i = 1 To StringLength\nIf IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)\nNext i\nGetNumeric = Result\nEnd Function<\/pre>\n\n\n\n
Here are the steps to create this function and then use it in the worksheet:<\/p>\n\n\n\n
- Go to the Developer tab.<\/li>
- Click on Visual Basic (You can also use the keyboard shortcut ALT + F11)<\/li>
- In the VB Editor backend that opens, right-click on any of the workbook objects.<\/li>
- Go to Insert and click on Module. This will insert the module object for the workbook.<\/li>
- In the Module code window, copy and paste the VBA code mentioned above.<\/li>
- Close the VB Editor.<\/li><\/ul>\n\n\n\n
Now, you will be able to use the GetText function in the worksheet. Since we have done all the heavy lifting in the code itself, all you need to do is use the formula =GetNumeric(A2). <\/p>\n\n\n\n
This will instantly give you only the numeric part of the string.<\/p>\n\n\n\n
![\"Using](\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2017\/08\/Using-Custom-VBA-Function-to-get-only-the-numeric-part-from-a-string-in-Excel.jpg\")
<\/figure><\/div>\n\n\n\nNote that since the workbook now has VBA code in it, you need to save it with .xls or .xlsm extension.<\/p>\n\n\n\n
<\/a><\/figure><\/div>\n\n\n\nDownload the Example File<\/strong><\/p>\n\n\n\n
In case you have to use this formula often, you can also save this to your Personal Macro Workbook. This will allow you to use this custom formula in any of the Excel workbooks that you work with.<\/p>\n\n\n\n
Extract Text from a String in Excel (using VBA)<\/h3>\n\n\n\n
In this part, I will show you how to create the custom function to get only the text part from a string.<\/p>\n\n\n\n
Below is the VBA code we will use to create this custom function:<\/p>\n\n\n\n
Function GetText(CellRef As String)\nDim StringLength As Integer\nStringLength = Len(CellRef)\nFor i = 1 To StringLength\nIf Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)\nNext i\nGetText = Result\nEnd Function<\/pre>\n\n\n\n
Here are the steps to create this function and then use it in the worksheet:<\/p>\n\n\n\n
- Go to the Developer tab.<\/li>
- Click on Visual Basic (You can also use the keyboard shortcut ALT + F11)<\/li>
- In the VB Editor backend that opens, right-click on any of the workbook objects.<\/li>
- Go to Insert and click on Module. This will insert the module object for the workbook.
- If you already have a module, double-click on it (no need to insert a new one if you already have it).<\/li><\/ul><\/li>
- In the Module code window, copy and paste the VBA code mentioned above.<\/li>
- Close the VB Editor.<\/li><\/ul>\n\n\n\n
Now, you will be able to use the GetNumeric function in the worksheet. Since we have done all the heavy lifting in the code itself, all you need to do is use the formula =GetText(A2).<\/p>\n\n\n\n
This will instantly give you only the numeric part of the string.<\/p>\n\n\n\n
![\"Using](\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2017\/08\/Using-Custom-VBA-Function-to-get-only-the-Text-part-from-a-string-in-Excel.jpg\")
<\/figure><\/div>\n\n\n\nNote that since the workbook now has VBA code in it, you need to save it with .xls or .xlsm extension.<\/p>\n\n\n\n
In case you have to use this formula often, you can also save this to your\u00a0Personal Macro Workbook. This will allow you to use this custom formula in any of the Excel workbooks that you work with.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Sumit Bansal There is no inbuilt function in Excel to extract the numbers from a string in a cell (or vice versa \u2013 \u00a0remove the numeric part and extract the text part from an alphanumeric string). However, this can be done using a cocktail of Excel functions or some simple VBA code. Let me first show you what Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18],"tags":[672,671,673],"class_list":["post-3210","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","tag-extract-numbers-from-a-cell","tag-extract-numbers-from-a-string-in-excel","tag-extract-numbers-from-a-text"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3210","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3210"}],"version-history":[{"count":2,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3210\/revisions"}],"predecessor-version":[{"id":3215,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3210\/revisions\/3215"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3210"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3210"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3210"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}