How do I stop Excel from converting value 0503E000 to 5.03E+02 automatically?
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?
microsoft-excel csv xls
edited Jan 7 '15 at 20:59
CharlieRB
20.6k44492
asked Jan 7 '15 at 20:44
CrashOverrideCrashOverride
111116
add a comment |
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?
microsoft-excel csv xls
edited Jan 7 '15 at 20:59
CharlieRB
20.6k44492
asked Jan 7 '15 at 20:44
CrashOverrideCrashOverride
111116
just resize the column width and it should be displayed normally
– Scorpion99
Jan 7 '15 at 20:47
Have you tried formatting the cell as text before entering the text?
– CharlieRB
Jan 7 '15 at 21:00
tried resizing and formatting as text already no go ....
– CrashOverride
Jan 7 '15 at 21:02
If you need to use the value as a hex number (i.e. you can't have it as a text string), you can nestDEC2HEXandHEX2DECstrings 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
add a comment |
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?
microsoft-excel csv xls
edited Jan 7 '15 at 20:59
CharlieRB
20.6k44492
asked Jan 7 '15 at 20:44
CrashOverrideCrashOverride
111116
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?
microsoft-excel csv xls
microsoft-excel csv xls
edited Jan 7 '15 at 20:59
CharlieRB
20.6k44492
asked Jan 7 '15 at 20:44
CrashOverrideCrashOverride
111116
edited Jan 7 '15 at 20:59
CharlieRB
20.6k44492
asked Jan 7 '15 at 20:44
CrashOverrideCrashOverride
111116
edited Jan 7 '15 at 20:59
CharlieRB
20.6k44492
edited Jan 7 '15 at 20:59
CharlieRB
20.6k44492
edited Jan 7 '15 at 20:59
CharlieRB
20.6k44492
20.6k44492
asked Jan 7 '15 at 20:44
CrashOverrideCrashOverride
111116
asked Jan 7 '15 at 20:44
CrashOverrideCrashOverride
111116
asked Jan 7 '15 at 20:44
CrashOverrideCrashOverride
111116
111116
just resize the column width and it should be displayed normally
– Scorpion99
Jan 7 '15 at 20:47
Have you tried formatting the cell as text before entering the text?
– CharlieRB
Jan 7 '15 at 21:00
tried resizing and formatting as text already no go ....
– CrashOverride
Jan 7 '15 at 21:02
If you need to use the value as a hex number (i.e. you can't have it as a text string), you can nestDEC2HEXandHEX2DECstrings 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
add a comment |
just resize the column width and it should be displayed normally
– Scorpion99
Jan 7 '15 at 20:47
Have you tried formatting the cell as text before entering the text?
– CharlieRB
Jan 7 '15 at 21:00
tried resizing and formatting as text already no go ....
– CrashOverride
Jan 7 '15 at 21:02
If you need to use the value as a hex number (i.e. you can't have it as a text string), you can nestDEC2HEXandHEX2DECstrings 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
just resize the column width and it should be displayed normally
– Scorpion99
Jan 7 '15 at 20:47
just resize the column width and it should be displayed normally
– Scorpion99
Jan 7 '15 at 20:47
Have you tried formatting the cell as text before entering the text?
– CharlieRB
Jan 7 '15 at 21:00
Have you tried formatting the cell as text before entering the text?
– CharlieRB
Jan 7 '15 at 21:00
tried resizing and formatting as text already no go ....
– CrashOverride
Jan 7 '15 at 21:02
tried resizing and formatting as text already no go ....
– CrashOverride
Jan 7 '15 at 21:02
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
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
add a comment |
6 Answers
6
active
oldest
votes
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 7 '15 at 21:21
Jason HutchinsonJason Hutchinson
24425
Yeah it works in Workbook, But unfortunately we have to use CSV for this purpose.....pity
– CrashOverride
Jan 7 '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 7 '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 7 '15 at 21:45
add a comment |
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 8 '15 at 5:58
brianarybrianary
44136
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 5 '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 at 9:59
add a comment |
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 8 '15 at 2:35
Ron RosenfeldRon Rosenfeld
2,0692611
1
This. I almost never open CSV files directly.
– pepoluan
Jan 9 '15 at 15:31
@pepoluan I don't understand what you have written.
– Ron Rosenfeld
Jan 10 '15 at 0: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
4
@pepoluan Thanks. Had I understoodthis, the rest would have been clear. Guess I'm getting old :-(
– Ron Rosenfeld
Jan 10 '15 at 15:49
|
show 2 more comments
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 8 '15 at 7:16
HardScaleHardScale
1212
1
This works when entering directly in Excel, but not with CSV file.
– SBF
Mar 5 '18 at 10:16
When opening a csv file, the apostrophe will actually be entered into the cell
– Stef
Jan 28 at 9:44
add a comment |
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.
answered Feb 27 at 15:40
Swapna nuneSwapna nune
1
add a comment |
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.
answered Feb 7 '17 at 18:34
Shmuel WolfsonShmuel Wolfson
11
add a comment |
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f861964%2fhow-do-i-stop-excel-from-converting-value-0503e000-to-5-03e02-automatically%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
6 Answers
6
active
oldest
votes
6 Answers
6
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 7 '15 at 21:21
Jason HutchinsonJason Hutchinson
24425
Yeah it works in Workbook, But unfortunately we have to use CSV for this purpose.....pity
– CrashOverride
Jan 7 '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 7 '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 7 '15 at 21:45
add a comment |
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 7 '15 at 21:21
Jason HutchinsonJason Hutchinson
24425
Yeah it works in Workbook, But unfortunately we have to use CSV for this purpose.....pity
– CrashOverride
Jan 7 '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 7 '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 7 '15 at 21:45
add a comment |
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 7 '15 at 21:21
Jason HutchinsonJason Hutchinson
24425
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 7 '15 at 21:21
Jason HutchinsonJason Hutchinson
24425
answered Jan 7 '15 at 21:21
Jason HutchinsonJason Hutchinson
24425
answered Jan 7 '15 at 21:21
Jason HutchinsonJason Hutchinson
24425
answered Jan 7 '15 at 21:21
Jason HutchinsonJason Hutchinson
24425
24425
Yeah it works in Workbook, But unfortunately we have to use CSV for this purpose.....pity
– CrashOverride
Jan 7 '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 7 '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 7 '15 at 21:45
add a comment |
Yeah it works in Workbook, But unfortunately we have to use CSV for this purpose.....pity
– CrashOverride
Jan 7 '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 7 '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 7 '15 at 21:45
Yeah it works in Workbook, But unfortunately we have to use CSV for this purpose.....pity
– CrashOverride
Jan 7 '15 at 21:29
Yeah it works in Workbook, But unfortunately we have to use CSV for this purpose.....pity
– CrashOverride
Jan 7 '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 7 '15 at 21:32
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 7 '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 7 '15 at 21:45
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 7 '15 at 21:45
add a comment |
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 8 '15 at 5:58
brianarybrianary
44136
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 5 '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 at 9:59
add a comment |
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 8 '15 at 5:58
brianarybrianary
44136
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 5 '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 at 9:59
add a comment |
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 8 '15 at 5:58
brianarybrianary
44136
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 8 '15 at 5:58
brianarybrianary
44136
answered Jan 8 '15 at 5:58
brianarybrianary
44136
answered Jan 8 '15 at 5:58
brianarybrianary
44136
answered Jan 8 '15 at 5:58
brianarybrianary
44136
44136
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 5 '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 at 9:59
add a comment |
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 5 '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 at 9:59
1
1
Great solution when pasting from SQL
– SeaSprite
Aug 23 '17 at 20:24
Great solution when pasting from SQL
– SeaSprite
Aug 23 '17 at 20:24
1
1
Also great solution when import data via "Get external data" in "Data" menu of Excel.
– Mohsen Abasi
Sep 5 '18 at 11:00
Also great solution when import data via "Get external data" in "Data" menu of Excel.
– Mohsen Abasi
Sep 5 '18 at 11:00
1
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 at 9:59
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 at 9:59
add a comment |
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 8 '15 at 2:35
Ron RosenfeldRon Rosenfeld
2,0692611
1
This. I almost never open CSV files directly.
– pepoluan
Jan 9 '15 at 15:31
@pepoluan I don't understand what you have written.
– Ron Rosenfeld
Jan 10 '15 at 0: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
4
@pepoluan Thanks. Had I understoodthis, the rest would have been clear. Guess I'm getting old :-(
– Ron Rosenfeld
Jan 10 '15 at 15:49
|
show 2 more comments
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 8 '15 at 2:35
Ron RosenfeldRon Rosenfeld
2,0692611
1
This. I almost never open CSV files directly.
– pepoluan
Jan 9 '15 at 15:31
@pepoluan I don't understand what you have written.
– Ron Rosenfeld
Jan 10 '15 at 0: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
4
@pepoluan Thanks. Had I understoodthis, the rest would have been clear. Guess I'm getting old :-(
– Ron Rosenfeld
Jan 10 '15 at 15:49
|
show 2 more comments
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 8 '15 at 2:35
Ron RosenfeldRon Rosenfeld
2,0692611
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 8 '15 at 2:35
Ron RosenfeldRon Rosenfeld
2,0692611
answered Jan 8 '15 at 2:35
Ron RosenfeldRon Rosenfeld
2,0692611
answered Jan 8 '15 at 2:35
Ron RosenfeldRon Rosenfeld
2,0692611
answered Jan 8 '15 at 2:35
Ron RosenfeldRon Rosenfeld
2,0692611
2,0692611
1
This. I almost never open CSV files directly.
– pepoluan
Jan 9 '15 at 15:31
@pepoluan I don't understand what you have written.
– Ron Rosenfeld
Jan 10 '15 at 0: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
4
@pepoluan Thanks. Had I understoodthis, the rest would have been clear. Guess I'm getting old :-(
– Ron Rosenfeld
Jan 10 '15 at 15:49
|
show 2 more comments
1
This. I almost never open CSV files directly.
– pepoluan
Jan 9 '15 at 15:31
@pepoluan I don't understand what you have written.
– Ron Rosenfeld
Jan 10 '15 at 0: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
4
@pepoluan Thanks. Had I understoodthis, the rest would have been clear. Guess I'm getting old :-(
– Ron Rosenfeld
Jan 10 '15 at 15:49
1
1
This. I almost never open CSV files directly.
– pepoluan
Jan 9 '15 at 15:31
This. I almost never open CSV files directly.
– pepoluan
Jan 9 '15 at 15:31
@pepoluan I don't understand what you have written.
– Ron Rosenfeld
Jan 10 '15 at 0:33
@pepoluan I don't understand what you have written.
– Ron Rosenfeld
Jan 10 '15 at 0:33
Which part exactly you don't understand?
– pepoluan
Jan 10 '15 at 14:55
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
@pepoluan. The relevance of what you wrote to my answer.
– Ron Rosenfeld
Jan 10 '15 at 15:29
4
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
@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
|
show 2 more comments
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 8 '15 at 7:16
HardScaleHardScale
1212
1
This works when entering directly in Excel, but not with CSV file.
– SBF
Mar 5 '18 at 10:16
When opening a csv file, the apostrophe will actually be entered into the cell
– Stef
Jan 28 at 9:44
add a comment |
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 8 '15 at 7:16
HardScaleHardScale
1212
1
This works when entering directly in Excel, but not with CSV file.
– SBF
Mar 5 '18 at 10:16
When opening a csv file, the apostrophe will actually be entered into the cell
– Stef
Jan 28 at 9:44
add a comment |
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 8 '15 at 7:16
HardScaleHardScale
1212
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 8 '15 at 7:16
HardScaleHardScale
1212
answered Jan 8 '15 at 7:16
HardScaleHardScale
1212
answered Jan 8 '15 at 7:16
HardScaleHardScale
1212
answered Jan 8 '15 at 7:16
HardScaleHardScale
1212
1212
1
This works when entering directly in Excel, but not with CSV file.
– SBF
Mar 5 '18 at 10:16
When opening a csv file, the apostrophe will actually be entered into the cell
– Stef
Jan 28 at 9:44
add a comment |
1
This works when entering directly in Excel, but not with CSV file.
– SBF
Mar 5 '18 at 10:16
When opening a csv file, the apostrophe will actually be entered into the cell
– Stef
Jan 28 at 9:44
1
1
This works when entering directly in Excel, but not with CSV file.
– SBF
Mar 5 '18 at 10:16
This works when entering directly in Excel, but not with CSV file.
– SBF
Mar 5 '18 at 10:16
When opening a csv file, the apostrophe will actually be entered into the cell
– Stef
Jan 28 at 9:44
When opening a csv file, the apostrophe will actually be entered into the cell
– Stef
Jan 28 at 9:44
add a comment |
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.
answered Feb 27 at 15:40
Swapna nuneSwapna nune
1
add a comment |
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.
answered Feb 27 at 15:40
Swapna nuneSwapna nune
1
add a comment |
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.
answered Feb 27 at 15:40
Swapna nuneSwapna nune
1
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.
answered Feb 27 at 15:40
Swapna nuneSwapna nune
1
answered Feb 27 at 15:40
Swapna nuneSwapna nune
1
answered Feb 27 at 15:40
Swapna nuneSwapna nune
1
answered Feb 27 at 15:40
Swapna nuneSwapna nune
1
1
add a comment |
add a comment |
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.
answered Feb 7 '17 at 18:34
Shmuel WolfsonShmuel Wolfson
11
add a comment |
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.
answered Feb 7 '17 at 18:34
Shmuel WolfsonShmuel Wolfson
11
add a comment |
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.
answered Feb 7 '17 at 18:34
Shmuel WolfsonShmuel Wolfson
11
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.
answered Feb 7 '17 at 18:34
Shmuel WolfsonShmuel Wolfson
11
answered Feb 7 '17 at 18:34
Shmuel WolfsonShmuel Wolfson
11
answered Feb 7 '17 at 18:34
Shmuel WolfsonShmuel Wolfson
11
answered Feb 7 '17 at 18:34
Shmuel WolfsonShmuel Wolfson
11
11
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f861964%2fhow-do-i-stop-excel-from-converting-value-0503e000-to-5-03e02-automatically%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
just resize the column width and it should be displayed normally
– Scorpion99
Jan 7 '15 at 20:47
Have you tried formatting the cell as text before entering the text?
– CharlieRB
Jan 7 '15 at 21:00
tried resizing and formatting as text already no go ....
– CrashOverride
Jan 7 '15 at 21:02
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
DEC2HEXandHEX2DECstrings 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