Need help filtering keywords in excel

[lolseo]

Hi guys,

Ive got a list of 200+ keywords from the keyword planner.

Im wanting to break these into categories for example.

(everything below is a fictional example)

My main keyword was "dog grooming"

Example keywords are
"easy dog grooming tips"
"how to make money with dog grooming"
"how to walk your dog"
"dog grooming for beginners"
"dog behaving badly"

Does anyone have a solution so i can "ctrl + f" search for "dog grooming" and copy the entire rows (not just the cells)

I'd like to run a search in excel for "dog grooming" and copy all rows (so the search volume, cpc etc associated with the term) into its own sheet. I've followed a vlookup tutorial but i don't think vlookup is what i need for this scenario.

The way ive filtered in the past is coming up with a set of "groups" for example

"Dog walking" - 1
"Dog Grooming" - 2
"Dog Training" - 3

I'd then go down my list and mark a number next to each phrase
"easy dog grooming tips" - 2
"how to make money with dog grooming" - 2
"how to walk your dog"
"dog grooming for beginners" -2
"dog behaving badly"

I'd then filter that column for the number and copy and paste everything onto a separate sheet.

This is very time consuming however.

Does anyone have a solution so i can "ctrl + f" search for "dog grooming" and copy the entire rows (not just the cells)

 

[Schvamp]

I can create you a tool for you wich you can use for upcoming projects aswell.
PM me your skype, no payments needed

 

[lolseo]

Im sure there will be a method using excel for this?

 

[DenisWeb]

Use filters. Set custom text filter with your KW on "keywords column" and you will see phrases only with your KW

 

[lolseo]

Use filters. Set custom text filter with your KW on "keywords column" and you will see phrases only with your KW
View attachment 41261

Thanks for the reply Denis but this doesn't work.

Using the filter option shows, for example all 200 of my keywords. The term "dog grooming" may be nested as the 3rd and 4th term of my search. Even using this option, its essentially the same as me going down the list by hand numbering it.

 

[ooPekoPeko]

CTRL+H (there must be something like Find and Replace, sorry don't have excel at hand right now)--> "*dog grooming*" (put "*" before and after keywords to select all the cell) --> search all --> then select all results in results bar --> click on excel window --> CTRL+C --> Select empty row or another sheet and CTRL+V

 

[lolseo]

Thanks for the reply Peko, but this is what i've already done.

My columins are for example

Keyword - search volume - cpc

Using your method (the same as mine, apart from using the "*" before and after the keyword only allows me to copy that cell, containing the keyword. I need to copy all the columns (the entire row for all instances of terms containing "dog grooming", NOT just the cell containing my keyword.

When doing ctrl+f and highlighting everything in the result section, it only highlights the individuals cells, not the entire row.

 

[lolseo]

Anyone else have any advice?

 

[Guifi]

This might not be perfect solution, but you might first categorize your keywords with some =SEARCH and conditionals, and then just filter out each category. Or automate a lil bit more with vlookup.

Here is a handy reference on slicing and dicing strings off text specially lesson2

Google this, I can't post links yet since I'm new here =D
Microsoft excel for SEOs - Distilled


Hope it helps!

 

[devde]

create an additional column and put this code (assuming A is the column where you have your keywords)

=IF(ISERROR(FIND("dog grooming",A1,1))=1,0,A1)

If the keyword contains "dog grooming" the formula will return the keyword itself otherwise it will return 0.
You can then sort the spreadsheet, you will have all the 0 at top or bottom (depending if you choosed ascending or descending sort), delete all the rows which contain 0 and your end result will be the rows containing only the keyword "dog grooming"

 

[lolseo]

create an additional column and put this code (assuming A is the column where you have your keywords)

=IF(ISERROR(FIND("dog grooming",A1,1))=1,0,A1)

If the keyword contains "dog grooming" the formula will return the keyword itself otherwise it will return 0.
You can then sort the spreadsheet, you will have all the 0 at top or bottom (depending if you choosed ascending or descending sort), delete all the rows which contain 0 and your end result will be the rows containing only the keyword "dog grooming"

Thanks ill give this ago