Hello,
I want to do what I think is an advanced search and replace and I don't know
how to do it. In column a I have a variety of phrases. In column b, c, d,
etc. I have a variety f sentences (each column has exactly the same
sentences). Each sentence has one phrase in common (although the rest of the
sentence is different). I want to replace that prase with each of the
phrases in column a. Let me diagram what I mean:
Column a
young, small boy
young, tall boy
young, small girl
young, tall girl
Etc.
Column b
I was talking with a nice, old man
I was listening to a nice, old man
I was sitting with a nice, old man
Etc.
Column c
I was talking with a nice, old man
I was listening to a nice, old man
I was sitting with a nice, old man
Etc.
I want the end results to be the following:
Column b
I was talking with a young, small boy
I was listening to a young, small boy
I was sitting with a young, small boy
Etc.
Column c
I was talking with a young, tall boy
I was listening to a young, tall boy
I was sitting with a young, tall boy
Etc.
Column d
I was talking with a young, small girl
I was listening to a young, small girl
I was sitting with a young, small girl
Etc.
And so on.
How can I do this? Thanks and be well.
All the best,
Moshe
Max - 10 Dec 2006 12:50 GMT
One formulas play to try ..
Assuming data1 below in A1:A4
> young, small boy
> young, tall boy
> young, small girl
> young, tall girl
and data2 below in B1:B3
> I was talking with a nice, old man
> I was listening to a nice, old man
> I was sitting with a nice, old man
Put in C1:
=SUBSTITUTE($B1,"nice, old man",OFFSET($A$1,COLUMN(A1)-1,))
Copy C1 across by 4 cols (as data1's no. of lines = 4) to F1,
then fill down to the last row of data in data2, ie to F3
The above will return the required results in cols C to F

Signature
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
> Hello,
>
[quoted text clipped - 50 lines]
>
> Moshe