Excel Excel: Need to search text in column B and if found, move to column A

Joined
May 7, 2012
Messages
3
Reaction score
0
I have a large spreadsheet where I need to search Column B for any cell containing the word "County" and if a cell is found, move that cell over to Column A. The spreadsheet has more than 10,000 rows, so I don't relish the thought of doing this manually.

The cells I am pulling out have more than just the one word. Example:
Column B contents: County: MOUNTRAIL State: ND
The recurring word is "County". Any cell in Column B needs to be moved to Column A without comprimising the rest of the document.

Any suggestions?

Thanks!
 
Joined
Mar 20, 2012
Messages
764
Reaction score
4
You could use the following to either copy the contents of Column B into Column A or show a blank if the word "County" is not in Column B:
=IF(ISERROR(FIND("County",B2)),"",B2)
You would place this formula in A2 (assuming you have column headers) and copy it down the column to get either a blank (if County is not in the cell next to it) or the contents of the cell in column B. Then, you could select all of column A and do Copy and Paste Special > Values to paste the results of the formula and get rid of it.

Let me know if this is what you're looking for!
 
Joined
May 7, 2012
Messages
3
Reaction score
0
Unfortunately, I do have information in Column A that I need to be able to keep. The good news is that if the "County" is appearing in Column B, then the cell in Column A is always going to be empty.

I copied some of the info from the two columns to Word and attached to show what I'm talking about.
 

Attachments

  • Example.doc
    164.5 KB · Views: 222
Joined
Mar 20, 2012
Messages
764
Reaction score
4
What I would suggest is inserting a new column before A and use the following in that column:

=IF(ISBLANK(B1),IF(ISERROR(FIND("County",C1)),"",C1),B1)

I didn't try that out, but I think it will have the desired effect. Then use the Copy and Paste Special > Values to get rid of the formula and delete column B.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top