# Strip mumbers from text field.

#### wonsonboy

##### New Member
Can anyone help me with an excel formula to strip numbers from text as per tht examples below.I am only after the leading numbers not the end ones.So any numbers left of the MID text

1NBL1A to 1

286EBL1 to 286

6006SBL2 to 6006

Im new here so apologies in advance if I haver posted this incorrectly.

Thanks

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### Sixthsense

##### Active Member
Looking for formula or VBA solution?

#### wonsonboy

##### New Member
just a simple formula would be great...if you can nut it out

#### Sixthsense

##### Active Member
In A1 Cell
 6006SBL2

<tbody>
</tbody>

In B1 Cell - Array formula - Requires Ctrl+Shift+Enter

=IFERROR(VALUE(LEFT(A3,MATCH(0,--ISNUMBER(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)+0),0)-1)),"")

#### wonsonboy

##### New Member
I couldnt get it to work but I will persist before I get back to you.....thanks so far

#### Sixthsense

##### Active Member
It is an Array formula so you have to press Ctrl+Shift+Enter instead of normal enter

#### Michael M

##### Well-known Member
And if you want a custom function
Paste this in a Standard Module
Code:
``````Function Lon(ByVal txt As String) As String
Dim X As Long     'Code base by Rick Rothstein (MVP - Excel)
For X = 1 To Len(txt)
If Mid(txt, X, 1) Like "*[!0-9]*" Then Mid(txt, X, 1) = Chr(1) ' Leave only numbers
Next
Lon = Replace(txt, Chr(1), "")
End Function``````

Excel 2007
AB
11NBL1A to 1111
2
3286EBL1 to 2862861286
4
56006SBL2 to 6006600626006
Sheet2
Cell Formulas
RangeFormula
B1=lon(A1)
B3=lon(A3)
B5=lon(A5)

#### Sixthsense

##### Active Member
Or

Code:
``````Function GetLeftNumber(vTxt As Variant) As String
Dim i As Integer, sRes As String

For i = 1 To Len(vTxt)
If Not IsNumeric(Mid(vTxt, i, 1)) Then Exit For
sRes = sRes & Mid(vTxt, i, 1)
Next

GetLeftNumber = sRes

End Function``````

#### XOR LX

##### Well-known Member
And a non-array formula solution:

=-LOOKUP(0,-(LEFT(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))&"**0"))

Regards

Replies
3
Views
475
Replies
11
Views
1K
Replies
4
Views
5K
Replies
5
Views
2K
Replies
1
Views
643

1,195,989
Messages
6,012,718
Members
441,722
Latest member
tpaman1975

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back