Forum Discussion

cchai's avatar
cchai
Copper Contributor
Jun 02, 2023

The annoying excel scientific notation

I have a column which is the supplier item code, it contains text or a long barcode (so it is not pure long "number" barcode), 

e.g.

ABC123456

1000000000000000

 

The data are from some source that I cannot control, I already changed the format of the column to text, but it still showing the scientific notation (e.g. 1E+15), so how can I completely stop this annoying scientific notation in text format?

 

And I think it is a bug! I already specify it is a "text" (a TEXT!), not a number, how come excel will still showing the scientific notation (but when I double click into the cell, it shows the original value in digits, so the value is here not missing, **bleep** it!)

excel

3 Replies

  • Cangkir's avatar
    Cangkir
    Brass Contributor
    Jun 02, 2023

    cchai 

    Assuming the formula shows original number, maybe this way:
    Use macro to add apostrophe (') in front of the numbers.
    Something like this:

    Sub to_Text()
Dim c As Range
 For Each c In Range("A2", Cells(Rows.Count, "A").End(xlUp))
    c = "'" & c.Formula
 Next
End Sub

    Before

    After

  • Cangkir's avatar
    Cangkir
    Brass Contributor
    Jun 02, 2023
    In the formula bar, do they show as original number? for example: 1000000000000000 instead of 1E+15.
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 02, 2023

    cchai 

    #1 The data are from some source that I cannot control What kind of Source is it (Csv/text file,...)

    #2 What version of Excel do you run?

    #3 On which platform/OS (Windows, Mac...)?

     

    Depending on the above you might have a solution with Get & Transform aka Power Query:

    In attached file query ExternalSource simulates an external data source, query ImportedData just transform the data types as desired (Text for Barcode)

     

    + Feel free to UpVote https://feedbackportal.microsoft.com/feedback/idea/45e412a2-513b-ed11-9db0-000d3a4e3f39

Resources