How-To

How to Extract a Number From a String in Excel

By Adam Davidson

Published June 7, 2023

When you import data into Excel, you may need to separate it. Learn how to extract a number from a string in Excel using this guide.

Excel is a powerful spreadsheet, but it can’t work miracles. If you’re importing data, and that data is a mess, then Excel won’t be able to do much with it. For example, if you have values that are attached to other characters as part of a string, you won’t be able to perform calculations on them unless you extract them first.

Thankfully, it’s possible to do exactly that, although it can be more complicated than you might first think. Here’s how to extract a number from a string in Excel.

How to Extract a Number from the End of a String Using Flash Fill

A quick and easy way to extract a number from the end of a string is to use Excel’s Flash Fill feature. This feature allows you to quickly fill a range of cells based on the pattern detected in one specific cell.  One downside of this method is that it requires you to do the first extraction yourself and if you only want to extract a number from a single string, it won’t be of any use.

In most cases, Flash Fill can correctly extract the number from the end of a string, although it is not infallible, so you should always check the results carefully. If it doesn’t work correctly, try manually filling the first two or three cells rather than just the first one.

To extract a number from the end of a string using Flash Fill:

1. Select the cell next to the first cell containing your strings.
   
2. Enter the number that appears at the end of the string.
   
3. If all of your data is in a continuous column with no gaps, select the cell directly below the one you just entered the number in.
   
4. If your data has gaps in some rows, select the first cell and all the cells beneath that you want to extract numbers from, including any blank rows.
   
5. Press Ctrl+E on Windows or Cmd+E on Mac to run the Flash Fill feature.
6. Your remaining cells will automatically be filled based on the pattern that Excel has spotted. In most cases, the numbers from the end of the string will be extracted correctly.
   

How to Extract a Number from the End of a String Using a Formula

One downside of using the Flash Fill method is that it requires you to extract numbers from the string for the first row yourself. It is possible to extract a number from the end of a string using a formula, although the formula is quite complex.

To extract a number from the end of a string using a formula:

1. Click in the cell where you want the extracted number to appear.
2. Enter the following formula, replacing ‘B2’ with the cell containing your string:

   =VALUE(RIGHT(B2,LEN(B2)-MAX(IF(ISNUMBER(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1)=FALSE,ROW(INDIRECT("1:"&LEN(B2))),0))))

3. Press Enter.
4. The number should be extracted from the string.
5. To apply the formula to any remaining cells, click the drag handle in the bottom right-hand corner of the cell.
   
6. Drag down over the cells where you want the formula to be replicated.
   
7. Release your mouse and the formula will be copied to the selected cells.
8. These cells should now contain your extracted numbers.
   
9. Any cells without a string to extract from will return an error. If you want to instead return an empty cell, you can instead use the formula:

   =IFERROR(VALUE(RIGHT(B2,LEN(B2)-MAX(IF(ISNUMBER(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1)=FALSE,ROW(INDIRECT("1:"&LEN(B2))),0)))),"")

10. Any empty cells will now return a blank space.
    

You may be wondering what this formula does. In brief, it generates an array that is the same length as the string. It places each of the characters from the string into this array in order. It then evaluates each of those characters to see if they are a number or not. Any characters that are numbers are converted to zero. Any characters that are not numbers are converted to a number representing its position in the string.

For example, AB5HG23 would convert to 1, 2, 0, 4, 5, 0, 0. It then looks for the highest value in this list, which is the position of the last character that isn’t a number. Finally, it returns all the characters after this point, which are the numbers at the end of the string. The VALUE function then converts this back into a number. Simple, right?

How to Extract a Number from the Start of a String Using Flash Fill

You can also use Flash Fill to extract a number from the start of a string by filling in the first example yourself and allowing Excel to stop the pattern.

To extract a number from the start of a string using Flash Fill:

1. Select the cell to the right of the first string that has a number you want to extract.
2. Enter the number that appears at the start of the string.
   
3. If all of your data is in a continuous column without gaps, select the cell directly below.
   
4. If your data has gaps, select the first cell and all the cells beneath that you want to extract numbers from, including any blank rows.
   
5. Press Ctrl+E on Windows or Cmd+E on Mac to start Flash Fill.
6. The cells you selected should now show the extracted numbers.
   

How to Extract a Number from the Start of a String Using a Formula

You can also use a formula to extract a number from the start of a string. This works in a similar way to the formula in the previous section but finds the position of the first non-numeric character and returns all the characters before that.

To extract a number from the start of a string using a formula:

1. Select the cell where you want the extracted number to appear.
2. Enter the following formula, replacing ‘B18’ with the cell containing your string:

   =VALUE(LEFT(B18,MATCH(FALSE,ISNUMBER(MID(B18,ROW(INDIRECT("1:"&LEN(B18)+1)),1)*1),0)-1))

3. Press Enter and the number will be extracted.
   
4. You can use Autofill in Excel to copy the formula to any other cells you require.
5. If you want any empty rows to return an empty result, use the following formula instead:

   =IFERROR(VALUE(LEFT(B18,MATCH(FALSE,ISNUMBER(MID(B18,ROW(INDIRECT("1:"&LEN(B18)+1)),1)*1),0)-1)),"")

How to Extract Numbers from Anywhere in a String Using Flash Fill

As mentioned previously, Flash Fill may not work if the first cell doesn’t match the format of some of the other cells. In this case, you may need to manually calculate two or three cells before Flash Fill gives you the result you need.

To extract numbers from anywhere in a string using Flash Fill:

1. In the cell next to the first string, manually type the numbers that are in the string.
   
2. If all of your data is in a continuous column, select the cell directly below.
   
3. If your data has some gaps, select the first cell and all the cells beneath that you want to extract numbers from, including any blank rows.
   
4. Press Ctrl+E on Windows or Cmd+E on Mac to start Flash Fill.
5. The cells should now fill with the extracted numbers.
   

How to Extract Numbers from Anywhere in a String Using a Formula

You can use a formula to extract all the numbers from a string, no matter where they appear.

To extract numbers from anywhere in a string using a Formula:

1. Select the cell where you want the extracted number to appear.
2. Enter the following formula, replacing ‘B9’ with the cell containing your string:

   =SUMPRODUCT(MID(0&B9,LARGE(INDEX(ISNUMBER(--MID(B9,ROW(INDIRECT("1:"&LEN(B9))),1))*ROW(INDIRECT("1:"&LEN(B9))),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10)

3. Your formula should extract all of the numbers from the string.
   
4. If you want any blank cells to return an empty cell, rather than zero, use this formula instead:

   =IFERROR(SUMPRODUCT(MID(0&B9,LARGE(INDEX(ISNUMBER(--MID(B9,ROW(INDIRECT("1:"&LEN(B9))),1))*ROW(INDIRECT("1:"&LEN(B9))),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10),"")

5. You can use Autofill to apply the formula to any other cells in your spreadsheet.
6. Any strings that don’t contain any numbers will return a zero. You can learn how to convert a zero to a dash in Excel if you want to change this.

Extend Your Excel Skills

Learning how to extract a number from a string in Excel can help you quickly extract the data you need from a list of strings. Whilst the formulas required may be complex, you don’t need to understand exactly how they work; just that they do!

There are more useful Excel tricks you can learn to take your skills to the next level. You can learn how to count cells with text in them, or how to calculate someone’s age from their date of birth. You can even create a random number generator in Excel.