Strip mumbers from text field.

[wonsonboy]

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

 

[Sixthsense]

Looking for formula or VBA solution?

 

[wonsonboy]

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

 

[Sixthsense]

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]

ADVERTISEMENT

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

 

[Sixthsense]

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

 

[Michael M]

ADVERTISEMENT

And if you want a custom function
Paste this in a Standard Module

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
	A	B
1	1NBL1A to 1	111
2
3	286EBL1 to 286	2861286
4
5	6006SBL2 to 6006	600626006
Sheet2

Cell Formulas
Range	Formula
B1	=lon(A1)
B3	=lon(A3)
B5	=lon(A5)

 

[Sixthsense]

Or

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]

And a non-array formula solution:

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

Regards