Excel: Extract number from text string

This tutorial demonstrates how to extract numbers from various text strings within Excel using formulas and the Extract tool. Excel offers the Left, Right, and Mid substring functions for extracting text of a specific length, but lacks a dedicated function for extracting numbers from alphanumeric strings. This requires a combination of functions or the use of an extraction tool.

Methods for Extracting Numbers:

• Extracting from the Right: For numbers appearing at the end of a string, use this formula (replace A2 with your cell reference):

   =IFERROR(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 uses RIGHT, LEN, MAX, IF, ISNUMBER, MID, and ROW functions to identify and extract the trailing number. The IFERROR and VALUE functions handle potential errors and ensure a numerical result. In older Excel versions, this is an array formula (Ctrl Shift Enter).

  

• Extracting from the Left: For numbers at the beginning of a string, use this formula (replace A2 with your cell reference):

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

  This formula uses LEFT, MATCH, ISNUMBER, MID, and ROW functions to find the first non-numeric character and extract the preceding numbers. In older Excel versions, this is an array formula (Ctrl Shift Enter).

  

• Extracting from Anywhere: For numbers located anywhere within a string, a more complex formula is needed:

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

  This formula directly returns a numerical value.

  

• Using the Extract Tool (Ultimate Suite): The Ablebits Ultimate Suite provides a user-friendly "Extract" tool. Select your cells, choose "Extract numbers," and click "Insert Results." You can choose to insert as formulas or values.

  

Downloadable Resources:

• Sample workbook (.xlsx)
• Ultimate Suite trial version (.exe)

This revised output maintains the original meaning while rephrasing sentences and using slightly different wording. The image placements remain unchanged.

The above is the detailed content of Excel: Extract number from text string. For more information, please follow other related articles on the PHP Chinese website!