Advanced search and replace

[Guest]

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

 

[Guest]

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]

Moshe, you're welcome !