?
Solved

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

Posted on 2010-09-01
6
Medium Priority
?
1,270 Views
Last Modified: 2012-05-10
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
Comment
Question by:marmaduke0
6 Comments
 
LVL 50
ID: 33575408
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
 

Author Comment

by:marmaduke0
ID: 33575444
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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33575473
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 6

Expert Comment

by:apresence
ID: 33575475
marmaduke0, please describe your ideal solution to this issue so that we can make sure our comments meet your expectations.
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 33575478
You could incorporate the Find and Replace in the macro that imports the content.
0
 

Author Comment

by:marmaduke0
ID: 33575492
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question