Dear friends,

Any idea how to desgin a UDF/Formula to replace a character(s) unless some
specific character(s) are preceding?

An example in this regard:

=UDF(A1,"CH","SCH","X") Or
=UDF(A1,B1,C1,D1) with B1:D1 containing the text:
"CH","SCH","X"

in case of an entry in A1, like:

CHECKXCHOWICH

the result comes out to be:

SCHECKXCHOWISCH

because the CH in the mid has an X preceding, it was not replaced but the
two on the sides did change.

Please see if you can help.

Thanx in advance.

Hi,

Try

=SUBSTITUTE(SUBSTITUTE(B1,"CH","SCH"),"XSCH","XCH" )

or enter the arguments in a few cells. This assumes that

CHECKXCHOWICH is in cell B1.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"FARAZ QURESHI" wrote:

Dear friends,

Any idea how to desgin a UDF/Formula to replace a character(s) unless some
specific character(s) are preceding?

An example in this regard:

=UDF(A1,"CH","SCH","X") Or
=UDF(A1,B1,C1,D1) with B1:D1 containing the text:
"CH","SCH","X"

in case of an entry in A1, like:

CHECKXCHOWICH

the result comes out to be:

SCHECKXCHOWISCH

because the CH in the mid has an X preceding, it was not replaced but the
two on the sides did change.

Please see if you can help.

Thanx in advance.

Something like this...

Function CONVERT1(strData, strFind, strReplace, strIgnore)
CONVERT1 = Replace(strData, strFind, strReplace)
CONVERT1 = Replace(CONVERT1, strIgnore & strReplace, strFind)
End Function

If this post helps click Yes
---------------
Jacob Skaria


"FARAZ QURESHI" wrote:

Dear friends,

Any idea how to desgin a UDF/Formula to replace a character(s) unless some
specific character(s) are preceding?

An example in this regard:

=UDF(A1,"CH","SCH","X") Or
=UDF(A1,B1,C1,D1) with B1:D1 containing the text:
"CH","SCH","X"

in case of an entry in A1, like:

CHECKXCHOWICH

the result comes out to be:

SCHECKXCHOWISCH

because the CH in the mid has an X preceding, it was not replaced but the
two on the sides did change.

Please see if you can help.

Thanx in advance.

Wow Pal,

U R simply the Best!!!!!!

Thanx again, Again & AGAIN!

"Jacob Skaria" wrote:

Something like this...

Function CONVERT1(strData, strFind, strReplace, strIgnore)
CONVERT1 = Replace(strData, strFind, strReplace)
CONVERT1 = Replace(CONVERT1, strIgnore & strReplace, strFind)
End Function

If this post helps click Yes
---------------
Jacob Skaria


"FARAZ QURESHI" wrote:

Dear friends,

Any idea how to desgin a UDF/Formula to replace a character(s) unless some
specific character(s) are preceding?

An example in this regard:

=UDF(A1,"CH","SCH","X") Or
=UDF(A1,B1,C1,D1) with B1:D1 containing the text:
"CH","SCH","X"

in case of an entry in A1, like:

CHECKXCHOWICH

the result comes out to be:

SCHECKXCHOWISCH

because the CH in the mid has an X preceding, it was not replaced but the
two on the sides did change.

Please see if you can help.

Thanx in advance.