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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"MosheMo" wrote:
> 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
>
>
Similar Threads
