Just the last 3 digits

[Greggo]

Hi, I have a list of numbers and I want to remove the decimal if there is one and any zeros before any numbers and report the first 3 numbers only e.g. 0.00012345 would become 123

 

[kvsrinivasamurthy]

If the value is 0.0007800 what should be output.
Try in B2, ARRAY formula.

=INT(VALUE(LEFT(MOD(A2,1)*10^LEN(MOD(A2,1)),3)))

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

 

[Peter_SSs]

Welcome to the MrExcel board!

One example is not much to go on. Could we have 5-10 that show what sort of variety there might be and provide the expected results for those samples?
The examples might then show if numbers like these are possible
0.02
123456.35698
2.365
-3.259
5698
0.30000
etc

 

[Alex Blakenburg]

Give this a try:
(in row 2)

=LEFT(VALUE(SUBSTITUTE(A2,".","")),3)

 

[Sanjeev1976]

You can try modified version of Alex's formula to get correct results for negative values :

=LEFT(ABS(VALUE(SUBSTITUTE(A4,".",""))),3)

 

[Tom.Jones]

It is enough:

=LEFT(ABS(SUBSTITUTE(A2,".","")),3)
or
=LEFT(--SUBSTITUTE(A2,".",""),3)

 

[kvsrinivasamurthy]

For negative numbers
=INT(VALUE(LEFT(MOD(ABS(A2),1)*10^LEN(MOD(A2,1)),3)))