ExcelBanter - If contains

On Wed, 24 Jun 2009 11:07:01 -0700, lightbulb
wrote:

This is kinda hard to explain, but I'll do the best I can to try. I want a
formula that does the following...

I want to search column G, for data that is in column E. Anytime there's
data found in column G that is in column E, I want it to be replaced with the
information from column F. The trick is that there may be information before
or after the data from column G that's being looked up from column E. For
example, if in E2 I have X-129.9Y-135.94 and in G4 I have
G98G73X-129.9Y-135.94Z5.5R5.Q2.F50. and F2 has 129.9Y-135.94(E-25)...I want
to the part of the data in G4 that matches E2, with the data in F2.

I could simply do a find and replace, but there are hundreds in the file, so
it would take forever and I was wondering if there was a macro or a formula
that I could use instead.

Any help?

This solution assumes that there are no blanks in column G.

In the formula, "rng" refers to the range in column G where the data exists:
e.g. E2:E1000, or whatever.

The formula can be entered in, for example, column H, adjacent to the first
entry in Column G that you need to replace. Then fill down.

This formula must be **array-entered**:

=IF(ISNA(REPLACE(G2,MAX(IF(ISNUMBER(FIND(rng,G2)),
FIND(rng,G2),0)),LEN(INDEX(rng,MATCH(TRUE,
ISNUMBER(FIND(rng,G2)),0))),INDEX(OFFSET(rng,0,1),
MATCH(TRUE,ISNUMBER(FIND(rng,G2)),0)))),G2,
REPLACE(G2,MAX(IF(ISNUMBER(FIND(rng,G2)),
FIND(rng,G2),0)),LEN(INDEX(rng,MATCH(TRUE,
ISNUMBER(FIND(rng,G2)),0))),INDEX(OFFSET(rng,0,1),
MATCH(TRUE,ISNUMBER(FIND(rng,G2)),0))))
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

If you want to replace the original data:
Select the range within Column H that has the results
Edit/Copy
select the first cell in column G (e.g. G2)
Edit/Paste Special/Values
--ron