Solved

Stop scientific notation in Excel 2010 or 2007

Posted on 2013-06-28
5
789 Views
Last Modified: 2013-07-02
I have a gl code 123-45-67-9012-3456, when I take out the dashes it converts to scientific notation is their anyway around it ?

1.999E+17 how do I remove the dashes to show  123456790123456 ?
0
Comment
Question by:cobp
5 Comments
 

Author Comment

by:cobp
ID: 39285694
Also I do not want a manual process I would like to make the change in a macro.

Thanks !
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 39285724
Right click on the cell
Format Cells
On the opened window - click 'Number' tab

Under there, click number, then set decimal places to '0' and click 'OK'

For multiple cells, select the range, right click on top of the range, then select 'Format Cells' and follow the above steps.
0
 
LVL 4

Expert Comment

by:LCCSAM
ID: 39285741
In VBA to change a selections format the code looks like this:

Sub FormatChange()
    Selection.NumberFormat = "0.00"
End Sub
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39285782
Excel can display 15 digits as a number. But if your GL code might have 16 or more digits, the least significant digits will be lost--so you need to store it as text rather than a number.

Here is a macro that caters to that possibility. It works on every cell in a selection, and both applies a Text format and removes the hyphens. GL codes that contain other non-numeric characters will be left as-is.
Sub GLcodeToNumber()
Dim cel As Range, rg As Range
Dim s As String
On Error Resume Next
Set rg = Selection
On Error GoTo 0
If Not rg Is Nothing Then
    Set rg = Intersect(rg, rg.Worksheet.UsedRange)
    For Each cel In rg.Cells
        If cel.Value <> "" Then
            s = Replace(cel.Value, "-", "")
            If IsNumeric(s) Then
                cel.NumberFormat = "@"
                cel.Value = s
            End If
        End If
    Next
End If
End Sub

Open in new window

Brad
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 39286804
Or, define a different format for those cells, which includes the dashes
thus, when you enter
123456790123456
the cell will actually display
123-45-67-9012-3456
[I didn't include an 8 in the series, as there wasn't one in your example either!]

To do this, set the cell format to Custom, and type the following into the box:
000-00-00-0000-0000

Apply this where you want with the format painter.
2 points to note though - it will only work with numbers of exactly that length, and also, it won't work if there are any letters in your codes.  

As well as saving your users time on data entry, it makes the cells easier to manipulate - ie building serieses, autofill, etc.

If your codes WILL include letters, some workarounds are here:
http://excelribbon.tips.net/T009394_Using_a_Custom_Format_to_Add_Dashes.html
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My experience with Windows 10 over a one year period and suggestions for smooth operation
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now