Solved

Stop scientific notation in Excel 2010 or 2007

Posted on 2013-06-28
5
791 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

803 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