Using VBA Replace function for single quote

[galantis]

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

 

[Roman]

Hi Galantis,
try this:

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

 

[Guest]

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]

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]

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

thanks.

galantis.

 

[galantis]

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

 

[Guest]

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.

 

[Bob Umlas]

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

 

[Guest]

thank you. that's pretty neat.

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