Stop scientific notation in Excel 2010 or 2007

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 ?
cobpAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
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
 
cobpAuthor Commented:
Also I do not want a manual process I would like to make the change in a macro.

Thanks !
0
 
Shanan212Commented:
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
 
LCCSAMCommented:
In VBA to change a selections format the code looks like this:

Sub FormatChange()
    Selection.NumberFormat = "0.00"
End Sub
0
 
Danny ChildIT ManagerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.