Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Stop scientific notation in Excel 2010 or 2007

Posted on 2013-06-28
5
Medium Priority
?
804 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

715 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