To extract specific numbers from a range

[sparkerk1]

Dear team,

I want to extract specific number from below , the below text when copied in excel comes in 6 rows, the criteria is to extract the INR FARE 44500 (only numbers required) from second line and Ignore the "USD" line, also sometime the second line comes as first and the first comes as second so it should be able extract only the number inr the "INR" only , this formula should revolve in the first 2 line only

Eg: if first Lines contains USD its should skip that part and jump on the next line and find the "INR" and give out the result "44500"
also if The "INR" line comes in the first row its should extract the and same and skip the second line.



USD 688.00 07NOV17BOM QR X/DOH QR X/SAO G3 RIO688.00
INR 44500 NUC688.00END ROE1.000000
INR 10834YQ XT INR 2787K3 INR 850YM INR 258IN INR 154
INR 388YR WO INR 614G4 INR 33PZ INR 584BR
INR 5280XT
INR 61002

 

[Rick Rothstein]

Does this formula do what you want...

=0+IF(LEFT(A1,3)="INR",MID(A1,FIND(" ",A1),FIND(" ",A1,5)-4),MID(A2,FIND(" ",A2),FIND(" ",A2,5)-4))

 

[sparkerk1]

Does this formula do what you want...

=0+IF(LEFT(A1,3)="INR",MID(A1,FIND(" ",A1),FIND(" ",A1,5)-4),MID(A2,FIND(" ",A2),FIND(" ",A2,5)-4))

Dear Sir,

Tried your above formula but getting below error

 

[shift-del]

Sparkerk1,

try this:

[COLOR=#000000][FONT=Arial]=MID([/FONT][/COLOR][COLOR=#008000][FONT=Arial]INDEX(A1:A2,[COLOR=#0000FF]MMULT({1,2},--[COLOR=#FF0000]ISNUMBER([COLOR=#804000]SEARCH("INR",A1:A2)[/COLOR])[/COLOR])[/COLOR])[/FONT][/COLOR][COLOR=#000000][FONT=Arial],5,5)[/FONT][/COLOR]

 

[sparkerk1]

Sparkerk1,

try this:

[COLOR=#000000][FONT=Arial]=MID([/FONT][/COLOR][COLOR=#008000][FONT=Arial]INDEX(A1:A2,[COLOR=#0000FF]MMULT({1,2},--[COLOR=#FF0000]ISNUMBER([COLOR=#804000]SEARCH("INR",A1:A2)[/COLOR])[/COLOR])[/COLOR])[/FONT][/COLOR][COLOR=#000000][FONT=Arial],5,5)[/FONT][/COLOR]

Dear Sir,

Thx above method worked fine only I had to change value of the last Digit to 8 to make the entire digits appear.

=MID(INDEX(A1:A2,MMULT({1,2},--ISNUMBER(SEARCH("INR",A1:A2)))),5,5)