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?

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?

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

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?

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

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?

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?

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

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

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

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