# Significant digits

Microsoft Excel 2003 (full product)
February 4, 2010 at 09:17:59
Specs: Windows XP, 2000
 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.

See More: Significant digits

#1
February 4, 2010 at 09:26:12
 Hi,Is this what you want,Before```1234.56 123.45 23.45 ```After```1234.56 0123.45 0023.45 ```Use Format Cells ... - Custom and "000#.00"Regards

Report •

#2
February 4, 2010 at 09:32:13
 re: "only and always 4 significant digits"What should 12,345.678 be displayed as?

Report •

#3
February 4, 2010 at 09:48:39
 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.56123.4523.45would be:1234.123.423.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.

Report •

Related Solutions

#4
February 4, 2010 at 10:10:57
 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.

Report •

#5
February 4, 2010 at 10:18:44
 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

Report •

#6
February 4, 2010 at 12:14:38
 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 Module1Enter 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 Function```These are the results - originals in column A and formulas in column B:cell B2=FiveDig(A2)` A B2 1234.56 1235.3 123.46 123.54 123.22 123.25 23.46 23.466 2.34 02.347 0.2347 0.2358 1.2455 1.2469 4.4 004.410 123456 #VALUE!Note that numbers with more than 4 integer digits return an error.Regards`

Report •

#7
February 4, 2010 at 12:34:24
 Humar, thanks very much. Extremely helpful, Sean.

Report •

#8
February 4, 2010 at 15:04:26
 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 Function```Regards

Report •

#9
February 4, 2010 at 19:52:58
 Works great, thanks again, Sean.

Report •