Convert any number to 3 digit only

If you want 12.658 to be converted to 126
=INT(10^(2+MOD(LOG(A1), 1)))

If you want 12.658 to be converted to 127
=ROUND(10^(2+MOD(LOG(A1), 1)), 0)

Hi analystbank,

Try this UDF:

Please Login or Register  to view this content.

You take any number and multiply it by 100 or 100000. Then you concatenate a blank space on the end of it. This turns the number into a string of letters.
Then you take the left three letters in this text string and add zero to it. This turns it back into a number and your answer.

Row\Col	A	B	C
1	1	100	B1: =--LEFT(1000000000000000*A1, 3)
2	1.301	130
3	74650	746
4	12.658	126

Trust, no offence here.

What if i want to have 3 digit as above (Post #3), but want to conver numeric number after 3 (whatever length) to decimal, like

1.301 should be 130.1

74650 should be 746.50

12.658 should be 126.58, and so on.

I tried changing - NumStr As String to NumStr as Long but no success

Try
=10^(2+MOD(LOG(A1),1))

@mikerickson, Thanks . How to make UDF (User Defined function of it).

I am just trying to create of UDF using your formula example but not able to create an UDF

Try

Please Login or Register  to view this content.