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

 

[rollis13]

As per the example shown, assuming that the data is in cell A1 and that the number you need is between a minus sign and a comma, you can use this formula in B1:

=IFERROR(SUBSTITUTE(MID(A2,FIND("-",A2,1)+1,FIND(",",A2,FIND("-",A2,1))-FIND("-",A2,1)-1)," ",""),"Not found")

 

[RAMU]

As per the example shown, assuming that the data is in cell A1 and that the number you need is between a minus sign and a comma, you can use this formula in B1:

=IFERROR(SUBSTITUTE(MID(A2,FIND("-",A2,1)+1,FIND(",",A2,FIND("-",A2,1))-FIND("-",A2,1)-1)," ",""),"Not found")

It works fine friend for this value but it does not mean everywhere "-" or "," or "." will be present. there may be something else also like #, @, or anything else. But whichever will be the alfa numeric value, the formula will extract only the six digit like 680311, whether it presents like "680311" or "680 311"
The value can be 50 words or more or less.

 

[Scott Huish]

What version of Excel do you have?

 

[RAMU]

As per the example shown, assuming that the data is in cell A1 and that the number you need is between a minus sign and a comma, you can use this formula in B1:

=IFERROR(SUBSTITUTE(MID(A2,FIND("-",A2,1)+1,FIND(",",A2,FIND("-",A2,1))-FIND("-",A2,1)-1)," ",""),"Not found")

See the formula is giving wrong result for this value "Plot No.B-46, STICE, Musalgaon" (Result coming 46), but it should not come, it should be "Not Found", same happens for this value also "10TH FLOOR, SAKAR-II," (Result coming II) but it is wrong, the formula will work only for these two type numeric values only (123456 or 123 456), alfa & special charcaters or spaces can be anything, similarlarly in case of "BAKCHIRUPAM - 102 005." it is coming "Not Found", but it should be 102005. Hope you understand my issue.

 

[Scott Huish]

=MAX(IFERROR(ABS(MID(SUBSTITUTE(A1," ",""),ROW(INDIRECT("1:255")),6)),0))

Format cell as
0;;

or

0;;"Not Found"

 

[RAMU]

=MAX(IFERROR(ABS(MID(SUBSTITUTE(A1," ",""),ROW(INDIRECT("1:255")),6)),0))

Format cell as
0;;

or

0;;"Not Found"

Almost ok, but till now got two errors against two types of values
1) ( LAWAMM METERS GROUP ) GAT NO.261 - Result coming 261, should be 0
2) 5-5-47/3 LANE BESIDE DIGVA CHAMBURS RAJA - Result showing 17292, also should be 0, but can't understand why 17292 is coming

 

[RAMU]

What version of Excel do you have?

2021

 

[Scott Huish]

Might have to rethink this.

17292 is coming because May 5, 1947 (5-5-47) is stored internally in Excel as 17292.

 

[Scott Huish]

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