CAN I REPLACE NON-PRINTING CHAR(127) WITH A SYMBOL?

[QDZF]

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?

[sebastienm]

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]

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

[QDZF]

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]

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

[sebastienm]

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?

[QDZF]

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?

[QDZF]

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
>

[QDZF]

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
>

[QDZF]

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
>

[Dave Peterson]

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