Duplicate Certain Character In Cell

[henz999]

Hello everyone,

I need help on how to duplicate certain character in a cell if it is found. For instance, I would like to duplicate character ' for every this single character that is found.
Sample Data:
Rich'ard
An'dre'as
Anthon'y

Expected Result
Rich''ard
An''dre''as
Anthon''y

The reason why I want to duplicate character ' is because I will be passing these characters to database function. As you probably know, if we don't duplicate the character, it would be treated as enclosing text argument thus result in error. If it is duplicated, it would be skipped and passed as actual ' character.

Warm Regards

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B
1
2	Rich'ard	Rich''ard
3	An'dre'as	An''dre''as
4	Anthon'y	Anthon''y
Data

Cell Formulas
Range	Formula
B2:B4	B2	=SUBSTITUTE(A2,"'","''")