Finding "Single Quotes" and replacing with a "Space"

[buddhabang]

Hi,

I've just written a report and in one of the fields, the Full_Address, I get entries such as:

Flat 1 99 Rowland's Road PO36 3XX

Unfortunately, this single quote causes issues once the data is extracted and used in an access database. I'm asking if there is anyway i can replace the quote. I browsed the forum but could not find the right answer anywhere.

This is what i'm currently using but brings back the same thing, no change:

Replace(Addr_Supply,FindOneOf(Addr_Supply,chr(39)), '')   OR

If(FindOneOf(Addr_Supply, '?~*" ' & Chr(39)), replace(Addr_Supply,FindOneOf(Addr_Supply,'?~*" ' &chr(39)), '-'))

Both return  (Flat 1 99 Rowland's Road PO36 3XX) - Same thing, no change

Can you please help? Much appreciated.

[matt_crowther · Accepted solution]

Try purgechar(Full_Address,chr(39))

Works for me.

Matt - Visual Analytics Ltd

@QlikviewBI

Qlikview Design Blog: http://QVDesign.wordpress.com

[#3]

Hi,

Try

replace(Full_Address,chr(39),chr(32))

Regards

[buddhabang]

Thanks Mathew, you're a star mate, really appreciate it.

Cheers.

[#5]

Hi,

You should have mentioned that u want to remove the ' instead of replace

Anyways u got the answer

Regards

[#6]

Hi,

U can use this expression.

=Replace(Addr_Supply,Chr(39),' ')

Regards,

Kabilan K.

[buddhabang]

Thanks guys for help, appreciate it. Yes you are right techie, should have said remove.

Thanks anyway guys