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?
Programmatically in an Excel cell. Thus far it is just using substitute and trying both cases, but I imagine there is a better way.

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.

marked as duplicate by techie007, Diogo, ChrisF, BBlake, 8088 Nov 10 '12 at 2:01