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

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.

if there are never more than 6 or less than 4 characters
=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(A1 1,4,1),"")&IF(len(A11)4,if(ISNUMBER(VALUE(mid((A1 1,5,1)))),mid(A11,5,1),""),"")&IF(len(A11)5,if(IS NUMBER(VALUE(mid((A11,6,1)))),mid(A11,6,1),""),"") )

"Mulvaney" wrote:

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.

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))),

"bj" wrote:

if there are never more than 6 or less than 4 characters
=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(A1 1,4,1),"")&IF(len(A11)4,if(ISNUMBER(VALUE(mid((A1 1,5,1)))),mid(A11,5,1),""),"")&IF(len(A11)5,if(IS NUMBER(VALUE(mid((A11,6,1)))),mid(A11,6,1),""),"") )

"Mulvaney" wrote:

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.

ASAP Utilities, a free Add-in available at www.asap-utilities.com has a
feature that will strip the alpha characters out of a string......

Vaya con Dios,
Chuck, CABGx3



"Mulvaney" wrote:

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.

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