How To Find The Middle Of Two Numbers

Extracting numbers from a list of cells with mixed text is a common information cleaning task.

Unfortunately, in that location is no direct menu or function created in Excel to help us accomplish this.

In this tutorial nosotros will look at three cases where you might have a list of mixed text, from which yous might want to extract numbers:

• When the number is always at the end of the text
• When the number is always at the beginning of the text
• When the number can be anywhere in the text

We will look at iii different formulas that can exist used to extract the numbers in each case.

At the end of the tutorial, we will also take a look at some VBA lawmaking that y'all can employ to accomplish the aforementioned.

Brace yourself, this might get a little complex!

• When Number is Ever at the End of the Text
• When Number is Always at the Beginning of the Text
• When Number can be Anywhere in the Text
• Using VBA to Extract Number from Mixed Text in Excel

Extracting a Number from Mixed Text when Number is Always at the Stop of the Text

Consider the following example:

Here, each prison cell has a mix of text and numbers, with the number always appearing at the finish of the text. In such cases, we will need to apply a combination of nested Excel functions to extract the numbers.

The functions nosotros will apply are:

• FIND – This function searches for a character or string in another string and returns its position.
• MIN – This function returns the smallest value in a listing.
• LEFT – This part extracts a given number of characters from the left side of a string.
• SUBSTITUTE – This office replaces a particular substring of a given text with another substring.
• IFERROR – This part returns an alternative result or formula if it finds an error in a given formula.

Substantially, nosotros will be using the above functions altogether to perform the following sequence of tasks:

1. Find the position of the start numeric value in the given cell
2. Extract and remove the text part of the given cell (by removing everything to the left of the first numeric digit)

The formula that nosotros will employ to extract the numbers from prison cell A2 is as follows:

=SUBSTITUTE(A2,LEFT(A2,MIN(IFERROR(FIND({0,1,2,three,four,5,6,7,8,9},A2),""))-one),"")

Allow us break downward this formula to sympathise it meliorate. Nosotros volition go from the inner functions to the outer functions:

• FIND({0,1,ii,3,iv,five,six,7,eight,ix},A2)

This function tries to find the positions of all the numbers (0-9) in the cell A2. Thus it returns an arrayformula:

{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,9,7,viii,#VALUE!}

It returns a #VALUE! error for all digits except the 7th, eighth, and 9th digits because it was not able to find these numbers (0,one,2,3,4,5,9) in prison cell A2. It simply returns the positions of numbers 6,7 and 8 which are the 9th, 7th, and 8th characters respectively in A2.

• IFERROR(FIND({0,1,two,3,four,5,6,vii,8,9},A2),"")

Side by side, the IFERROR function replaces all the error elements of the array with a bare (""). Every bit such it returns the arrayformula:

{"","","","","","",ix,7,viii,""}

• MIN(IFERROR(Detect({0,1,two,three,4,5,6,7,8,nine},A2),""))

After this the MIN office finds the array element with least value. This is basically the position of the outset numeric character in A2. The part now returns the value 7.

• LEFT(A2,MIN(IFERROR(Observe({0,1,2,3,iv,5,6,7,viii,9},A2),""))-i)

At this betoken nosotros want to extract all text characters from A2 (and so that nosotros can remove them). So we want the LEFT function to extract all the characters starting backwards from the 7-1= 6th grapheme. Thus nosotros use the higher up formula. The outcome we get at this betoken is "arnold". Notice we subtracted one from the second parameter of the LEFT function.

• SUBSTITUTE(A2,LEFT(A2,MIN(IFERROR(Discover({0,1,2,3,iv,v,6,7,8,ix},A2),""))-1),"")

Now all that'south left to exercise is remove the string obtained, by replacing it with a blank. This can be easily achieved by using the SUBSTITUTE role: We finally get the numeric characters in the mixed text, which is "786".

One time you are washed entering the formula, make sure you printing CTRL+SHIFT+Enter, instead of just the return key. This is because the formula involves arrays.

In a nutshell, here's what'due south happening when you break down the formula:

=SUBSTITUTE(A2,LEFT(A2,MIN(IFERROR(Find({0,i,two,iii,iv,5,half dozen,vii,8,9},A2),""))-i),"")

=SUBSTITUTE(A2,LEFT(A2,MIN(IFERROR({{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,9,7,8,#VALUE!}),""))-1),"")

=SUBSTITUTE(A2,LEFT(A2,MIN({"","","","","","",nine,7,viii,""}))-1),"")

=SUBSTITUTE(A2,"arnold","")

=786

When you drag the formula down to the residuum of the cells, here's the result y'all get:

Extracting a Number from Mixed Text when Number is Always at the Commencement of the Text

Now let united states consider the case where the numbers are always at the commencement of the Text.

Consider the following example:

Here, each jail cell has a mix of text and numbers, with the number always actualization at the beginning of the text. In such cases, we will once again need to employ a combination of nested Excel functions to extract the numbers.

In add-on to the functions we used in the previous formula, nosotros are going to use two additional functions. These are:

• MAX – This function returns the largest value in a listing
• RIGHT – This part extracts a given number of characters from the right side of a string
• LEN – This function finds the length of (number of characters in) a given string.

Essentially, we volition be using these functions altogether to perform the post-obit sequence of tasks:

1. Detect the position of the last numeric value in the given cell
2. Extract and remove the text part of the given cell (past removing everything to the right of the last numeric digit)

The formula that we volition employ to excerpt the numbers from prison cell A2 is as follows:

=SUBSTITUTE(A2,RIGHT(A2,LEN(A2)-MAX(IFERROR(Observe({0,one,2,three,4,5,vi,7,8,ix},A2),""))),"")

Permit u.s. intermission down this formula to understand it better. We will become from the inner functions to the outer functions:

• Find({0,ane,2,3,4,5,half dozen,7,eight,ix},A2)

This function tries to discover the positions of all the numbers (0-9) in the cell A2. Thus it returns an arrayformula:

{#VALUE!,#VALUE!,i,#VALUE!,#VALUE!,2,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

Information technology returns a #VALUE! error for all digits except the 3rd and 6th digits because it was not able to find these numbers (0,1,3,4,6,seven,8,nine) in cell A2. It simply returns the positions of numbers ii and 5 which are the 1st and 2nd characters respectively in A2.

• IFERROR(Observe({0,1,2,3,4,5,6,7,8,ix},A2),"")

Adjacent, the IFERROR function replaces all the error elements of the array with a blank. As such it returns the assortment formula:

{"","",1,"","",2,"","","",""}

• MAX(IFERROR(Discover({0,1,two,3,4,5,6,7,viii,9},A2),""))

After this the MAX function finds the array element with the highest value. This is basically the position of the terminal numeric character in A2. The part now returns the value ii.

• LEN(A2)-MAX(IFERROR(FIND({0,1,two,3,4,5,six,7,eight,9},A2),""))

At this point we want to extract all text characters from A2 (and so that we tin remove them). We need to specify how many characters we want to remove. This is obtained by computing the length of the cord in A2 minus the position of the terminal numeric value.Thus we will get the value viii-2 = "6".

• RIGHT(A2,LEN(A2)-MAX(IFERROR(FIND({0,one,2,3,4,5,6,seven,8,nine},A2),"")))

Nosotros tin can now employ the Right function to extract 6 characters starting from the 2d character onwards. The consequence nosotros go at this bespeak is "arnold".

• SUBSTITUTE(A2,RIGHT(A2,LEN(A2)-MAX(IFERROR(FIND({0,1,2,iii,4,v,6,7,8,9},A2),""))),"")

Now all that'southward left to practise is remove this string by replacing it with a blank. This is easily achieved past using the SUBSTITUTE function. Nosotros finally go the numeric characters in the mixed text, which is "25".

Again, in one case you lot are done entering the formula, don't forget to press CTRL+SHIFT+Enter, instead of simply the return key.

In a nutshell, here's what's happening when yous break down the formula:

=SUBSTITUTE(A7,RIGHT(A7,LEN(A7)-MAX(IFERROR(FIND({0,1,2,3,four,5,6,7,viii,ix},A7),""))),"")

=SUBSTITUTE(A7,RIGHT(A7,LEN(A7)-MAX(IFERROR({#VALUE!,#VALUE!,ane,#VALUE!,#VALUE!,two,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

),""))),"")

=SUBSTITUTE(A7,RIGHT(A7,LEN(A7)-MAX({"","",1,"","",2,"","","",""}

)),"")

=SUBSTITUTE(A7,Correct(A7,LEN(A7)-2),"")

=SUBSTITUTE(A7,RIGHT(A7,eight-2),"")

=SUBSTITUTE(A7,"arnold","")

=25

When y'all elevate the formula downwards to the remainder of the cells, here'due south the result you go:

Extracting a Number from Mixed Text when Number can exist Anywhere in the Text

Finally let united states consider the case where the numbers can be anywhere in the text, be it the kickoff, end or eye part of the text.

Allow'due south take a look at the following example:

Here, each cell has a mix of text and numbers, where the number appears in any part of the text. In such cases, we will need to utilize a combination of nested Excel functions to extract the numbers.

Here are the functions that nosotros are going to use this fourth dimension:

• INDIRECT – This role simply returns a reference to a range of values.
• ROW – This function returns a row number of a reference.
• MID – This part extracts a given number of characters from the centre of a string.
• TEXTJOIN – This role combines text from multiple ranges or strings using a specified delimiter between them.

Note that the formula discussed in this section will work only in Excel version 2016 onwards as information technology uses the newly introduced TEXTJOIN part. If you are using an older version of Excel, then you may consider using the VBA method instead (discussed in the adjacent department).

In this method, nosotros will substantially be using the above functions birthday to perform the post-obit sequence of tasks:

1. Break upwards the given text into an array of private characters
2. Find out and remove all characters that are not numbers
3. Combine the remaining characters into a full number

The formula that we will use to extract the numbers from cell A2 is every bit follows:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),ane)*1,""))

Permit usa break downwardly this formula to empathise it amend. We will go from the inner functions to the outer functions:

• LEN(A2)

This function finds the length of the string in prison cell A2. In our instance it returns 13.

• INDIRECT("one:"&LEN(A2))

This function just returns a reference to all the rows from row one to row 12.

• ROW(INDIRECT("1:"&LEN(A2)))

Now this function returns the row numbers of each of these rows. In other words, it just returns an assortment of numbers 1 to 12. This function thus returns the array:

{1;2;3;4;v;6;seven;8;nine;x;xi;12;13}

Note: Nosotros utilise the ROW() function instead of simply difficult-coding an array of numbers i to 12 because we want to exist able to customize this formula depending on the size of the string existence worked on. This volition ensure that the function adjusts itself when copied to another cell. ·

• MID(A2,ROW(INDIRECT("1:"&LEN(A2))),i)

Side by side the MID function extracts the graphic symbol from A2 that corresponds to each position specified in the array. In other words, it returns an array containing each character of the text in A2 as a separate element, equally follows:

{"a";"r";"n";"o";"l";"d";"1″;"4″;"iii″;"b";"fifty";"u";"e"}

• IFERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1,"")

Later this, the IFERROR function replaces all the elements of the array that are not-numeric. This is considering it checks if the array element can be multiplied past one. If the element is a number, it tin can easily be multiplied to return the same number. Just if the element is a non-numeric character, then information technology cannot exist multiplied with 1 and thus returns an error.

The IFERROR function hither specifies that if an chemical element gives an error on multiplication, the event returned volition exist bare. Equally such it returns the arrayformula:

{"";"";"";"";"";"";1;4;3;"";"";"";""}

• TEXTJOIN("",True,IFERROR(MID(A2,ROW(INDIRECT("i:"&LEN(A2))),one)*ane,""))

Finally, we can merely combine the array elements together using the TEXTJOIN role. The TEXTJOIN office here combines the string characters that remain (which are the numbers only) and ignores the empty string characters. We finally get the numeric characters in the mixed text, which is "143".

Once yous are washed entering the formula, don't forget to press CTRL+SHIFT+Enter, instead of only the render key.

In a nutshell, here's what's happening when you lot pause downward the formula:

=TEXTJOIN("",True,IFERROR(MID(A2,ROW(INDIRECT("ane:"&LEN(A2))),1)*1,""))

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:"&13)),one)*i,""))

=TEXTJOIN("",True,IFERROR(MID(A2,{one;two;3;4;five;half-dozen;seven;viii;9;ten;11;12;13},1)*i,""))

=TEXTJOIN("",TRUE,IFERROR({"a";"r";"n";"o";"fifty";"d";"one″;"iv″;"3″;"b";"l";"u";"east"}

*ane,""))

=TEXTJOIN("",True,IFERROR(MID(A2,{one;ii;iii;4;5;6;vii;8;ix;x;xi;12;13},1)*1,""))

=TEXTJOIN("",Truthful, {"";"";"";"";"";"";1;iv;3;"";"";"";""})

=143

When you drag the formula downward to the rest of the cells, hither's the result you lot get:

Using VBA to Extract Number from Mixed Text in Excel

The higher up method works well enough in extracting numbers from anywhere in a mixed text.

However, it requires one to use the TEXTJOIN function, which is non available in older Excel versions (versions before Excel 2016).

If you're on a version of Excel that does not support TEXTJOIN, then you lot can, instead, use a snippet of VBA code to get the job done.

If you have never used VBA before, don't worry. All you demand to practise is copy the code below into your VBA developer window and run information technology on your worksheet data.

Here's the code that you will need to copy:

                'Code by Steve Scott from spreadsheetplanet.com  Part ExtractNumbers(CellRef Equally String) Dim StringLength Every bit Integer StringLength = Len(CellRef) For i = 1 To StringLength    If (IsNumeric(Mid(CellRef, i, 1))) Then Effect = Event & Mid(CellRef, i, i) Next i ExtractNumbers = Outcome End Office              

The in a higher place code creates a user-defined function called ExtractNumbers() that yous can use in your worksheet to extract numbers from mixed text in whatsoever cell.

To get to your VBA programmer window, follow these steps:

1. From the Developer tab, select Visual Basic.

2. One time your VBA window opens, Click Insert->Module. That's it, y'all tin can at present outset coding.

Type or copy-paste the above lines of code into the module window. Your code is now ready to run.

At present, whenever you lot want to extract numbers from a jail cell, simply type the proper name of the function, passing the jail cell reference as a parameter. So to excerpt numbers from a cell A2, you will just need to type the part as follows in a cell:

=ExtractNumbers(A2)

Caption of the Code

At present let u.s.a. accept some fourth dimension to sympathize how this code works.

• In this code, nosotros defined a function named ExtractNumbers, that takes the string in the cell we want to work on. We assigned the name CellRef to this string.

Role ExtractNumbers(CellRef As Cord)

• We created a variable named StringLength, that will hold the length of the cord, CellRef.

Dim StringLength As Integer

StringLength = Len(CellRef)

• Adjacent, nosotros loop through each character in the cord CellRef and find out if it is a number. We use the role Mid(CellRef, i, one) to excerpt a grapheme from the cord at each iteration of the loop. Nosotros also use the IsNumeric() office to observe out if the extracted grapheme is a number. Each extracted numeric character is combined together into a string called Issue.

For i = 1 To StringLength

         If (IsNumeric(Mid(CellRef, i, ane))) Then Consequence = Outcome & Mid(CellRef, i, 1)

Next i

• This Result is then returned by the role.

ExtractNumbers = Result

Note that since the workbook now has VBA code in it, you need to salvage information technology with .xls or .xlsm extension.

You tin also choose to save this to your Personal Macro Workbook, if you think you will be needing to run this code a lot. This will allow you to run the code on any Excel workbook of yours.

Well, that was a lot!

In this tutorial, we showed you lot how to extract numbers from mixed text in excel.

Nosotros saw three cases where the numbers are situated in unlike parts of the text. We as well showed you how to utilise VBA to get the task done quickly.

Other Excel articles you may too like:

• How to Extract URL from Hyperlinks in Excel (Using VBA Formula)
• How to Contrary a Text String in Excel (Using Formula & VBA)
• How to Add Text to the Beginning or End of all Cells in Excel
• How to Remove Text subsequently a Specific Character in Excel (iii Easy Methods)
• How to Divide Accost in Excel?
• How to Excerpt Text After Space Character in Excel?
• How to Find the Concluding Space in Text String in Excel?

Source: https://spreadsheetplanet.com/extract-number-from-text-excel/

Posted by: fluddrainglevers.blogspot.com

Share this post