Removing Scientific Notation in Excel cell

[sjha]

Ladies and Gentleman,

I have tried to serach this forum but could not find what I need. I have bunch of 22 digits numbers, e.g. 9612019086568871420292. When I copy them into Excel cells they become like 9.61202E+21. And, this is the problem. I would like to see them as 9612019086568871420292. I have tried all possible way to format as General, Numbers, Texts, etc but none worked. Also did Paste Special>Value, etc. none worked. Any suggestion? Thank you in advance.

 

[dringstrom]

The goal of course is to preserve the entire # and not allow Excel to lose anything in translation by converting a # to scientific notation, correct? In that case, highlight the cell/column with your large #, right-click to format the cell(s), and choose the custom format from the Number tab. In the Type box type in one lb symbol (#) and that's it. Now save as .csv and the full # is preserved. On pulling the file back into Excel, reformat the cell/column back to custom/#, accordingly.

Formatting the numbers after import doesn't solve the problem, because by that point Excel has rounded the numbers off during the conversion to scientific notation. The better approach is to import the .CSV file as a text file, and tell Excel that the column of large numbers is text.

 

[PACE-1]

Formatting the numbers after import doesn't solve the problem, because by that point Excel has rounded the numbers off during the conversion to scientific notation. The better approach is to import the .CSV file as a text file, and tell Excel that the column of large numbers is text.

That's no entirely true, the original number is still visible when you click on the cell, so the rounding is done before displaying the value. Since this page is on the top results on Google, I wanted to post my solution.

My problem was that I had a large file originating from CSV, already prepared for import and noticed some phone numbers were wrong because of scientific notation. Reimporting wasn't an option (already done lots Search+replace etc.).

SpaceCadet101's trick would work except that I had both numbers and text in the column and formatting all as # led to strange results (e.g. div by 0 when there was a slash or missing parts).

So I created a formular to convert the s.n. values and leave everything else. I created a new column next to the column with the problem values, then inserted the following formula (A1 is the source cell in this example):

=IF(ISNUMBER(A1);TEXT(A1;"#");T(A1))

The formular checks if the content is a number, if true it is formatted with #, else it is treated as text value. Then set the format of the source column to text and copy the values back and delete the extra column.
Hope this helps!

br,
Ben

 

[expatCanuck]

Greetings -

New to the forum.
Yes, I know this thread's old, but I have an alternate approach, and I'd welcome feedback.

I need to create a text .psv (Pipe Separated Value) file from Excel data that's coming in with ~15 significant digits.

No matter what formatting I try, using VBA to create a string (using either Str or CStr) generates the undesired scientific notation, which I can't use.

So, I wrote a bit of code to parse that notation:

' parse the contents to remove any scientific notation'
dim ePosn As Integer  ' the position of 'E' in the scientific notation
dim cellContents As String
dim expStr As String   ' the two digit exponent, as a string
 
ePosn = InStr(cellContents, "E")
If ePosn = 0 Then 'no scientfic notation
  rowContents = rowContents + cellContents + "|"
Else ' Excel has added scientific notation, so parse it
  expStr = Mid(cellContents, Len(cellContents) - 1, 2) ' find out what the exponent is
  Select Case expStr
      ' depending on the exponent, prepend with requisite number of zeros;
      ' append the first digit, and then the remaining digits before notation
    Case "01"
      numStr = "0." + Mid(cellContents, 1, 1) + Mid(cellContents, 3, Len(cellContents) - 6)
 
    '... Case 02, Case 03 ...
 
    Case "04"
      numStr = "0.000" + Mid(cellContents, 1, 1) + Mid(cellContents, 3, Len(cellContents) - 6)
    Case Else
      mBoxRet = MsgBox("Error parsing notation.")
  End Select
  rowContents = rowContents + numStr + "|"
End If

Feedback welcome, particularly as regards things to watch out for.

 

[gmendel]

Format as a number with no commas or decimal points. It won't affect the text and if will make the scientific fields text if you copy and paste special values

 

[martinezjosei]

I tried this and it works for my case:

fomat cells > number > increase the number of decimal places to your number's max decimal places.

Yes, I know this is an old thread but the info is still valid.

 

[psahay]

SIMPLE TRICK - GUARANTEED TO WORK

Well a pretty long thread still struggling for solution

Select all your cells -> Format cells -> Special -> Zip code -> OK

It will also work in the following cases when 0001 gets copied over as 1.

Once you format as mentioned above, you will get back 0001 - Enjoy

Thanks,

 

[Legacy 161456]

Just wanted to add my solution:

Selecting the column with the scientific notations and then using the "Text to Columns" process on the "Data" tab. On the third step you can change the "Column data format" from General to Text. Finish the process and the numbers should be a full number string.

 

[shambhushankar]

I know its old one, but this can be done.


Data --> Text to Column --> (fixed width) Next --> Next --> (Text) Finish