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

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.

--
Regards,
Tom Ogilvy

"Robert Christie" wrote in message
...
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

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

eg =CLEAN(A1)


---
Message posted from http://www.ExcelForum.com/

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



"Robert Christie" wrote in message
...
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

That is probably a better choice.

--
Regards,
Tom Ogilvy



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

eg =CLEAN(A1)


---
Message posted from http://www.ExcelForum.com/

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

-----Original Message-----
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.

--
Regards,
Tom Ogilvy

"Robert Christie"
wrote in message
...
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



.

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



"Robert Christie" wrote in message
...
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

-----Original Message-----
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.

--
Regards,
Tom Ogilvy

"Robert Christie"
wrote in message
...
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



.

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



"Robert Christie"
wrote in message
...
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

-----Original Message-----
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.

--
Regards,
Tom Ogilvy

"Robert Christie"

wrote in message
...
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



.



.