How to convert the values into 3 digits?

[Eric]

Does anyone have any suggestions on how to convert the values into 3 digits
based on following conditions?
There is a number 14 in cell A1, then it converts into 140 in 3 digit format.
There is a number 1900 in cell A1, then it converts into 190 in 3 digit
format.
There is a number 8999 in cell A1, then it converts into 900 in 3 digit
format.
There is a number 1001 in cell A1, then it converts into 100 in 3 digit
format.

Does anyone have any suggestions on how to convert it?
Thanks in advance for any suggesitons
Eric

 

[JackPoland]

Function ThreeDigits(Source As String) As String
Select Case Len(Source)
Case 1
qq = Source & "00"
Case 2
qq = Source & "0"
Case 3
qq = Source
Case Is > 3
qq = Left(Source, 3)
End Select
ThreeDigits = qq
End Function

But there is a problem with 8999 which should be 900, like you said.
I do not know why 1001 = 100 and 8999=900 but not 899...

 

[joeu2004]

Does anyone have any suggestions on how to convert the values
into 3 digits based on following conditions?

One way:

=SIGN(A1)*SUBSTITUTE(LEFT(TEXT(ABS(A1),"0.00E+0"),4),".","")

If the number is alway non-negative, that can be simplified:

=--SUBSTITUTE(LEFT(TEXT(A1,"0.00E+0"),4),".","")



----- original posting -----

 

[joeu2004]

=SIGN(A1)*SUBSTITUTE(LEFT(TEXT(ABS(A1),"0.00E+0"),4),".","")

If the number is alway non-negative, that can be simplified:
=--SUBSTITUTE(LEFT(TEXT(A1,"0.00E+0"),4),".","")

Alternatively, respectively:

=sign(A1)*int(100*left(text(abs(A1),"0.00E+0"),4))

=int(100*left(text(A1,"0.00E+0"),4))

INT() eliminates artifacts of the binary representation. You might get away
without it. But for example, compare the following:

=mod(100*left(text(1111,"0.00E+0"),4), 1)

=mod(int(100*left(text(1111,"0.00E+0"),4)), 1)

 

[Ron Rosenfeld]

Does anyone have any suggestions on how to convert the values into 3 digits
based on following conditions?
There is a number 14 in cell A1, then it converts into 140 in 3 digit format.
There is a number 1900 in cell A1, then it converts into 190 in 3 digit
format.
There is a number 8999 in cell A1, then it converts into 900 in 3 digit
format.
There is a number 1001 in cell A1, then it converts into 100 in 3 digit
format.

Does anyone have any suggestions on how to convert it?
Thanks in advance for any suggesitons
Eric

If the values will always be positive:

=ROUND(LEFT(A1&"000",4)/10,0)

If there might be negative values, perhaps:

=SIGN(A1)*ROUND(LEFT(ABS(A1)&"000",4)/10,0)


--ron

 

[Mc/]

I thought this would be easier; place the following formula in a work column,
and copy it down as far as needed. You will want to format that column to 0
decimal places.,
Assuming the original value list is in Column A, then in an available work
column.

=IF(A1<=100,A1*10,A1/10)

You could Copy/Paste Special>Values over the original column, if needed.