data import

[bglass]

I am trying to import data from a data acquisition program
into excel. The import import options in excel are "fixed
width" fileds or "Delimited ". I can import in the "Fixed
Width" option but I also import what I assume is
a "delimter" symbol. The symbol is a small square that
then shows up in each field and has to be manually
removed. If I try to import in the "delimited" mode there
are several options to designate the "delimiter" symbol(ie
comma, tab etc) but not the "small square". I have tried
each of the available "delimiter" symbols but none of them
work. Does anyone know what the "small square is or a way
to accomplish this import process.

 

[hgrove]

bglass wrote...
...

The symbol is a small square that then shows up in each field
and has to be manually removed. . . . Does anyone know what
the "small square is or a way to accomplish this import process.

Excel uses the small square to display several characters it can't o
won't display otherwise. Decimal ASCII characters 1-31, 127, an
various codes over 128. It could be any of these.

If these squares were in the same positions in each line, you could us
fixed width, select these 'columns' as separate fields, then opt not t
include them. If they're in varying positions, then you'd need to use
formula approach. If the first square in the first record (say, A2) wer
at character position 6, and you wanted to replace all instances of i
with commas, use the following formula in a cell to the right of i
(say, G2).

G2:
=SUBSTITUTE(A2,MID(A2,6,1),",")

Then fill G2 down so there's a formula in col G for each cell in col
containing this data. Select the col G range, Edit > Copy, select th
col A range, Edit > Paste Special as Values. Then delete the col
cells containing these formulas

 

[Guest]

The symbol is in the same position in every field and I
have been using the import options to create a new column
for each symbol and select to not import that column. But
now I need to import hundreds of columns of data and
manually creating and deleting the symbol columns is a
problem. I think I can do it with a macro but if there
were a better way I'd like to figure it out. My other
option is to try to modify the data acquisition program to
use a comma as the delimiter so Excel could deal with it.
Thanks for the information.