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

Read more on Find & Replace

Using Power Query to Extract Numbers from a String

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

Seeking Microsoft Certification?

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.