How to Extract a Number from a String in Excel

Extracting numbers from strings in Microsoft Excel is a common task that many users encounter in their data manipulation and analysis endeavors. Often, data imported into Excel workbooks or collected from various sources may contain alphanumeric characters, and isolating numerical values from such strings is essential for performing calculations, creating charts, or simply cleaning and formatting the data. Whether you are a seasoned Excel user or a beginner, mastering the art of extracting numbers from strings can significantly enhance your ability to work with diverse datasets efficiently. In this comprehensive guide, we will explore various techniques and functions that Excel offers for this purpose, from simple and straightforward methods to more advanced, versatile approaches.

Steps to Extract Numbers from Strings in Excel

1. Using Excel’s Flash Fill Feature

A quick and user-friendly method for extracting numbers from the end of a string is by employing Excel’s Flash Fill feature. This feature allows you to fill a range of cells based on patterns detected in one specific cell. However, it necessitates manual intervention for the first extraction.

Steps:

1. Select the cell next to the first cell containing your strings and enter the number located at the end of the string.

2. If your data is arranged in a continuous column without any gaps, choose the cell directly beneath where you just entered the number in the Excel sheet.

3. If there are gaps, choose all the cells beneath that you wish to extract numbers from, including blank rows.

4. Press Ctrl+E on Windows or Cmd+E on Mac to execute Flash Fill.

In most cases, Excel will automatically extract numbers from the string, assuming a recognizable pattern.

READ ALSO: How to Fix Excel Not Auto Calculating

2. Extracting Numbers from the End of a String Using a Formula

If Flash Fill doesn’t suit your situation or if you want a more automated approach, you can use a formula. This method does not require manual input for the first extraction.

Steps:

Select the cell where you want the extracted number, and enter the following formula, replacing A2 with the cell containing your string and press Enter:

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

This formula efficiently extracts numbers from the end of the string.

You can then apply it to other cells by using the drag handle in the bottom-right corner of the cell.

3. Extracting Numbers from the Start of a String Using Flash Fill

Excel’s Flash Fill can also be used to extract numbers from the start of a string.

Steps:

1. Choose the cell next to the first string with the number you want to extract.
2. Manually enter the number that is at the start of the string.
3. If your data is continuous, select the cell directly below it. If there are gaps, choose all the cells beneath that you wish to extract numbers from.

4. Press Ctrl+E on Windows or Cmd+E on Mac.

Excel will use the entered value to detect the pattern and extract numbers accordingly.

4. Extracting Numbers from the Start of a String Using a Formula

If Flash Fill isn’t the optimal method for your task, you can use a formula to extract numbers from the beginning of a string.

Steps:

1. Select the cell where you want the extracted number.
2. Enter the following formula, replacing A2 with the cell containing your string and press Enter:

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

3. This formula retrieves the number from the start of the string. You can extend it to other cells using Excel’s Autofill feature.

5. Extracting Numbers from Anywhere in a String Using Flash Fill

When you’re dealing with inconsistent patterns in your data, you might need to manually calculate a few cells before using Flash Fill effectively.

Steps:

1. In the cell beside the first string, manually input the numbers present in the string.
2. If your data is continuous, select the cell directly below the one with manual input. If there are gaps, choose the first cell and all the cells below where you want numbers extracted.
3. Press Ctrl+E on Windows or Cmd+E on Mac.

Excel will now extract numbers based on the pattern it detects.

6. Extracting Numbers from Anywhere in a String Using a Formula

To extract numbers from anywhere within a string, irrespective of pattern, you can use a formula.

Steps:

1. Select the cell where you want the extracted number.
2. Enter the following formula, replacing A2 with the cell containing your string and press Enter.:

=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)

This formula will efficiently extract all the numbers from the string, regardless of their positions within it.

Conclusion:

Mastering the art of extracting numbers from strings in Excel is a fundamental skill that empowers users in various fields. With the techniques and functions you’ve learned, you’re now well-equipped to handle diverse data scenarios, making Excel an even more valuable tool for data analysis and manipulation. Keep honing your skills to unlock Excel’s full potential in your projects.