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

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

.