How to extract specific number from a single cell

[nitemath2]

HI,

I am using Excel 2016.

Need help to extract specific number/value from a single cell.

The text is in Column "A" and result is in Column "B". Sample sheet attached.


Thanks
Nitesh

[AliGW]

There's no common logic here, so I don't think it's going to be easy. Have you covered all eventualities with this sample?

[nitemath2]

Yes Ali and before posting I have tried the solutions available on google but didn't get the result which I am expecting.

[AliGW]

That's not what I meant at all!

Does your sample file show all possible types of number that need extracting, or are there other scenarios?

[nitemath2]

Yes, all possible types of number that need extracting

[Pete_UK]

This formula caters for the 4 specific examples that you included in your sample file:

=IF(COUNTIF(A2,"*#*"),MID(A2,FIND("CSI#",A2),12),IF(COUNTIF(A2,"* No.*"),MID(SUBSTITUTE(A2," No.","#"),FIND("CSI#",SUBSTITUTE(A2," No.","#")),12),IF(ISNUMBER(--RIGHT(A2,8)),RIGHT(A2,8),"")))

You can put it in C2 and copy down, so you can compare directly.

If you have other variations in your data you need to point these out to us, as ALI has already asked, as the formula may not work fully.

Hope this helps.

Pete

[nitemath2]

Thanks Pete , its worked

[Pete_UK]

Glad to hear it - thanks for the rep.

Pete