Extracting numbers from a string in excel is a must-have skill in data cleaning.

There are different methods of this extraction depending on the position of the numbers.

## Extracting numbers at the end of a string

**Scenario 1: There is a separator between text and numbers**

### Use Text to Columns

### Use the Flash fill tool

This is the simplest as all you have to do is to give excel an example and then press Ctrl + E

**NB: As simple as this method may be, be careful to check the final results as it does not always give correct results.**

### Use a formula

**How the formula works:**

⭐ **FIND(“-“,[@[Project Number]])** returns the position of the separator in the string

⭐ **LEN([@[Project Number]])** returns the length of the string

⭐ **LEN([@[Project Number]])-FIND(“-“,[@[Project Number]])** returns the number of characters after the separator

⭐ **RIGHT([@[Project Number]],LEN([@[Project Number]])-FIND(“-“,[@[Project Number]]))** extracts the above number of characters from the right of the string

NB: This is the only dynamic method–unlike the above 2 methods if the string changes the method is able to extract the numbers.

PS: Text to Columns & Flash fill will also work if numbers are at the beginning of the string

The formula changes in the case of extracting numbers at the beginning of a string

**How the formula works,**

**MID(C3,SEQUENCE(LEN(C3)),1)** splits the string into separate characters **{“8″;”8″;”6″;”A”;”G”;”E”;”T”;”Q”;”Y”}**

Since you want to count the numbers in the string, add zero to convert to the number MID(C3,SEQUENCE(LEN(C3)),1)+0 this results in **{8;8;6;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}**

COUNT function ignores the **#VALUE!** error and returns just numbers count

The LEFT function extracts the characters from the left of a string based on the count number.

**Scenario 2: There is NO separator between text and numbers**

Unlike the above scenario, you cannot use the **Text to Column** method there.

You may use the flash fill method but be very careful as it does not always give the correct results

### Use a Nested function

**How the Formula Works**

**SEQUENCE(10,,0)** returns an array of all possible numbers {0,1,2,3,4,5,6,7,8,9}

- If you don’t have Office365 you can replace it with this array

**SEARCH(SEQUENCE(10,,0),C3&SEQUENCE(10,,0))** returns the position of any of the numbers in the text

**MIN(SEARCH(SEQUENCE(10,,0),C3&SEQUENCE(10,,0)))** returns the 1st position that a number occurs in the text

To include this first number in the extraction, add 1 **(MIN(SEARCH(SEQUENCE(10,,0),C3&SEQUENCE(10,,0))))+1**

**LEN(C3)-(MIN(SEARCH(SEQUENCE(10,,0),C3&SEQUENCE(10,,0))))+1** returns a count of the number characters

RIGHT function extracts these number characters

NB: If you do not have Office365 use below formula

**=RIGHT(C3,LEN(C3)-(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C3&{0,1,2,3,4,5,6,7,8,9})))+1)**

## Extracting numbers mixed in a string

Incase you don’t have Office365 you can replace the SEQUENCE function with **ROW(INDIRECT(“1:”&LEN(B3)))**

**=TEXTJOIN(“”,TRUE,IFERROR(MID(B3,ROW(INDIRECT(“1:”&LEN(B3))),1)+0,””))**

If you do not have the TEXTJOIN function, you can use the CONCAT function as shown below

**=CONCAT(IFERROR(MID(B3,ROW(INDIRECT(“1:”&LEN(B3))),1)+0,””))**

If you are using Office 2013 and below try below formula…thanks to Muhammad Nauman

**=SUM(MID(0&B3, LARGE(INDEX(ISNUMBER(0+MID(B3,INDEX(ROW(INDIRECT(“1:”&LEN(B3))),,),1))*(INDEX( ROW(INDIRECT(“1:”&LEN(B3))),,)),0), INDEX(ROW(INDIRECT(“1:”&LEN(B3))),,))+1,1) * 10^(INDEX(ROW(INDIRECT(“1:”&LEN(B3))),,))/10)**

## Find and Replace ALL LETTERS in Excel

To replace all letters once follow the steps below:

- Copy all the mixed string
- Open a new Microsoft Word Document
- Paste the data there
- Click Ctrl + H
- Go to Special and Select Any Letter

## Using Power Query to Extract Numbers from a String

**Text.Select([Project Number],{“0”..”9″})**

You cited a formula by Muhammad Nauman in the section for extracting digits in a mixed string for those using XL2013 and earlier. I believe this shorter formula will also work…

=MID(SUMPRODUCT(–MID(“01″&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID(“01″&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

**Commit formula using CTRL+SHIFT+ENTER

Note that, as written, cell A1 cannot contain more than 300 characters total and that the formula can only return a maximum of 14 total digits.

how to extract specific number from a string of numbers in row, eg

0 0 0 0 8 0 0 2 3 0 0 – how to extract number 8?

Assuming your string is in cell G15

=INDEX(MID(G15,SEQUENCE(LEN(G15)),1),

MATCH(TRUE,ISNUMBER(FIND(“8”,MID(G15,SEQUENCE(LEN(G15)),1))),0))

Tried “=RIGHT(C3,LEN(C3)-(MIN(SEARCH(SEQUENCE(10,,0),C3&SEQUENCE(10,,0))))+1)” but it always returned the entire cell’s text. Worked only when the cell contained ONLY a number formatted as text, e.g. “90.25%”. Confirmed I am using Excel 365. Not going to try the array version because I have several thousand rows.

Please provide a sample workbook so that I can see where the problem is

how to take account number out of cell

123 Louisville Gas

4567 Louisville Water Company

89 Spectrum

1 Waste Management

I want the number in one cell and the company in another.

Hi Kim,

Try below formula

=CONCAT(IFERROR(MID(A1,SEQUENCE(LEN(A1)),1)+0,””))

Assuming your text is in cell A1

Hi, How to take Total of this formula (right side of the ‘&’ only) to another cell.

=”SFD869: “& 100000-22500-1900-2500-500

Use this function

=SUM(IFERROR(TEXTSPLIT(TextString,,{“&”,”-“})+0,””))

how to get only the serial number on the following example http://go2se.com/ref=WXXXX–/sn=085476328804

If you have office365 use below function

=TEXTAFTER(B2,”sn=”)

What could you use with these formulas to where it extracts number after strings that might also contain a number.

For example, using your methods I can get 9513 from SUP:SUB9513.

However, I need to get 10849 from SUP:L-310849.

This is using Excel 2019. We haven’t switched to 365 yet.

Hi Page,

Are they always the last 5 numbers?

If Yes, then use the right function =RIGHT(B2,5)+0

If not tell me what pattern they follow

How do I separate these into two columns – column 1 has 1, 2, 3,… column 2 has 5537.25, 5189.07, 7287.69,….

Name: LOTS : 1

Area: 5537.25 Sq. Ft.

Name: LOTS : 2

Area: 5189.07 Sq. Ft.

Name: LOTS : 3

Area: 7287.69 Sq. Ft.

You can use the TEXTSPLIT or FILTERXML functions.

Send me a workbook sample

I have this formula : =(ASAPEXTRACTNUMBERS(CONCATENATE(O13))), which returning back a string with all numbers in a cell, but I need the minimum value instead of a serie of number. eg. original cell contains:

Functional Test.: 4

In- Circuit Test: 4

Inspection, Visual: 7

Run-in Test: 4

Use below DAF

=MAX(IFERROR(TEXTAFTER(TEXTSPLIT(C3,CHAR(10)),”:”)+0,””))

If you do not have Office365, use below function

=MAX(ISNUMBER(FIND(ROW($1:$100),C3))*ROW($1:$100))

Hello, I am looking to find a formula to calculate the number of visits someone has remaining on a package, however the string shows the number of visits used. The string is “Physical Therapy Initial Evaluation – 1/1 Physical Therapy Follow-Up Visit – 3/9”. I would like it to show as “6” as the person has used 3 out of 9 visits and has 6 remaining. Thanks for your help!

Please send a sample worksheet…Textsplit can work

Hi! for the Extracting numbers mixed in a string option, it was not working for me until I wrote “Arrayformula” before the MID function. Hope it helps someone else!

Thanks for the info

There’s also a long way by using the substitute formula assuming you’re looking at cell A1.

For removing Numbers:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, “0”, “”), “1”, “”), “2”, “”), “3”, “”), “4”, “”), “5”, “”), “6”, “”), “7”, “”), “8”, “”), “9”, “”)

For removing letters:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, “A”, “”), “B”, “”), “C”, “”), “D”, “”), “E”, “”), “F”, “”), “G”, “”), “H”, “”), “I”, “”), “J”, “”), “K”, “”), “L”, “”), “M”, “”), “N”, “”), “O”, “”), “P”, “”), “Q”, “”), “R”, “”), “S”, “”), “T”, “”), “U”, “”), “V”, “”), “W”, “”), “X”, “”), “Y”, “”), “Z”, “”)

Awesome Alternative!!!

need to extract data between multiple cells containing text & numbers Example:

if cell A1 texts like CAI I need the result to be SLB // IF CONTAIN NUMBER like 222 I need the results to be VIVO ALEX

Plz note: maybe the same cell contains a mix between (Numbers & Texets )

Colum A ColumB

CAI SLB

EBE SLB

BAG SLB-Bag

SCCR SLB

222 VivO ALX

ENO WAS

223 VivO CAI