Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Stop scientific notation in Excel 2010 or 2007

Posted on 2013-06-28
5
Medium Priority
?
805 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 1500 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

885 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