- Extract Numbers from the Left Side of the Text
- Extract Numbers from the Right Side of the Text
- Separate Numbers from Mixed Text and Numbers
Extract Numbers from the Left Side of the String
Assume you have a range of cells starting from A1. Each cell contains a dollar amount, followed by a space, then a person’s name. Using the following formula, which combines the two TEXT functionsLEFT and FIND, you can extract this dollar value:
=LEFT(A4,FIND(" ",A4)-1)If cell A1 contains the data $99.70 for XYZ, the formula’s result will be $99.70. However, this result will be returned as text rather than as a true numeric value; therefore, by default, it will be left-aligned within the cell.
You can modify the formula so that the result is no longer a text value, but rather, a true numeric value, by adding 0 to the value:
=LEFT(A4,FIND(" ",A4)-1)+0This will force the dollar value returned to become a true number; therefore, it will be right-aligned by default. All you need to do now is format the cell accordingly.
Extract Numbers from the Right Side of the String
We have a cell that contains “Product ID: 12345”, we want to get only the number “12345”.
In the next example, we have a range of cells starting from A1. Each cell contains a text string, followed by a colon:, then a number (product id). Using the following formula, which combines the three TEXT functions LEN, RIGHT and FIND, you can extract this dollar value:
=RIGHT(A1,LEN(A1)-FIND(":",A1))+0This formula is used to extract the text after a colon (:) in a cell. It works by finding the position of the colon in the cell A using the FIND function, and then subtract it from the total length of the cell using the LEN function. This gives the number of characters after the colon, which is then used as an argument for the RIGHT function to return that portion of the text. Adding 0 to the returns a numeric value.
Separate Numbers from Mixed Text and Numbers
Another problem that can arise regarding text and numbers occurs when you mix text and numbers in the same cell, with no real way of extracting the numeric portion only. In this case, you can use a custom function to extract the numeric portion from a text string.
To create this custom function, press Alt+F11 (or Option+F11), select Insert » Module, and enter the following code:
Function ExtractNumber(sText As String)
Dim lCount As Long
Dim l As Long
Dim lNum As String
For lCount = Len(sText) To 1 Step -1
If IsNumeric(Mid(sText, lCount, 1)) Then
l = l + 1
lNum = Mid(sText, lCount, 1) & lNum
End If
If l = 1 Then lNum = CInt(Mid(lNum, 1, 1))
Next lCount
ExtractNumber = CLng(lNum)
End FunctionClose the VBA window and enter the formula =ExtractNumber("a123b45c") in a cell, the ExtractNumber formula will return the extracted number 12345.
To see this formula in the Excel window, click Formulas > Insert Function. The function will appear under User Defined in the Insert function dialog box (Shift+F3):
In the following figure, column A contains a mixture of text and numbers, column B contains the result of using the ExtractNumber user-defined function, and column C shows how the formula looks in column B.
