Excel Formulas to Split the Numbers from Units of Measure ~ Easily!!

Sometimes you encounter data that mixes units directly with numbers. Unfortunately, numbers in this format are considered to be text inside of Excel, and you won’t be able to perform any math operations on such values. So here we will show the formulas to split the number from a unit of measure in Excel. Let’s step into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

General Formula:

Use the below formula to split the number from a unit of measure in Excel.

*=MAX(ISNUMBER(VALUE(MID(A1,{1,2,3,4,5,6,7,8,9},1)))*{1,2,3,4,5,6,7,8,9})+1 *

Syntax Explanations:

MAX – In Excel, the MAX function will return the largest numeric value from the range of input values.
ISNUMBER – The Excel ISNUMBER function is used to return TRUE when a cell contains a number, and FALSE if there will not be a number.
VALUE – The VALUE Function in Excel is used to convert text into a numeric value.
MID – It is used to extract the number (starting from the left side) or characters from the given string. Read more on the MID Function.
A1 – It represents the input value.
Plus operator (+) – This symbol is used to add the values.
Comma symbol (,) – It is a separator that helps to separate a list of values.
Parenthesis () – The main purpose of this symbol is to group the elements.

Example:

Let’s consider the below example image.

Here, we will enter the input text strings in Column B.
Now we are going to split the number into units of measure.

Select any cell and type the above-given formula.

Finally, press the ** ENTER** key, you can get the result as shown below.

Wind-Up:

So yeah guys this is how you can split a unit of measure in Excel. Hope you like this article. Let me know if you have any doubts regarding this article or any other article on this site.

Thank you so much for visiting *Geek Excel!! **If you want to learn more helpful formulas, check out Excel Formulas *!! **