How to do concise two alternative substitutions in Windows Excel 2010? [duplicate]

Question

Possible Duplicate:
Can you do Regular Expressions in Excel without VBScript?

I wish to replace a string pattern with another along the lines of the following:

=SUBSTITUTE(SUBSTITUTE(name," Vlue", " Replace")," Value", " Replace")

Wherein I am looking for either " Value" or " Vlue" and replacing it with " Replace".

Perhaps something heading in this direction (that does not work):

=SUBSTITUTE(name," V?lue", " Replace")

How can I easily achieve this in Excel 2010 on Windows?

Are you trying to do this programmatically, or just using manual Search and Replace? What have you tried already? — Ƭᴇcʜιᴇ007, Nov 9, 2012 at 15:00
Programmatically in an Excel cell. Thus far it is just using substitute and trying both cases, but I imagine there is a better way. — WilliamKF, Nov 9, 2012 at 15:02

Jook · Accepted Answer · 2012-11-09 15:56:11Z

Not that easy as you expect it to be.

First of, ? requires a letter, hence is not suited for your case, because only 5 letter words would be accepted, not Vlue.

To do this replacement with formulas I needed the following:

A1 = "this is vlue test"
B1 = "v*lue"

Position, where v*lue begins

C1 = SEARCH(B1,A1,1)

Position, where v*lue ends. This might need improvement.

D1 = SEARCH(" ",A1,C1)

Substitution:

E1 = SUBSTITUDE(A1,MID(A1,C1,D1-C1),"REPLACE")

You can of course combine this all into one formula, but because it will get quickly confusing, I didn't.

1 Answer 1