Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / December 2006

Tip: Looking for answers? Try searching our database.

Advanced search and replace

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
MosheMo - 10 Dec 2006 08:16 GMT
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

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.