Categories

# Extract Number From Cells That Mix of Text and Numbers

Sometimes you need to extract the numeric portion from a cell that contains both text and numbers. For example, if you have a cell that contains “Product ID: 12345”, you may want to get only the number “12345”. There are different ways to do this, we will demonstrate some methods and examples for doing this task.

1. Extract Numbers from the Left Side of the Text
2. Extract Numbers from the Right Side of the Text
3. 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 functions`LEFT` 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)+0``

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

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))+0``

This 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 Function``````

Close 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`.