Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Map characters to their relative positions in a cell
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). Ive 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Map characters to their relative positions in a cell
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,A1128),"!!!","") Then you'll get !!! displayed on row 3 for any really unusual characters in the text. "JohnG" wrote: 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). Ive 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Map characters to their relative positions in a cell
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" wrote: 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,A1128),"!!!","") Then you'll get !!! displayed on row 3 for any really unusual characters in the text. "JohnG" wrote: 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). Ive 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Map characters to their relative positions in a cell
Hopefully I also satisfied some of your childish desires also. Thanks for
the feedback. Did you find the culprit? "JohnG" wrote: 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" wrote: 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,A1128),"!!!","") Then you'll get !!! displayed on row 3 for any really unusual characters in the text. "JohnG" wrote: 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). Ive 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lock Relative Cell Positions? | Excel Discussion (Misc queries) | |||
Copying charts and their data with relative cell positions | Charts and Charting in Excel | |||
Relative positions from a button | Excel Programming | |||
Testing positions in a cell | Excel Discussion (Misc queries) | |||
How do I sum using relative cell positions? | Excel Worksheet Functions |