Map characters to their relative positions in a cell

[JohnG]

I've been using an Excel 2003 macro to loop through text cells in a
spreadsheet that is sent to me from another office. My macro loops until
column H (formatted as text) is empty; that is ="" (equal to a null string).
It's been worked just fine until recently when the macro seems to end
prematurely. It returns a normal end but clearly there are additional rows
yet to be processed.

The row that causes the macro to finish prematurely always contains many
clearly visible character comments, sometimes 5-6,000 characters in length.
My hunch is that one or more special character sequences within the text is
causing the macro to call it quits. But what characters and where are they
in the string?

Is there a way to map the actual characters that appear in col H is a
separate spreadsheet? The way I envision it, I need a spreadsheet that would
display:
Col A as having one entry for each possible ASCII character that could be in
my text.
Col B thru X would show the displacements of each occurrence of that
character from the start of text in the cell.

With that information, I figure I could look for usage of non-standard ASCII
characters, modify them to asterisks for example and then retry the macro
until I find the bad one(s).

I’ve read a number of interesting/informative posts about how to delete
troublesome characters that are known, but how can I delete these characters
if I don't know what they are or where they are?

Thanks in advance for any help.
John

 

[JLatham]

Try it this way, perhaps. Assuming your text is in Sheet1 cell A1
Over on Sheet2 at cell A1 enter this formula:
=CODE(MID(Sheet1!$A$1,COLUMN(),1))
and in A2 on sheet2, enter this formula:
=CHAR(A1)
Now, fill those formulas to the right until you start seeing #VALUE errors
(meaning you've gone past the end of the text in Sheet1!$A$1).
That will give you the numeric code for each character in the Sheet1!$A$1
cell in row 1 of Sheet2 and echo it's character in row 2 so that you can
locate the 'offending' character easily.

As added help, put this formula into Sheet2 A3
=IF(OR(A1<32,A1>128),"!!!","")
Then you'll get !!! displayed on row 3 for any really unusual characters in
the text.

 

[JohnG]

JLatham: Thanks very much for your speedy reply. You have met 100% of my
minimum daily adult requirements on the topic of my post. You folks are
great!
Warm regards,
John

 

[JLatham]

Hopefully I also satisfied some of your childish desires also. Thanks for
the feedback.
Did you find the culprit?