find and copy partial contents of cells

**heiniquadrassel** · 11-19-2007, 01:47 PM

Hi all,

I am sorry for the somewhat opaque title. What I was trying to say is this:

I have a list of about 2 million phrases. The list was created by combining two lists in all possible combinations:

a list of about 800 street names
a list of about 1000 property names

example

church street flat
church street apartment
house church street
house king's road
etc

what I would like excel to do is the following:

look at each cell and determine which of the 800 street names it contains. write that streetname into adjacent cell.

this would then leave me with the original column, where each of the 2 million cells has been assigned one of 800 streetnames.

for example:

church street flat | church street
church street apartment | church street
house church street | church street
house king's road | king's road

that's all. I think that can't be too difficult, and probably it has already been discussed here in the forum. unfortunately I did not know for which keywords to search, that is why I couldn't find the posts.

I hope somebody can help me as I need to get this sorted for work as soon as possible. All semi-automatic ways I could think of to accomplish this would take me days that I don't have.

Cheers

**stevekirk** · 11-19-2007, 03:25 PM

hi and welcome,

i am a little confused as to what you have can you give us a bit more detail

are all the

church street flat
church street apartment
house church street
house king's road

in one column if so what column
can these be copied to the end column

steve

**Leith Ross** · 11-19-2007, 03:28 PM

Welcome to the Forum! As a member, you now have full access to the knowledge and posting tools that make this site unique.

In order to provide you with prompt replies to your questions, there are a few rules we ask that you observe. Please remember to follow these rules when you post as a courtesy to your fellow members, and to those who are volunteering their time, and knowledge.

Readability is key in getting a fast response to your question. You have many tools available to assist you in formatting your post. Any code you post, whether a worksheet formula or VBA code should be wrapped. You can find a list of other HTML format options in the forum rules.

How to Wrap your Code
On the Message window Toolbar you will see the # icon. Select the text you want to appear in a Code Window, and Click the # icon. This will wrap the code for you. You can also do this manually by placing the tag [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] at the end of the last line.

You can read the Forum rules by clicking on the link below.
Forum Rules
If you have any questions, contact me by clicking on my name Leith Ross

Sincerely,
Leith Ross

**heiniquadrassel** · 11-19-2007, 04:19 PM

hi steve,

thank you for the quick reply.

the words you quoted are all in one column. There is only one column I am using. The one column consist of about one million cells. Each cell contains between 2 and perhaps 7 words.

e.g.

"church street flat" would be the content of one cell.
"church street 5 bedroom apartments" would be the content of another cell.
etc.

the one thing all 1 million cells have in common is, that they all contain one of about 800 different streetnames, in this case "church street". The street name could also consist of 3 or 4 words. The street name is always combined with additional words like "flat" "5 bedroom flat" or "property".

what i would like to do is to have a second column right beside this column. In this second column, each cell should only contain the appropriate street name of it's left neighbour, without the "flat" or "property" etc. I have attached an example spreadsheet.

Right now the data is only in a .txt file,as I only have Excel 2003 at work. I am planning to put it into a spreadsheet at home with Excel 2007, which I hope supports sheets with 1mio+ lines.

I imagine it would have to be something like:

Compare if cell a1 contains one of the 800 streetnames in list C1:C800. if yes, write that streetname into cell B1. this formula could then be copied all the way down from A1 to A1000000. Or not?

what exactly do you mean by "what column" and "end column"? Aren't all columns the same? I could cut and paste the column everywhere I want, or couldn't I? I hope the attached file answers your question.

I really appreciate your help, thank you very much again

arian

**stevekirk** · 11-20-2007, 10:43 AM

hi,

the criteria seems to be changing from the first post. that post shows
roads, streets, to be common in all cells
you example now shows "house" of which the they are all ater the word "in"

i think we would need to understand what all the varibles are before you can get any help

steve

**Bjornar** · 11-20-2007, 11:54 AM

Put all your unique Street names in Colum D. If you don't use all 800 cells you have to fill all the remaining cell with some text that you are sure you don't find in your 1000000 rows. Eg: "XXXXXXXX"

Then you put this array formula in B3:

Please Login or Register  to view this content.

This is an array formula so you have to press Ctrl+Shift+Enter instead of just Enter when you put it in the cell.
You can then copy this to all the other cells in column B.

**heiniquadrassel** · 11-20-2007, 12:23 PM

Thank you so much Bjornar!

That's just marvellous. I have tested your formula with a small spreadsheet and it works perfectly. I have yet to process the 2mio row version and read up on INDEX, MATCH and SEARCH, but I am confident now that it will work out.

Again, great job, I think I am smiling for the first time today.

Kind regards,
Arian