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,176 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

Expert Comment

by:Ingeborg Hawighorst
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Best Excel  formula for  this scenario 2 36
Locking down a pc/laptop 11 29
VLOOKUP 6 17
Excel. How to get via VBA the last used row in a named dynamic range 10 36
By default the complete memory dump option is disabled in windows . If we want to enable the complete memory dump for a diagnostic purpose, we have a solution for it. here we are using the registry method to enable this.
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 create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

770 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