Tab delimited has \240 codes

[jimktrains]

I saved an Excel worksheet as Tab Delimited text and when I open the text file on unix, I see a lot of \240 codes in there which is causing my program problems. What are they and how can I save to text without them?

Jim

 

[xlhelp]

There is usually a 240 character limit for Space delimited files, but not Tab delimited. Do your cells have more than 240 characters?

You could concatenate your cells in a row to another column and paste the contents of that column to notepad

Alternately, you could ask this question in the VBA forum and someone may have a macro solution.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[SkipVought]

What is a \240 CODE?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jimktrains]

I mean when I look at it, this is what I see
\240\240\240\240\240

It's not a limit of 240 characters, it's a control code of \240 that I don't know why it is saving it that way.

 

[SkipVought]

What do you see in Notepad?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlhelp]

Found it on Google:

Have you tried
sed 's/\\240//g' file


Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[xlhelp]

You would probably be better off posting this in:
forum80

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[jimktrains]

In notepad, after each field it jumps to the next because of the TAB. But after the one field, it has spaces, one for each \240 code, then jumps to the next field. It has spaces in the Excel spreadsheet as well. Is there a way by column to strip the trailing spaces?

 

[SkipVought]

"It has spaces in the Excel spreadsheet as well"

Simple to get rid of the spaces here!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlhelp]

Yes, the TRIM function

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[jimktrains]

I added a new column and put =TRIM(B1) and when I leave the cell, all I see is =TRIM(B1) in that cell. Shouldn't it show the results of the TRIM functions, like when I do a =SUM() function?

 

[SkipVought]

[quote[I see is =TRIM(B1) in that cellp.quote]

1. Change the FORMAT of the cell from TEXT to GENERAL.

2. EDIT the cell.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

2. EDIT the cell... and hit ENTER.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jimktrains]

Quote: Change the FORMAT of the cell from TEXT to GENERAL.

That allowed me to enter the formula, but when I save it as text again, the new formula field still has the \240 codes in it

 

[SkipVought]

Please do this.

Copy one cell that seems to contain this problem and PASTE it here in a post.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jimktrains]

I found a way to strip it out of the text file on unix. The \240 is an octal character for something unprintable. So I used this unix command

tr -cd '\11\12\40-\176' < infile > outfile

and my outfile is free of the codes.

I guess it is the creator of the Excel file that put that in there that I need to have a conversation with. That's why the =TRIM() functions didn't work because it wasn't a space.

Thanks everyone for your suggestions.

Jim

 

[SkipVought]

[tt]
=IF(CODE(RIGHT(A1,1))=240,LEFT(A1,LEN(A1)-1),A1)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]