• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1301
  • Last Modified:

When I import a text file into Excel I get a square with a question mark inside

I import a lot of external data into Excel, which using Excel 2003 SP3 on Win XP SP3 worked fine. I did have to change the file origin from Windows ANSI to 65001:Unicode (UTF-8) to remove any unwanted characters.
When I import using Excel 2003 SP3 on Windows 7 and select 65001:Unicode (UTF-8) as the file origin I see black squares with question marks in.
Any ideas what's causing this?
Thanks
0
marmaduke0
Asked:
marmaduke0
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello marmaduke,

these are characters that Excel can not display with the current character set. You can try to figure out what the Ansi code for the character is by copying the single character into a cell, e.g. A1, and then put =code(A1) into B1 or another cell.

To get rid of the character, you can use Find and Replace. Copy the character, hit Ctrl-H, copy the character into the "Find What" field and enter nothing (or a space, depending on your circumstances) into the "Replace with" field, then hit the "replace all" button.

cheers, teylyn
0
 
marmaduke0Author Commented:
Thanks for the quick response, I found that the ANSI code is 63 by using your suggestion.
I don't really want to do a search and replace if I can help it as I have different users who use the same macros to import data in different OS's.
0
 
CluskittCommented:
This happens because it's a different unicode character set than you have. For example, arabic characters would usually return code 63.
However, programatically, it would be very hard to get rid of this. Code 63, if you use ANSI, it a question mark. The problem lies in the fact that there is usually a length of the string var that isn't visible to the developer and is handled internally by VBA.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
apresenceCommented:
marmaduke0, please describe your ideal solution to this issue so that we can make sure our comments meet your expectations.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
You could incorporate the Find and Replace in the macro that imports the content.
0
 
marmaduke0Author Commented:
Thanks all, I will use Teylyn's recommendation to incorporate the find and replace into the macro. There will be a little leg work but once it's done it's done.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now