| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CAN I REPLACE NON-PRINTING CHAR(127) WITH A SYMBOL?
HAVE A FILE WITH A STRING OF TEXT BROKEN BY CHAR(127). I WOULD LIKE TO BREAK
THAT STRING AT EACH OCCURENCE OF CHAR(127). EASIEST WAY I COULD THINK OF WAS TO REPLACE IT WITH A SYMBOL AND THEN USE TEXT TO COLUMNS. IS THERE A BETTER WAY THAN THIS? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CAN I REPLACE NON-PRINTING CHAR(127) WITH A SYMBOL?
Hi,
No need to Replace; you can directly use the 'Text To Column' feature. As the delimiter, choose 'Other' and type in the char 127 by typing: ALT + 0127, ie type 0127 WHILE pressing the ALT. -- Regards, Sébastien <http://www.ondemandanalysis.com "QDZF" wrote: HAVE A FILE WITH A STRING OF TEXT BROKEN BY CHAR(127). I WOULD LIKE TO BREAK THAT STRING AT EACH OCCURENCE OF CHAR(127). EASIEST WAY I COULD THINK OF WAS TO REPLACE IT WITH A SYMBOL AND THEN USE TEXT TO COLUMNS. IS THERE A BETTER WAY THAN THIS? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CAN I REPLACE NON-PRINTING CHAR(127) WITH A SYMBOL?
When I did this, only the first section broke into the new column. The rest
did not appear at all. There's 6 of these breaks in the string. How do I get the others to break into new columns? "sebastienm" wrote: Hi, No need to Replace; you can directly use the 'Text To Column' feature. As the delimiter, choose 'Other' and type in the char 127 by typing: ALT + 0127, ie type 0127 WHILE pressing the ALT. -- Regards, Sébastien <http://www.ondemandanalysis.com "QDZF" wrote: HAVE A FILE WITH A STRING OF TEXT BROKEN BY CHAR(127). I WOULD LIKE TO BREAK THAT STRING AT EACH OCCURENCE OF CHAR(127). EASIEST WAY I COULD THINK OF WAS TO REPLACE IT WITH A SYMBOL AND THEN USE TEXT TO COLUMNS. IS THERE A BETTER WAY THAN THIS? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CAN I REPLACE NON-PRINTING CHAR(127) WITH A SYMBOL?
Are you positive that all those characters are char(127)'s?
Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.htm QDZF wrote: When I did this, only the first section broke into the new column. The rest did not appear at all. There's 6 of these breaks in the string. How do I get the others to break into new columns? "sebastienm" wrote: Hi, No need to Replace; you can directly use the 'Text To Column' feature. As the delimiter, choose 'Other' and type in the char 127 by typing: ALT + 0127, ie type 0127 WHILE pressing the ALT. -- Regards, Sébastien <http://www.ondemandanalysis.com "QDZF" wrote: HAVE A FILE WITH A STRING OF TEXT BROKEN BY CHAR(127). I WOULD LIKE TO BREAK THAT STRING AT EACH OCCURENCE OF CHAR(127). EASIEST WAY I COULD THINK OF WAS TO REPLACE IT WITH A SYMBOL AND THEN USE TEXT TO COLUMNS. IS THERE A BETTER WAY THAN THIS? -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CAN I REPLACE NON-PRINTING CHAR(127) WITH A SYMBOL?
I believe so. Just playing around with it, I used the CLEAN function and
replaced all 127 with 7. It changed everything and made one long text string. I'll use the addin and see what I get. Thanks. "Dave Peterson" wrote: Are you positive that all those characters are char(127)'s? Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.htm QDZF wrote: When I did this, only the first section broke into the new column. The rest did not appear at all. There's 6 of these breaks in the string. How do I get the others to break into new columns? "sebastienm" wrote: Hi, No need to Replace; you can directly use the 'Text To Column' feature. As the delimiter, choose 'Other' and type in the char 127 by typing: ALT + 0127, ie type 0127 WHILE pressing the ALT. -- Regards, Sébastien <http://www.ondemandanalysis.com "QDZF" wrote: HAVE A FILE WITH A STRING OF TEXT BROKEN BY CHAR(127). I WOULD LIKE TO BREAK THAT STRING AT EACH OCCURENCE OF CHAR(127). EASIEST WAY I COULD THINK OF WAS TO REPLACE IT WITH A SYMBOL AND THEN USE TEXT TO COLUMNS. IS THERE A BETTER WAY THAN THIS? -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CAN I REPLACE NON-PRINTING CHAR(127) WITH A SYMBOL?
Great addin! It says it is not char(127). When I view the line through the
addin, it shows two characters. One is 013 and the next is 010. I tried 'Text to Columns' using Alt + every variation of these codes and nothing worked. Now that we know what we are dealing with, is there something else I should be doing? Thanks. "Dave Peterson" wrote: Are you positive that all those characters are char(127)'s? Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.htm QDZF wrote: When I did this, only the first section broke into the new column. The rest did not appear at all. There's 6 of these breaks in the string. How do I get the others to break into new columns? "sebastienm" wrote: Hi, No need to Replace; you can directly use the 'Text To Column' feature. As the delimiter, choose 'Other' and type in the char 127 by typing: ALT + 0127, ie type 0127 WHILE pressing the ALT. -- Regards, Sébastien <http://www.ondemandanalysis.com "QDZF" wrote: HAVE A FILE WITH A STRING OF TEXT BROKEN BY CHAR(127). I WOULD LIKE TO BREAK THAT STRING AT EACH OCCURENCE OF CHAR(127). EASIEST WAY I COULD THINK OF WAS TO REPLACE IT WITH A SYMBOL AND THEN USE TEXT TO COLUMNS. IS THERE A BETTER WAY THAN THIS? -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CAN I REPLACE NON-PRINTING CHAR(127) WITH A SYMBOL?
The char(10) can be changed via edit|replace (or you can use data|text to
columns and specify the delimiter as alt-0010). But I've never had any luck doing the same thing with char(13). You can use a helper column filled with formulas like: =substitute(a1,char(13)&char(10),"|") Drag it down the column. Convert to values and use data|text to columns against this helper column (and use | as the delimiter) QDZF wrote: Great addin! It says it is not char(127). When I view the line through the addin, it shows two characters. One is 013 and the next is 010. I tried 'Text to Columns' using Alt + every variation of these codes and nothing worked. Now that we know what we are dealing with, is there something else I should be doing? Thanks. "Dave Peterson" wrote: Are you positive that all those characters are char(127)'s? Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.htm QDZF wrote: When I did this, only the first section broke into the new column. The rest did not appear at all. There's 6 of these breaks in the string. How do I get the others to break into new columns? "sebastienm" wrote: Hi, No need to Replace; you can directly use the 'Text To Column' feature. As the delimiter, choose 'Other' and type in the char 127 by typing: ALT + 0127, ie type 0127 WHILE pressing the ALT. -- Regards, Sébastien <http://www.ondemandanalysis.com "QDZF" wrote: HAVE A FILE WITH A STRING OF TEXT BROKEN BY CHAR(127). I WOULD LIKE TO BREAK THAT STRING AT EACH OCCURENCE OF CHAR(127). EASIEST WAY I COULD THINK OF WAS TO REPLACE IT WITH A SYMBOL AND THEN USE TEXT TO COLUMNS. IS THERE A BETTER WAY THAN THIS? -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CAN I REPLACE NON-PRINTING CHAR(127) WITH A SYMBOL?
I GOT IT! I used the CLEAN and SUBSTITUTE functions to replace 013 with 127.
I then went into Text to Columns and split by char(127) and it worked. Thanks for all the help. "Dave Peterson" wrote: Are you positive that all those characters are char(127)'s? Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.htm QDZF wrote: When I did this, only the first section broke into the new column. The rest did not appear at all. There's 6 of these breaks in the string. How do I get the others to break into new columns? "sebastienm" wrote: Hi, No need to Replace; you can directly use the 'Text To Column' feature. As the delimiter, choose 'Other' and type in the char 127 by typing: ALT + 0127, ie type 0127 WHILE pressing the ALT. -- Regards, Sébastien <http://www.ondemandanalysis.com "QDZF" wrote: HAVE A FILE WITH A STRING OF TEXT BROKEN BY CHAR(127). I WOULD LIKE TO BREAK THAT STRING AT EACH OCCURENCE OF CHAR(127). EASIEST WAY I COULD THINK OF WAS TO REPLACE IT WITH A SYMBOL AND THEN USE TEXT TO COLUMNS. IS THERE A BETTER WAY THAN THIS? -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CAN I REPLACE NON-PRINTING CHAR(127) WITH A SYMBOL?
hmm it works for me.
So say in A1 you have a*b*c*d with * representing the chr(127) - select A1 and goto to menu Data Text-To-Column - in the dialog Text-To-Column, step 1/3, you choose Delimited - step2/3: check 'Other', enter ALT+0127, Uncheck 'Treat COnsec Delimiters, set TextQualifier to None. - step 3/3, make sure each column is NOT set to 'Do not Import column' by clicking on each column. Do you get the result: 1. a b*c*d with 'a' in one column and b*c*d in the next one In this case i would check if other * in the string are really chr(127) with the formula: = ( char(127) = mid(A1,4,1) ) where 4 is the position of a * in the string 2. or do you just get a 'a' in one column and the rest has diseapeared, in which case i would check Step3/3 above. -- Regards, Sébastien <http://www.ondemandanalysis.com "QDZF" wrote: When I did this, only the first section broke into the new column. The rest did not appear at all. There's 6 of these breaks in the string. How do I get the others to break into new columns? "sebastienm" wrote: Hi, No need to Replace; you can directly use the 'Text To Column' feature. As the delimiter, choose 'Other' and type in the char 127 by typing: ALT + 0127, ie type 0127 WHILE pressing the ALT. -- Regards, Sébastien <http://www.ondemandanalysis.com "QDZF" wrote: HAVE A FILE WITH A STRING OF TEXT BROKEN BY CHAR(127). I WOULD LIKE TO BREAK THAT STRING AT EACH OCCURENCE OF CHAR(127). EASIEST WAY I COULD THINK OF WAS TO REPLACE IT WITH A SYMBOL AND THEN USE TEXT TO COLUMNS. IS THERE A BETTER WAY THAN THIS? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CAN I REPLACE NON-PRINTING CHAR(127) WITH A SYMBOL?
In your example, my results are number 2. Steps 1, 2 and 3 were identical to
my process. Although, when I get to step 3 only one column appears. I don't have the ability to choose other columns and set them as "General" or "Text". "sebastienm" wrote: hmm it works for me. So say in A1 you have a*b*c*d with * representing the chr(127) - select A1 and goto to menu Data Text-To-Column - in the dialog Text-To-Column, step 1/3, you choose Delimited - step2/3: check 'Other', enter ALT+0127, Uncheck 'Treat COnsec Delimiters, set TextQualifier to None. - step 3/3, make sure each column is NOT set to 'Do not Import column' by clicking on each column. Do you get the result: 1. a b*c*d with 'a' in one column and b*c*d in the next one In this case i would check if other * in the string are really chr(127) with the formula: = ( char(127) = mid(A1,4,1) ) where 4 is the position of a * in the string 2. or do you just get a 'a' in one column and the rest has diseapeared, in which case i would check Step3/3 above. -- Regards, Sébastien <http://www.ondemandanalysis.com "QDZF" wrote: When I did this, only the first section broke into the new column. The rest did not appear at all. There's 6 of these breaks in the string. How do I get the others to break into new columns? "sebastienm" wrote: Hi, No need to Replace; you can directly use the 'Text To Column' feature. As the delimiter, choose 'Other' and type in the char 127 by typing: ALT + 0127, ie type 0127 WHILE pressing the ALT. -- Regards, Sébastien <http://www.ondemandanalysis.com "QDZF" wrote: HAVE A FILE WITH A STRING OF TEXT BROKEN BY CHAR(127). I WOULD LIKE TO BREAK THAT STRING AT EACH OCCURENCE OF CHAR(127). EASIEST WAY I COULD THINK OF WAS TO REPLACE IT WITH A SYMBOL AND THEN USE TEXT TO COLUMNS. IS THERE A BETTER WAY THAN THIS? |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CAN I REPLACE NON-PRINTING CHAR(127) WITH A SYMBOL?
I could use Data|Text to columns|Delimited
and specify Other as the character. And I hit and held the alt-key while typing 0127 on the numeric keypad to get that character. It'll save one step. QDZF wrote: HAVE A FILE WITH A STRING OF TEXT BROKEN BY CHAR(127). I WOULD LIKE TO BREAK THAT STRING AT EACH OCCURENCE OF CHAR(127). EASIEST WAY I COULD THINK OF WAS TO REPLACE IT WITH A SYMBOL AND THEN USE TEXT TO COLUMNS. IS THERE A BETTER WAY THAN THIS? -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| removing char(13) with find/replace | Excel Discussion (Misc queries) | |||
| How to substitute a comma with a newline char using Replace. | Excel Discussion (Misc queries) | |||
| Using replace box to change a character into a CR (Alt-Enter char) | Excel Discussion (Misc queries) | |||
| How do I find/replace a "<" symbol? | Excel Discussion (Misc queries) | |||
| Replace Symbol "Code(63)" | Excel Worksheet Functions | |||
Hybrid Mode
