how do i extract a number from a cell with no spaces?

M

Mulvaney

I have a column of data with the following entries: N0, AN67, AN134P, BL202,
AN67P, etc.

How can i extract just the numbers from these cells?

Thank you.
 
B

bj

if there are never more than 6 or less than 4 character
=value(IF(ISNUMBER(VALUE(LEFT(A11,1))),left(A11,1),"")&IF(ISNUMBER(VALUE(mid((A11,2,1)))),mid(A11,2,1),"")&IF(ISNUMBER(VALUE(mid((A11,3,1)))),mid(A11,3,1),"")&IF(ISNUMBER(VALUE(mid((A11,4,1)))),mid(A11,4,1),"")&IF(len(A11)>4,if(ISNUMBER(VALUE(mid((A11,5,1)))),mid(A11,5,1),""),"")&IF(len(A11)>5,if(ISNUMBER(VALUE(mid((A11,6,1)))),mid(A11,6,1),""),""))
 
B

bj

or if your characters always start with two alpha nd may or may not end in
one alpha
=if
(isnumber(value(right(a11,len(a11)-2))),value(right(a11,len(a11)-2))),value(left(right(a11,len(a11)-2)),len(a11)-3))),
 
T

TemplateBuilder

Hi Mulvaney,

I ran into the same problem and solved it making a simple VBA Function.

----------------------------------------------------------------------
Public Function NumPart(c) As String

Dim i As Integer
Dim Tekst As String

'Templatebuilder
'Returning numeric value from string'

Tekst = ""
For i = 1 To Len(c)
If InStr(1, "0123456789", Mid(c, i, 1), vbTextCompare) > 0 Then
Tekst = Tekst + Mid(c, i, 1)
Next i

NumPart = Tekst

End Function
-------------------------------------------------

Now you can use the function in your worksheet. (example =numpart(A1))

Probably there will be other (maybe better) solutions. But this one is
working for me.

gr. TemplateBuilder
 
Top