Unwanted Square Character in .txt file

[Robert Christie]

Hi all
Using XP Windows and XP Office 2002.
I'm using Excel to open a tab delimited .txt file which
is a report from a database.
At the end of each row of fields is a total of the fields
in the row. i.e 1 1 1 3
This total number has a square character on the end.
Is it possible to remove this square character using a
macro.?
I've tried using TrimAll, copying and adding a blank cell
using paste special, but can not remove it.
I tried a find and replace, but the square character is
copied across to the find as a space.
Could I use a macro to copy/select only the numbers to a
helper column along side, copy and paste special values
only then delete the original column,
The Square Character can be deleted manually.

TIA

Bob Christie

 

[Tom Ogilvy]

assuming you are correct and this is the last cell in the string (no
invisible characters like spaces).

in a cell next to the offensive text put in a formula like

=code(right(A1,1))
Assume the offensive text is in cell A1

this will tell you what the ascii code for the character is.

You could then use this information to do the replace or you could just use
a formula like

=left(A1,Len(A1)-1)

then drag fill this down the column. Select the results, do Edit =>Copy

Select the original Data then do Edit=>Paste Special and select values.

then delete the column with the formulas.

You could write similar type code.

 

[BrianB]

There is a worksheet function called CLEAN() that may help.
This removes any characters that are not printable.

eg =CLEAN(A1)

 

[Tomek]

Hi,
those square characters are for example new page characters (code 12) or
other non printable characters. You may get rid of them using worksheet
function CLEAN() or programmaticaly looping through cells searching for
characters of ascii code below 32, You may find them using function Asc().
Tomek

 

[Tom Ogilvy]

That is probably a better choice.

 

[Robert Christie]

Thanks Tom
Your code formula said it was ascii 3, which I think is a
black heart shape,(Ascii 3 is alt key+numberpad 3) yes?

Tom from BrianB and Tomek posts can I assume your formula
=left(A1,Len(A1)-1) is a Boots & Braces way of going
about the problem, where CLEAN() may not remove some
characters.

Thankyou again Tom and to BrianB and Tomek.
PS. Once again the good people of this group have saved
my Bacon, much appreciated.

Bob Christie

 

[Tom Ogilvy]

My approach is more generalized, so I guess you can characterize it as that.
I believe Clean will get anything that shows as a square, but there are
other types of characters that can cause problems even though they don't
show as a square. Also, discovering the ascii value of the character can
allow you to use the replace method in your code rather than doing cell by
cell

cells.replace What:=Chr(3), Replacement:="", Lookat:=xlPart

It depends on how you want to attack the problem.

by the way, char 3 is shown as End of Text (ETX) in the acsii code table.

sometimes you can have success doing replace through the menu. In the first
box hold down the Alt Key and using the Numeric Keypad, enter 003

then try the replace [click the replace all button]. (leave the second box
blank).

 

[Robert Christie]

Thankyou Tom for added info and your time.

Bob Christie

-----Original Message-----
My approach is more generalized, so I guess you can characterize it as that.
I believe Clean will get anything that shows as a square, but there are
other types of characters that can cause problems even though they don't
show as a square. Also, discovering the ascii value of the character can
allow you to use the replace method in your code rather than doing cell by
cell

cells.replace What:=Chr(3), Replacement:="", Lookat:=xlPart

It depends on how you want to attack the problem.

by the way, char 3 is shown as End of Text (ETX) in the acsii code table.

sometimes you can have success doing replace through the menu. In the first
box hold down the Alt Key and using the Numeric Keypad, enter 003

then try the replace [click the replace all button]. (leave the second box
blank).

--
Regards,
Tom Ogilvy

Thanks Tom
Your code formula said it was ascii 3, which I think is a
black heart shape,(Ascii 3 is alt key+numberpad 3) yes?

Tom from BrianB and Tomek posts can I assume your formula
=left(A1,Len(A1)-1) is a Boots & Braces way of going
about the problem, where CLEAN() may not remove some
characters.

Thankyou again Tom and to BrianB and Tomek.
PS. Once again the good people of this group have saved
my Bacon, much appreciated.

Bob Christie
character
is. or
you could just use and
select values.

wrote in message to
a

.

 

[Guest]

I think it shameful that the ability to search and replace a tab character (or other special character) can be included in Microsoft Word and not in Excel. If you can perform this programmatically MS, then put it into the spreadsheets function capability
Giac

 

[Dave Peterson]

=substitute(a1,char(9)," ")

would replace a tab character with a space character using a worksheet function.