Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

marked as duplicate by Ƭᴇcʜιᴇ007, Diogo, ChrisF, BBlake, 8088 Nov 10 '12 at 2:01

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
Are you trying to do this programmatically, or just using manual Search and Replace? What have you tried already? –  Ƭᴇcʜιᴇ007 Nov 9 '12 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 '12 at 15:02
    
Shouldn't it be Va?lue to match what you're describing? –  Oliver Salzburg Nov 9 '12 at 15:02
    
Except that doesn't ? require one character? –  WilliamKF Nov 9 '12 at 15:03

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.