Hi,

I want to replace single quote in my excel worksheet using replace
function.

The way I am using at the moment to replace blank spaces to empty
spaces in one entire column is

newSheet.Columns("A").Replace " ", "", xlPart

I would like to do the same with text containing single quote e.g
"""PNL """"W"""" TRANSFE
to
PNLWTRANSFE

What do you put in for the substring to search for?

thanks in advance

Galantis


--
galantis
------------------------------------------------------------------------
galantis's Profile: http://www.excelforum.com/member.php...o&userid=24739
View this thread: http://www.excelforum.com/showthread...hreadid=383053

Hi Galantis,
try this:

Selection.Replace Chr$(34), "", xlPart

Any time you need to put a quote mark into a literal string you simply double
it up, like this:

MyString = "I want to say ""Hello"" to you"

Therefore to search for a single quote mark in the Replace function you need
four quote marks like this:

NewString = Replace(OldString, """", "")

(Example is of the VB Replace function)

Why four quotes marks? The first is the opening quote mark for the literal
string value, the next two are the doubled-up quote mark within the literal
string, and the last is the closing quote mark to end the literal string.
Clear as mud?

"galantis" wrote:


Hi,

I want to replace single quote in my excel worksheet using replace
function.

The way I am using at the moment to replace blank spaces to empty
spaces in one entire column is

newSheet.Columns("A").Replace " ", "", xlPart

I would like to do the same with text containing single quote e.g
"""PNL """"W"""" TRANSFE
to
PNLWTRANSFE

What do you put in for the substring to search for?

thanks in advance

Galantis


--
galantis
------------------------------------------------------------------------
galantis's Profile: http://www.excelforum.com/member.php...o&userid=24739
View this thread: http://www.excelforum.com/showthread...hreadid=383053

thanks for the reply and it works!

now, how do you catch the error generated when find and replacing
substring that does not exist? a Null

I do not want a error pop-up message box which stops my marco.

galantis


--
galantis
------------------------------------------------------------------------
galantis's Profile: http://www.excelforum.com/member.php...o&userid=24739
View this thread: http://www.excelforum.com/showthread...hreadid=383053

it's okay, I found a similar item been asked from this forum and I will
try to use it.

thanks.

galantis.


--
galantis
------------------------------------------------------------------------
galantis's Profile: http://www.excelforum.com/member.php...o&userid=24739
View this thread: http://www.excelforum.com/showthread...hreadid=383053

thanks, suggestions works!

but now, how do I catch the error if the find and replace substring
don't find anything? returns a null?


I do not want a pop-up message box which stops my marco.

galantis


--
galantis
------------------------------------------------------------------------
galantis's Profile: http://www.excelforum.com/member.php...o&userid=24739
View this thread: http://www.excelforum.com/showthread...hreadid=383053

I used to have a chart with all the keyboard alpha, numeric, and symbol
characters. I would like to know where to find the numeric value for this.
Chr$(34) I can't find the Chr$ anywhere.

"Roman" wrote:

Hi Galantis,
try this:

Selection.Replace Chr$(34), "", xlPart

The result can vary depending on the font used, but you can do the
following:
in A1, enter =CHAR(ROW()) and fill this down to A255.
Now, you can make the symbols larger, obviously, by increasing the font of
the column, to, say, 14 pt. - The first 32 rows or so will mainly just show
a little square, but scroll down.
Now, you can copy column A out to Columns B & beyond, and format each column
as a new font to see different symbols, especially Wingdings, etc.

You can use this chart to know how to enter the symbol directly. For
example, to enter a ¢ sign, you can see it's on row 162, so you can hold the
Alt key, and type 0162 from the numeric keypad (leading 0 important), then
let go of the Alt key and the symbol will appear.

Bob Umlas
Excel MVP

"lschuh" wrote in message
...
I used to have a chart with all the keyboard alpha, numeric, and symbol
characters. I would like to know where to find the numeric value for
this.
Chr$(34) I can't find the Chr$ anywhere.

"Roman" wrote:

Hi Galantis,
try this:

Selection.Replace Chr$(34), "", xlPart

thank you. that's pretty neat.

"Bob Umlas" wrote:

The result can vary depending on the font used, but you can do the
following:
in A1, enter =CHAR(ROW()) and fill this down to A255.
Now, you can make the symbols larger, obviously, by increasing the font of
the column, to, say, 14 pt. - The first 32 rows or so will mainly just show
a little square, but scroll down.
Now, you can copy column A out to Columns B & beyond, and format each column
as a new font to see different symbols, especially Wingdings, etc.

You can use this chart to know how to enter the symbol directly. For
example, to enter a ¢ sign, you can see it's on row 162, so you can hold the
Alt key, and type 0162 from the numeric keypad (leading 0 important), then
let go of the Alt key and the symbol will appear.

Bob Umlas
Excel MVP

"lschuh" wrote in message
...
I used to have a chart with all the keyboard alpha, numeric, and symbol
characters. I would like to know where to find the numeric value for
this.
Chr$(34) I can't find the Chr$ anywhere.

"Roman" wrote:

Hi Galantis,
try this:

Selection.Replace Chr$(34), "", xlPart