How do I stop Excel from converting value 0503E000 to 5.03E+02 automatically?

Question

Excel converting value 0503E000 to 5.03E+02 in any cell automatically in CSV I tried to convert it to text number and general but after i save it and reopen it again it goes back to 5.03E+02

How do I stop this from happening?

just resize the column width and it should be displayed normally — Scorpion99, Jan 07 '15 at 20:47
Have you tried formatting the cell as text *before* entering the text? — CharlieRB, Jan 07 '15 at 21:00
If you need to use the value as a hex number (i.e. you can't have it as a text string), you can nest `DEC2HEX` and `HEX2DEC` strings for the original number (e.g. `=DEC2HEX(HEX2DEC("0503E000"))` for your example). It may not help if you need to manipulate the csv file without it containing formulae, but it's another option besides formatting as text. — Myles, Mar 22 '18 at 17:46

score 36 · Answer 1 · answered Jan 08 '15 at 05:58

36

If you can convert or control the CSV format, you can force a column to parse as text in Excel by wrapping it in double quotes and prepending an equals sign.

Excel will carelessly discard precision in this format:

Value,0503E000,1234123412341234

Or even this format:

Value,"0503E000","1234123412341234"

Converting it to:

Value  |  5.03E+02  |  1234123412341230

However, adding the equals sign forces Excel to begrudgingly preserve your data:

Value,="0503E000",="1234123412341234"

…which opens as:

Value  |  0503E000  |  1234123412341234

answered Jan 08 '15 at 05:58

brianary

1,321
1
10
8

1

Great solution when pasting from SQL – SeaSprite Aug 23 '17 at 20:24
1

Also great solution when import data via "Get external data" in "Data" menu of Excel. – Mohsen Abasi Sep 05 '18 at 11:00
1

Great solution. The only (small) drawback is that you'll end up with formulaes instead of values. So just copy everything and then **paste values**, which will lead to a somewhat smaller Excel file. – Stef Jan 28 '19 at 09:59
Only if the file is in read only mode, if user edit the file and save. He will again loose the actual value – prabhatojha Sep 24 '20 at 11:34
This answer might work, but thinking about this, I think its ideocracy: you have to format your CSVs in a way especially for Excel, having to adapt all other applications that need to use the CSV. Furthermore, when you export xlsx to csv, you have to manually edit the csv and change all of this in order to import it again... – JHBonarius Jan 21 '21 at 13:39
@JHBonarius Yeah, Excel's default behavior is pretty frustrating, especially for a program that's primarily intended to work with numbers. It seems trouble enough to disqualify Excel from working with CSV files when I have the choice. My guess is that it's some backward compatibility thing from a time when higher precision was prohibitively expensive, and Office just can't break away from it. – brianary Feb 16 '21 at 17:53

score 17 · Accepted Answer · answered Jan 07 '15 at 21:21

17

I think the key issue here is that you are using a CSV, which does not have the cell type embedded in it. Excel automatically tries to interpret the cell as having a scientific number which is why you are seeing 5.03E+02 instead of 0503E000.

Try creating an Excel workbook and formatting all of the cells as text, and then pasting in the data. I tried it in Excel 2013 and it worked.

answered Jan 07 '15 at 21:21

Jason Hutchinson

284
2
5

Yeah it works in Workbook, But unfortunately we have to use CSV for this purpose.....pity – CrashOverride Jan 07 '15 at 21:29
So don't use Excel to view the contents, or run a macro on the cell, to convert it back or use slightly different syntax if you used 0x before the value I don't belive it would convert – Ramhound Jan 07 '15 at 21:32
I think we may have to lay this off ... because if we open it in another editor like notepad or notepad ++ it has the correct value....so I think cell has the correct value just displaying it wierdly in scientific notation.... – CrashOverride Jan 07 '15 at 21:45

score 11 · Answer 3 · answered Jan 08 '15 at 02:35

11

Instead of Opening" the CSV file in Excel, select to Import the file (in Excel 2007-2010 you would navigate to the Data Ribbon / Get External Data / From Text). Not sure about 2013 but there should be something similar. When you do that, the Text Import wizard will open and afford you the opportunity to format the column containing that Value as Text, before Excel (not so helpfully) changes it to a numeric value.

answered Jan 08 '15 at 02:35

Ron Rosenfeld

8,198
3
13
17

1

This. I almost never open CSV files directly. – pepoluan Jan 09 '15 at 15:31
@pepoluan I don't understand what you have written. – Ron Rosenfeld Jan 10 '15 at 00:33
Which part exactly you don't understand? – pepoluan Jan 10 '15 at 14:55
@pepoluan. The relevance of what you wrote to my answer. – Ron Rosenfeld Jan 10 '15 at 15:29
I wrote "This", which meant I agree/support your answer. Then I wrote "I never open CSV files directly", referring to your explanation on using 'Import' instead of 'Open'. – pepoluan Jan 10 '15 at 15:42
4

@pepoluan Thanks. Had I understood `this`, the rest would have been clear. Guess I'm getting old :-( – Ron Rosenfeld Jan 10 '15 at 15:49
1

I was hopeful that using "this" as a complete sentence would have died a quick death, but 4 years later, it's as strong as ever. – variant Nov 28 '18 at 14:53

score 2 · Answer 4 · answered Jan 08 '15 at 07:16

2

Start the cell with an apostrophe to force text interpretation:

'0502E000

Basically, this tells Excel not to parse the field as a number. Because there is an 'E' in the field, it looks like a number to Excel. The apostrophe won't actually be entered into the cell:

[a1] '0502E000
[b1] =hex2dec(a1)

Cell 'b1' will display 84074496.

answered Jan 08 '15 at 07:16

HardScale

121
3

4

This works when entering directly in Excel, but not with CSV file. – SBF Mar 05 '18 at 10:16
1

When opening a csv file, the apostrophe will actually be entered into the cell – Stef Jan 28 '19 at 09:44

score 0 · Answer 5 · answered Feb 27 '19 at 15:40

Try to load the file (data fetched from DB table) through DATA option in MS excel then simply while loading just select “Do not detect data types” under “Data Type Detection” then load , this will keep the as it as format of data and loads excel, no need for any column conversions.

score 0 · Answer 6 · answered Jul 26 '19 at 20:41

0

Paste the data into a google spreadsheet. Select the column ==> Format ==> Number => Custom format => type the number of digits to which you want format to happen (ex: 10 digits = 0000000000)

answered Jul 26 '19 at 20:41

Kiran

1
1

I would like to suggest better [Edit] you post,, since need to add some examples!! – Rajesh Sinha Jul 27 '19 at 07:23

score 0 · Answer 7 · edited Jan 25 '23 at 05:51

Found the Best Solution to this Problem! Here is how -

Use the Feature - Get Data from Text/CSV

(can be found under Data Tab> Get Data> From File> From Text/CSV)

Get Data from Text:

Then import the CSV from which you want to load data. Loading CSV

In the pop-up, turn off Auto Data Type Detection.

And the finally click load Load Button

Voila! Your Data is imported to the sheet, without rounding off the numbers! Successfully Imported Long Number Data without Rounding:

If you get a different pop-up menu (old looking) Steps will be slightly different, and you will need to manually click on column and set data type from General to Text to achieve similar result!

Thanks to Markus Meyer for turning my SS Links to Embedded Images! ( apparently to embed image one needs at least 10 reputation, while I had none LoL ) — Anmoldeep Singh, Jan 25 '23 at 05:57

score -1 · Answer 8 · answered Feb 07 '17 at 18:34

With the default cell formatting, numbers with a zero in front and long numbers will automatically get modified by Excel. For example, if you type 012, it will be changed to 12.

If you format the cells as Text, then any number that you type in the cell will stay as is, and will not be modified by Excel.

However, if you paste a number from another source into a cell that is formatted as Text, and in the source there is any sort of formatting, the formatting of the cell will change from Text to General, and the number manipulation will come back.

The solution that I found was to do a paste special, and paste the number as Text. Then the cell remains with its Text formatting, and the number is not modified at all.

How do I stop Excel from converting value 0503E000 to 5.03E+02 automatically?

8 Answers8

Linked

Related