Extract Numeric Value from alfa numeric value

[RAMU]

Dear All,
In excel cells there are alfa numeric values like "KEMAJA, KALUA - 680 311, MANDAN BEACH 6453." I wish to extract only the six digit numeric value in another cell by a formula. pls mind , six digit in case of without any space (like 680311) but seven digit with a space (like 680 311) BUT My desired result will be 680311, the formula will never consider the other numeric value like "6453", I mean in case of seven digit that is including the space. I was trying lot but not successful
Need help
Thanks in advance
Regards
RAMU

 

[kvsrinivasamurthy]

Try. A2=input text
ARRAY formula

=SMALL(IF(ISNUMBER(MID(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""))-6)),6)+0),MID(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""))-6)),6)+0,""),1)

Normal formula, if you have aggregate function.

=AGGREGATE(15,6,(MID(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""))-6)),6)+0)/ISNUMBER(MID(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""))-6)),6)+0),1)

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

 

[RAMU]

Can you use a UDF? This should work.

Function Extract6Num(r As String)
r = Replace(r, " ", "")
With CreateObject("vbscript.regexp")
    .Pattern = "\d{6}"
    If .test(r) Then
        Extract6Num = .Execute(r)(0)
    Else
        Extract6Num = "Not Found"
    End If
End With
End Function

Honestly not aware of it, pls help sir

 

[RAMU]

Try. A2=input text
ARRAY formula

=SMALL(IF(ISNUMBER(MID(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""))-6)),6)+0),MID(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""))-6)),6)+0,""),1)

Normal formula, if you have aggregate function.

=AGGREGATE(15,6,(MID(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""))-6)),6)+0)/ISNUMBER(MID(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""))-6)),6)+0),1)

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

both the formulas are not working, error is coming either #NUM! or #VALUE!

 

[RAMU]

Can you use a UDF? This should work.

Function Extract6Num(r As String)
r = Replace(r, " ", "")
With CreateObject("vbscript.regexp")
    .Pattern = "\d{6}"
    If .test(r) Then
        Extract6Num = .Execute(r)(0)
    Else
        Extract6Num = "Not Found"
    End If
End With
End Function

Sorry, got it, 95% perfect now, let me check all, shall give my feedback

 

[kvsrinivasamurthy]

Both are working for me. Upload file with 5-6 examples in some website and give link here.

 

[kvsrinivasamurthy]

1st formula needs ARRAY entry. Ctrl+Shift+Enter.
2nd formula works in version 2010 and on Words.
Still not working upload a sample file in some website and give link here.