Forum Discussion
separate single words from multiple words in tabs
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
15 Replies
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," ",""))+1filter 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.
- 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.
- 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?
- 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?
- 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.
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.. baitlong 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 ... quietIn 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 😉
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.
