Hello,

I will need your assistance. I have in field of Excel an address.
Sometimes in this address, it sometimes happens to have a special
character which is a square. How to remove it and replace it by a
space.

I wish to make a function which starts to seek this character in each
cell of C6 with C65000. It must pass from cell to cell, to check in the
string cell if this character is present and so yes to replace it by a
space but how to do that. I started but I cannot continue

Here my code:

Sub test()
For Each Cell In Worksheets("Feuil1").Range("C6:C65000")
Replace(ActiveCell.Value, Chr$(7), " ")
Next Cell
End Sub


Example in my cell:

10, Bvd of London <- here, i ve a square and after an enter key

Paris


thank you for your assistance

Keawee


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

See if this works.

Sub test()
For Each c In Selection 'Worksheets("Feuil1").Range("C6:C65000")
c.Value = Application.Substitute(c, Chr(7), "")
Next c
End Sub

"keawee" wrote in message
...

Hello,

I will need your assistance. I have in field of Excel an address.
Sometimes in this address, it sometimes happens to have a special
character which is a square. How to remove it and replace it by a
space.

I wish to make a function which starts to seek this character in each
cell of C6 with C65000. It must pass from cell to cell, to check in the
string cell if this character is present and so yes to replace it by a
space but how to do that. I started but I cannot continue

Here my code:

Sub test()
For Each Cell In Worksheets("Feuil1").Range("C6:C65000")
Replace(ActiveCell.Value, Chr$(7), " ")
Next Cell
End Sub


Example in my cell:

10, Bvd of London <- here, i ve a square and after an enter key

Paris


thank you for your assistance

Keawee


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/