Microsoft Excel 2003 (full product)

Is there any way to get Excel to format numbers in block of cells so that there are 4 significant digits, regardless of the decimal point location? The default is to specify the number of digits after the decimal. However, this means that the number of characters/numbers in the sell varies.

I'm trying to make sure there are only and always 4 significant digits regardless of decimal point location.

Thanks very much, Sean.

Hi, Is this what you want,

Before

1234.56 123.45 23.45After

1234.56 0123.45 0023.45Use Format Cells ... - Custom and "000#.00"

Regards

re: " "only and always4 significant digitsWhat should 12,345.678 be displayed as?

1,234.5678 should be displayed as:

1234.

(12,345.678 is a larger # than any in the data)The other #'s mentioned, before they are:

1234.56

123.45

23.45

would be:

1234.

123.4

23.45Its for a Fortran input file, so the result must be 5 digits, one digit always being a decimal point somewhere in the number.

I'm thinking there may be a way to copy and paste, so that the new block of numbers is stored as only 5 characters each, rather than just being displayed as such.

So I guess the problem is not to ensure 4 significant figures, rather its to ensure a number in each cell of 5 characters, 1 being the decimal point.

Thanks very much, Sean.

The 000#. format will work actually, it will just round anything after the decimal. Losing some data accuracy. If anyone knows a better way please post. Thanks again, Sean.

Hi, You can make 5-character text strings:

1234. 123.4 23.45 and 2.34 becomes: 02.34

Using this formula:

=IF(LEN(F10)>5,LEFT(F10,5),IF(LEN(F10)=5,F10,REPT("0",5-LEN(F10))&F10))

where F10 contains the original number.but there is no rounding ...

Regards

Hi, I created a custom function that returns five characters including a "."

and it rounds the values.I haven't tested it extensively, so if you want to use it, it may require some tweaking.

Open the Visual Basic window (Alt + f11)

Find the name of your workbook - in the form: VBAProject(MyWorkbook.xls)

Right click and select Insert and insert a standard module (not class module)

Under Modules double-click on Module1

Enter the following code:Option Explicit Function FiveDig(Numb As Range) As Variant Dim dblDig As Double Dim strDig As String Dim intDPt As Integer On Error GoTo ErrHnd dblDig = Numb.Value strDig = Format(dblDig, "#.00#") 'find position of decimal point intDPt = InStr(1, strDig, ".") 'test for too long an integer If intDPt > 5 Then FiveDig = CVErr(xlErrValue) Exit Function End If Select Case intDPt Case Is = 5 FiveDig = Left(CStr(Round(dblDig, 0)), 5) & "." Case Is = 4 FiveDig = Left(CStr(Round(dblDig, 1)), 5) If Len(FiveDig) < 5 Then FiveDig = String(5 - Len(FiveDig), "0") & FiveDig End If Case Is = 3 FiveDig = Left(CStr(Round(dblDig, 2)), 5) If Len(FiveDig) < 5 Then FiveDig = String(5 - Len(FiveDig), "0") & FiveDig End If Case Is = 2 FiveDig = Left(CStr(Round(dblDig, 3)), 5) If Len(FiveDig) < 5 Then FiveDig = String(5 - Len(FiveDig), "0") & FiveDig End If Case Is = 1 If Int(dblDig) = 0 Then FiveDig = Left(CStr(Round(dblDig, 3)), 5) Else FiveDig = Left(CStr(Round(dblDig, 4)), 5) End If If Len(FiveDig) < 5 Then FiveDig = String(5 - Len(FiveDig), "0") & FiveDig End If End Select Exit Function 'error handler ErrHnd: Err.Clear FiveDig = CVErr(xlErrNA) End FunctionThese are the results - originals in column A and formulas in column B:

cell B2=FiveDig(A2)A B

2 1234.56 1235.

3 123.46 123.5

4 123.22 123.2

5 23.46 23.46

6 2.34 02.34

7 0.2347 0.235

8 1.2455 1.246

9 4.4 004.4

10 123456 #VALUE!

Note that numbers with more than 4 integer digits return an error.Regards

Humar, thanks very much. Extremely helpful, Sean.

Hi Sean, You're welcome! I noticed that input numbers without a decimal point are not correctly handled, so here is a revised version of the FiveDig() function.

(The existing conversions I showed remain the same, but numbers such as 12 are converted to 0012.)Option Explicit Function FiveDig(Numb As Range) As Variant Dim dblDig As Double Dim strDig As String Dim intDPt As Integer On Error GoTo ErrHnd 'get base values of number as number and text string dblDig = Numb.Value strDig = Format(dblDig, "#.00#") 'find position of "." intDPt = InStr(1, strDig, ".") 'test if no decimal part of number If Int(dblDig) = dblDig Then intDPt = 0 strDig = CStr(dblDig) End If 'test for too long an integer If intDPt > 5 Then FiveDig = CVErr(xlErrValue) Exit Function End If 'handle conversion based on position of decimal point (intDPt) Select Case intDPt Case Is = 5 FiveDig = Left(CStr(Round(dblDig, 0)), 5) & "." Case Is = 4 FiveDig = Left(CStr(Round(dblDig, 1)), 5) If Len(FiveDig) < 5 Then FiveDig = String(5 - Len(FiveDig), "0") & FiveDig End If Case Is = 3 FiveDig = Left(CStr(Round(dblDig, 2)), 5) If Len(FiveDig) < 5 Then FiveDig = String(5 - Len(FiveDig), "0") & FiveDig End If Case Is = 2 FiveDig = Left(CStr(Round(dblDig, 3)), 5) If Len(FiveDig) < 5 Then FiveDig = String(5 - Len(FiveDig), "0") & FiveDig End If Case Is = 1 If Int(dblDig) = 0 Then FiveDig = Left(CStr(Round(dblDig, 3)), 5) Else FiveDig = Left(CStr(Round(dblDig, 4)), 5) End If If Len(FiveDig) < 5 Then FiveDig = String(5 - Len(FiveDig), "0") & FiveDig End If Case Is = 0 If dblDig > 9999 Then FiveDig = CVErr(xlErrValue) Else FiveDig = CStr(dblDig) & "." End If If Len(FiveDig) < 5 Then FiveDig = String(5 - Len(FiveDig), "0") & FiveDig End If End Select Exit Function 'error handler ErrHnd: Err.Clear FiveDig = CVErr(xlErrNA) End FunctionRegards

Works great, thanks again, Sean.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History