Extract Numbers From Text in Excel

To extract numbers from text in Excel, first decide where the number sits: at the start, at the end, after a separator, or scattered through the cell. Each position has its own text function, and you finish with VALUE so the result is a number you can add up. This guide covers each case with a formula that works on any length of text.

Which formula should you use?#

The right formula depends on where the number is and whether its length changes. Use this table to jump to the right section:

Every formula ends in a real number, not text, thanks to VALUE or --. The sections below explain each one.

How to extract a number at the start of text#

Use LEFT when the number comes first. For a weight like "250kg", the unit is always two characters, so keep everything except the last two:

=VALUE(LEFT(A2,LEN(A2)-2))

LEN counts the characters (5), minus 2 leaves 3, and LEFT takes the first 3: "250". VALUE turns it into 250. This works for "75kg" and "1200kg" too, because the formula never assumes the number's length.

If the number ends at a space, as in "42 units", find the space instead: =VALUE(LEFT(A2,FIND(" ",A2)-1)) returns 42.

How to extract a number at the end of text#

Use RIGHT when the number comes last. For invoice codes like "INV-2048", FIND gives the position of the dash, and everything after it is the number:

=VALUE(RIGHT(A2,LEN(A2)-FIND("-",A2)))

The dash is character 4 and the text has 8 characters, so RIGHT takes the last 4: "2048". VALUE returns 2048. The same formula gives 731 for "INV-731" and 15002 for "INV-15002". The lesson on LEFT, RIGHT, MID and LEN practises exactly this kind of split.

How to extract a number with TEXTBEFORE and TEXTAFTER#

In Microsoft 365 and Excel 2024, TEXTAFTER returns everything after a separator, so you don't need to count characters:

=VALUE(TEXTAFTER(A2,"-"))

For "INV-2048" the result is 2048. TEXTBEFORE does the opposite. Combine them to pull a number out from between two separators. For "Order 5531 shipped":

=VALUE(TEXTBEFORE(TEXTAFTER(A2," ")," "))

TEXTAFTER returns "5531 shipped", and TEXTBEFORE keeps the part before the next space: 5531. More examples are in how to extract text between two characters, and the same idea pulls the domain out of an email address.

How to extract a number from the middle of text#

MID takes characters from any position: MID(text, start, how many). For "Box 12 of 40", the number starts at character 5 and is 2 long:

=VALUE(MID(A2,5,2))

The result is 12. When the position changes from row to row, calculate it with FIND. The number starts after the first space and ends before " of":

=VALUE(MID(A2,FIND(" ",A2)+1,FIND(" of",A2)-FIND(" ",A2)-1))

This also returns 12, and it works for "Box 7 of 40" or "Box 125 of 400". The MID function guide explains the three arguments.

How to extract a number with a decimal point#

Split at the characters around the number, not at the digits, and the decimal point comes along. For "Price: 12.50 USD", the number sits between ": " and the next space:

=VALUE(TEXTBEFORE(TEXTAFTER(A2,": ")," "))

TEXTAFTER returns "12.50 USD", TEXTBEFORE keeps "12.50", and VALUE returns 12.5. Format the cell with two decimals if you want to see 12.50. In older versions, the MID and FIND pattern from the previous section does the same job. If VALUE returns #VALUE! here, your computer may use a comma as the decimal separator, so check the regional settings.

How to extract every digit from text#

When digits are scattered through the text, Microsoft 365 can test each character and keep only the digits:

=--TEXTJOIN("",TRUE,IFERROR(--MID(A2,SEQUENCE(LEN(A2)),1),""))

For "Ref A7-B3-9" the result is 739. Here is how it works:

1. SEQUENCE(LEN(A2)) makes the list 1, 2, 3 and so on, one number per character.
2. MID(A2, ..., 1) splits the text into single characters.
3. -- turns digits into numbers; letters, spaces and dashes become errors.
4. IFERROR swaps the errors for empty text, and TEXTJOIN glues the digits together.
5. The -- at the front turns "739" into the number 739.

This joins every digit, so it drops decimal points and minus signs, and it can't separate two numbers in one cell. Recent Microsoft 365 builds also include REGEXEXTRACT, which can return the first run of digits with =--REGEXEXTRACT(A2,"\d+"). Older versions need Flash Fill: type the first answer, then press Ctrl+E on Windows, or use Data > Flash Fill on a Mac.

Why you need VALUE at the end#

Text functions always return text, even when the result looks like a number. Without VALUE, "2048" sits on the left of the cell, SUM ignores it, and a lookup against the number 2048 fails. The VALUE function guide covers the details, and -- in front of a formula does the same job.

Keep the result as text when the digits are an ID with leading zeros, such as "00731". VALUE would turn it into 731. For more on text that looks like numbers, see numbers stored as text.

Practise it#

Splitting codes and IDs is one of the most common data-cleaning jobs in a real sheet. Practise LEFT, RIGHT, MID and LEN in a live spreadsheet: you split real product codes and the lesson checks each formula instantly. Then practise TEXTBEFORE and TEXTAFTER to split text at a separator in one step.