separate single words from multiple words in tabs

[marco_osh]

Hello everyone

Would someone want to think along with my question? thank you!

I do keyword research. and I copy these keywords in excel. Sometimes they are sentences, sometimes they are just words. This often goes with 500 at a time.

Now I want to sort a single word into a new tab.
Keywords with 2 words again in a different tab.
keywords with 3 words in tab 3
and search terms that consist of 4 keywords, I want to have in tab 4.
and so on ..

How should I approach this?

With kind regards, Marco van Spronsen

[#2]

1. @marco_osh   Can you post that spreadsheet (the one you created to illustrate the issue) rather than just an image? It would help any of us who might help actually manipulate the sample.
2. I have a couple questions on the "input" end of things:
   • Is this a daily task?
   • How does the data get collected or entered into this first sheet? Typed by you? Downloaded from some other source? If the latter, what form does it take?
3. And then at the "output" or "intermediate" stage, could you describe briefly what you'll do with the various derivative sheets, with the selections of key words on them?
4. Finally, what is the ultimate objective? Just a count by keywords? Something else?

All of the answers here might influence the best way to proceed.

[#3]

@marco_osh 

I agree with @mathetes , depends on goals and way of handling data that could be different solution.

For example, if manually you may add helper column to calculate number of keywords in each cell as

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

filter the range on it and copy/paste results into other sheets.

Approximately the same approach with Power Query, create queries for each number of keywords returning results into the sheet, to update new set it's only Refresh All.

Another way to automate with one button press is with VBA programming.

[#4]

@mathetes 

1. yes here it is(it's an example of what I mean. To keep it organized for the people here;)

2 a. not daily .. but weekly. ;)

2 b. copy and paste

3. 

The intended purpose is:

By taking these words apart. I can focus on the keywords that still need attention ..

for example:

Short tail keywords: fish ..
fish .. boat
fishing.. rod
fish .. shoes
fish.. bait

long tail key words: fishing with a boat that ..
fishing with a boat that ... goes fast
fishing with a boat that goes ... slow
fishing with a boat that is ... covered
fishing with a boat that is ... quiet

In the Exel document I normally also have the search volume. I have this from low to high. this way I can see where there is little competition. (on which keywords little is searched in google, the more specific the search terms / keyword combination .. the less competition there is)

this way I can process these long tail keywords in my blog posts.

Someone googled: fishing with a boat that goes fast ..
then there is a big chance that my blog article will be shown first in google. if I have processed these keywords in my blog post.

If there are 1000 people per month searching for this .. and my blog article is at the top of the Google search results .. then there is a big chance that a few hundred people will come to my blog. and so on my website;)

But why in this way?
Very simple .. by grouping these keywords under the number of words in 1 sentence .. you can select groups .. and enter them again in machines that after these keywords, paste new words, where people search again.

this way you end up with keywords / search terms that your competition is not yet using.

the disadvantage is that here very few people are probably looking for this. but these few people are very specific looking for something. so the sales chance is much greater than someone who searches google for: fish ..

it is difficult for you to sell a fishing boat to this person looking for fish ... maybe that person is looking for smoked fish;): P

I hope you understand me and that you can help me with this

With kind regards, Marco ;)

[#5]

@marco_osh   So I took Sergei's formula and used it in the attached to make a helper column. Then using Data....Filter, I've copied the 1, 2, and 3 word collections. You can do the rest.

From what you've written, it sounds very much like this "manual" process--which doesn't take long--is sufficient for your weekly analysis. From here, again, from what you've written, you really need to just work through the lists to see what key-word combinations to aim for in your blog so as to attract customers. That is to say, it seems like more of a manual process once you've got the first sheet sorted into the various combinations. If that's not the case, come back with further explanations.

[#6]

oh !!, that's exactly what I'm looking for! .. I just have to understand it all, thanks! :)

[#7]

give me a few minutes hahaha ;)

[#8]

@marco_osh 

The Data.....Filter function is pretty simple. You see the little inverted triangle next to the word "Count" in column B? Click on that and you can select the description (number) that applies to the rows meeting that criterion. Then just copy them and paste them to the appropriate sheet.

[#9]

@mathetes hmmm. you meen this one?

thank you for helping!

[#10]

or do you mean this:

[#11]

@marco_osh 

This one:

You see, right, how you can de-select all of them, and select only ONE number, so that only the rows populated with the selected number of key words will show.

[#12]

@mathetes okay and where should I click afterwards? a new window will open.

sorry I don't get it, but I think we're almost there right? :)

[#13]

@marco_osh 

So here, I've clicked on the little triangle, opened up the dialog box, selected the number "6" and see this:

I then highlight all the resulting cells in column A, as shown, and hit Ctrl-C (in Mac, Command-C), to Copy

After which I Paste those into the worksheet set up for six word groupings.

Make sense?

===============================

P.S. HOWEVER, if really what you're doing is reviewing these by individually examining them, there's really no need to move them to separate sheets. You can simply do the filtering to get the groupings of 2 key words, 3 key words, 4 key words, etc. You could add another column to note those with "long tails" or "short tails" or whatever else you wanted to note for your purposes of generating more hits on your blog.

[#14]

You can simply do the filtering to get the groupings of 2 key words, 3 key words, 4 key words, etc.

I can now sort everything.

With search volume, costs per click (cpc), Paid Difficulty and Search Difficulty.

but what I want is that we start at the top left (A2) with 1 word. and that at the very bottom (501) there are several words with after each word / words, the data.

If this is possible, then i'm happy. :) thanks for the help @mathetes 

[#15]

@marco_osh 

You're now showing a different database than you started with. Remember Sergei's formula that counted the words. If you want the sort to include a sort based on number of key words you need to put that formula in and use the result as part of the sort.

You should be able to do that. If you can't then you'll need to upload this more elaborate worksheet so he or I can put that column in.

But you should be able to accomplish that on your own, Marco.

[#16]

it worked now, thanks for both of your help :)

with kind regards, Marco