Using VBA Replace function for single quote

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

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, 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

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.
Re: 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" <[email protected]> wrote in message
news:[email protected]...
>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.
> Re: 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" <[email protected]> wrote in message
> news:[email protected]...
> >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.
> > Re: Chr$(34) I can't find the Chr$ anywhere.
> >
> > "Roman" wrote:
> >
> >> Hi Galantis,
> >> try this:
> >>
> >> Selection.Replace Chr$(34), "", xlPart
> >>
> >>
>
>
>