Solved

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

Posted on 2010-09-01
6
1,209 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
To Patch or not to Patch? That is the question!

Don't get caught out like thousands of others around the world in the recent Ransomware Fiasco!
Discuss..
- Why it's not a good idea to wait before Patching
- Sensible approaches to Patching discussed
- Add your feedback, comments and suggestions

 
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 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

When you try to extract and to view the contents of a Microsoft Update Standalone Package (MSU) for Windows Vista, you cannot extract the files from the MSU. Here we are going to explain how to extract those hotfix details without using any third pa…
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…
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

737 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