How to extract a number from a string in Excel

To extract numbers from text strings in Excel, use a combination of text functions or formulas depending on the data structure. If numbers are in consistent positions, combine LEFT, RIGHT, MID, and SEARCH functions to isolate them. For unpredictable placements, apply an array formula using TEXTJOIN, MID, ROW, and INDIRECT to extract all numeric characters. For large datasets, consider Power Query to create reusable workflows that filter out non-numeric characters or apply regular expressions. Each method suits different scenarios: position-based extraction works for predictable formats, the array formula handles scattered numbers, and Power Query streamlines bulk processing.

Sometimes, you need to pull a number out of a text string in Excel — like when you're dealing with product codes, log entries, or messy data exports. It’s not always straightforward since Excel doesn’t have a built-in function specifically for this task. But with a mix of text functions or a little help from formulas, it can be done pretty reliably.

Use LEFT, RIGHT, MID, and SEARCH together

If the number has a consistent position in the string — say it's always at the end, always at the beginning, or appears after a certain character — you can combine basic text functions to extract it.

For example:

• If your data looks like ABC123, and you want to get 123, you might use RIGHT() if you know how many digits are there.
• If the number is somewhere in the middle and marked by other characters (like Item:12345), you can use MID() with SEARCH() to locate the start and length of the number.

Here’s one way to do it:

• Find where the number starts using SEARCH()
• Use MID() to grab the substring starting from that point
• Optionally, clean up the result or limit the number of characters extracted

This method works best when the structure of the string is somewhat predictable.

Try a formula that pulls all numbers from any position

When the number could be anywhere in the string and isn't easy to isolate with position-based functions, you can build a formula that strips out only the numeric characters.

One common approach uses TEXTJOIN, MID, ROW, and INDIRECT in an array formula:

=TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:99")),1)*1,""))

This checks every character in cell A1, keeps only the digits, and joins them back into a single number.

Keep in mind:

• This formula assumes the string is in cell A1
• You may need to adjust the range "1:99" if your strings are longer
• In older versions of Excel without TEXTJOIN, you’ll need a custom VBA function instead

Consider Power Query for bulk processing

If you’re working with a large dataset and need to extract numbers regularly, Power Query is a solid option.

You can:

• Load your data into Power Query
• Add a custom column that filters out non-numeric characters
• Use simple transformations or even regular expressions (with some M code knowledge)

This method takes a bit more setup, but once it’s done, you can reuse it across files and automatically update as new data comes in.

Extracting numbers from strings in Excel isn’t something you can do with a single click, but depending on your data pattern and version of Excel, you’ve got several solid options. Whether you stick with formulas or go the Power Query route, just make sure your method matches how your data is structured.

That’s about it — nothing too fancy, but definitely useful when you're cleaning up data.

The above is the detailed content of How to extract a number from a string in Excel. For more information, please follow other related articles on the PHP Chinese website!