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,227 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

689 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