Excel Character Encoding - Cell showing one thing, while text export includes new character
I have an interesting situation in Excel 365.
I one column I have phone numbers of my contacts. By looking in a cell it looks like this:
+xxx31417728
But when I export file as text file TAB delimited, the same number turns into:
+xxx?31417728
Please notice the question mark, which is not visible in a cell.
What is the issue? How to show these kind of hidden characters in Excel?
Thanks.
microsoft-excel characters
edited Jan 9 at 17:37
DavidPostill♦
105k25227260
asked Jan 9 at 17:06
gdolencgdolenc
112
add a comment |
I have an interesting situation in Excel 365.
I one column I have phone numbers of my contacts. By looking in a cell it looks like this:
+xxx31417728
But when I export file as text file TAB delimited, the same number turns into:
+xxx?31417728
Please notice the question mark, which is not visible in a cell.
What is the issue? How to show these kind of hidden characters in Excel?
Thanks.
microsoft-excel characters
edited Jan 9 at 17:37
DavidPostill♦
105k25227260
asked Jan 9 at 17:06
gdolencgdolenc
112
what happens if you convert the column to text before you export?
– Albin
Jan 9 at 17:42
add a comment |
I have an interesting situation in Excel 365.
I one column I have phone numbers of my contacts. By looking in a cell it looks like this:
+xxx31417728
But when I export file as text file TAB delimited, the same number turns into:
+xxx?31417728
Please notice the question mark, which is not visible in a cell.
What is the issue? How to show these kind of hidden characters in Excel?
Thanks.
microsoft-excel characters
edited Jan 9 at 17:37
DavidPostill♦
105k25227260
asked Jan 9 at 17:06
gdolencgdolenc
112
I have an interesting situation in Excel 365.
I one column I have phone numbers of my contacts. By looking in a cell it looks like this:
+xxx31417728
But when I export file as text file TAB delimited, the same number turns into:
+xxx?31417728
Please notice the question mark, which is not visible in a cell.
What is the issue? How to show these kind of hidden characters in Excel?
Thanks.
microsoft-excel characters
microsoft-excel characters
edited Jan 9 at 17:37
DavidPostill♦
105k25227260
asked Jan 9 at 17:06
gdolencgdolenc
112
edited Jan 9 at 17:37
DavidPostill♦
105k25227260
asked Jan 9 at 17:06
gdolencgdolenc
112
edited Jan 9 at 17:37
DavidPostill♦
105k25227260
edited Jan 9 at 17:37
DavidPostill♦
105k25227260
edited Jan 9 at 17:37
DavidPostill♦
105k25227260
105k25227260
asked Jan 9 at 17:06
gdolencgdolenc
112
asked Jan 9 at 17:06
gdolencgdolenc
112
asked Jan 9 at 17:06
gdolencgdolenc
112
112
what happens if you convert the column to text before you export?
– Albin
Jan 9 at 17:42
add a comment |
what happens if you convert the column to text before you export?
– Albin
Jan 9 at 17:42
what happens if you convert the column to text before you export?
– Albin
Jan 9 at 17:42
what happens if you convert the column to text before you export?
– Albin
Jan 9 at 17:42
add a comment |
1 Answer
1
active
oldest
votes
Excel exports text files with Windows-1252/CP-1252 character encoding.
https://stackoverflow.com/a/50653812/148844
So if you have a UTF-8 character that does not fit within the Windows-1252 character set, then it will be exported corrupted.
There is no known way to export text files correctly with Excel because it is a known bug with no UTF support, as you can see from the thousands of answers I linked to.
Some alternative ways to export is to copy & paste to Google Sheets then export a TSV or use Open Office to read the Excel file and export with UTF8.
Sometimes UTF has non-printing characters to control where diacritical marks show and join separate letters into one in other languages.
https://en.wikipedia.org/wiki/Control_character#In_Unicode
answered Jan 9 at 17:17
ChloeChloe
2,450124076
add a comment |
Your Answer
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%2f1392369%2fexcel-character-encoding-cell-showing-one-thing-while-text-export-includes-ne%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Excel exports text files with Windows-1252/CP-1252 character encoding.
https://stackoverflow.com/a/50653812/148844
So if you have a UTF-8 character that does not fit within the Windows-1252 character set, then it will be exported corrupted.
There is no known way to export text files correctly with Excel because it is a known bug with no UTF support, as you can see from the thousands of answers I linked to.
Some alternative ways to export is to copy & paste to Google Sheets then export a TSV or use Open Office to read the Excel file and export with UTF8.
Sometimes UTF has non-printing characters to control where diacritical marks show and join separate letters into one in other languages.
https://en.wikipedia.org/wiki/Control_character#In_Unicode
answered Jan 9 at 17:17
ChloeChloe
2,450124076
add a comment |
Excel exports text files with Windows-1252/CP-1252 character encoding.
https://stackoverflow.com/a/50653812/148844
So if you have a UTF-8 character that does not fit within the Windows-1252 character set, then it will be exported corrupted.
There is no known way to export text files correctly with Excel because it is a known bug with no UTF support, as you can see from the thousands of answers I linked to.
Some alternative ways to export is to copy & paste to Google Sheets then export a TSV or use Open Office to read the Excel file and export with UTF8.
Sometimes UTF has non-printing characters to control where diacritical marks show and join separate letters into one in other languages.
https://en.wikipedia.org/wiki/Control_character#In_Unicode
answered Jan 9 at 17:17
ChloeChloe
2,450124076
add a comment |
Excel exports text files with Windows-1252/CP-1252 character encoding.
https://stackoverflow.com/a/50653812/148844
So if you have a UTF-8 character that does not fit within the Windows-1252 character set, then it will be exported corrupted.
There is no known way to export text files correctly with Excel because it is a known bug with no UTF support, as you can see from the thousands of answers I linked to.
Some alternative ways to export is to copy & paste to Google Sheets then export a TSV or use Open Office to read the Excel file and export with UTF8.
Sometimes UTF has non-printing characters to control where diacritical marks show and join separate letters into one in other languages.
https://en.wikipedia.org/wiki/Control_character#In_Unicode
answered Jan 9 at 17:17
ChloeChloe
2,450124076
Excel exports text files with Windows-1252/CP-1252 character encoding.
https://stackoverflow.com/a/50653812/148844
So if you have a UTF-8 character that does not fit within the Windows-1252 character set, then it will be exported corrupted.
There is no known way to export text files correctly with Excel because it is a known bug with no UTF support, as you can see from the thousands of answers I linked to.
Some alternative ways to export is to copy & paste to Google Sheets then export a TSV or use Open Office to read the Excel file and export with UTF8.
Sometimes UTF has non-printing characters to control where diacritical marks show and join separate letters into one in other languages.
https://en.wikipedia.org/wiki/Control_character#In_Unicode
answered Jan 9 at 17:17
ChloeChloe
2,450124076
answered Jan 9 at 17:17
ChloeChloe
2,450124076
answered Jan 9 at 17:17
ChloeChloe
2,450124076
answered Jan 9 at 17:17
ChloeChloe
2,450124076
2,450124076
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%2f1392369%2fexcel-character-encoding-cell-showing-one-thing-while-text-export-includes-ne%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
what happens if you convert the column to text before you export?
– Albin
Jan 9 at 17:42