I’m glad that now I’m writing here not through a proxy, as I wrote my last post with congratulations. The matter is that in the end of December for some reasons some servers were closed. Apparently, the SEO blog of the sectarian came under fire and as a result did not open from Belarus. But now, almost everything is always good.
So what I mean. Today, I have something to share with you. I’m sure everyone used the Excel program ever. I tried to learn it in computer science lessons, where we were told completely incomprehensible and uninteresting things. Then I used it on my first job as a sales manager. There was no special cunning: just write down the information in the table and mark the cells with different colors.
Bonus: I found a good article with the correspondence of English and Russian formulas in Excel. It is useful if you use this program in different languages.
This formula allows you to make all the first letters in the words capitalized, which is useful when writing titles. You simply write, not paying attention to the register, and then substitute the formula – and everything is ready.
When you need to write 50-60 characters in a title or 156-160 in a meta description, one way to control the number of characters is to substitute text in Word, which is effective, but long. In Excel, everything happens much simpler, it is enough to use the DLSTR formula.
This formula allows you to “glue” values in several cells. For example, it helped me a lot when creating regular expressions. After this formula, the values of all the cells that interest you are to be inserted in parentheses through the semicolon
Allocation of domains from the list of address pages
Suppose you have a list of backlinks that you need to systematize. It’s clear that many URLs will be from the same domains, so why not separate the domains separately, and then do not sort pages by them?
To select domains from the list of URLs, you do not use such a simple formula as those listed above. You need something more powerful: a hybrid of two formulas – LEVSIMV (LEFT) and SEARCH (SEARCH) .
In the end, we will use this formula
= LEFT (A1; SEARCH (“/”; A1; 9)) – in English
In this case:
= LEVSIMV (LEFT) – means that from the address of the page we need the part that is on the left.
A1 – column number, where we take the domain. This is the only meaning of the formula that can change constantly.
SEARCH – is responsible for finding the part of the URL that we need.
“/” – here we are We show that we are looking for a slash
9 is the ordinal number of the first character of the domain after http: // (7 characters). Here you can write number 8, only in this case you will not receive domains from addresses with Http: s : // (8 characters).
You do not even need to remember this formula, just copy it to yourself and use it if necessary, without forgetting to change the cell numbers.
Selecting and removing duplicate values
This is probably not a formula, but a very useful function. But despite its usefulness, very few people know about it and use it. But in vain.
To find duplicate values in a column, select it and click Conditional Formatting> Cell Selection Rules> Conditional Formatting> Highlight Cells Rules> Duplicate Values
As a result, the column shows the duplicated values:
To remove duplicates, select the column (as well as all those columns that refer to it so that the values do not go down), and select Data> Remove Duplicates from the menu. Then tick only next to the column you want to remove the duplicates from, click OK. With this deletion, of the several duplicate values, only one remains in the column. In other words, if there were 2 identical lines, 1 would be deleted; If 3, retires 2, etc.
How to quickly apply the formula to several cells
For example, you have 200 lines, each with text, and you need to know the number of characters in each line. To do this, you write down the formula (you already know which “class =” wp-smiley “/>), and then just drag it down, so it applies to all the rows. To stretch the formula down , Select the bottom right corner of the line:
And then – start actively using new knowledge to optimize and speed up your work. At first it may seem complicated, but eventually you will understand that everything is simple))) The bottom line is that you do not need to understand Excel as a pro. It is enough only those knowledge that you use day in and day out, the rest is superfluous.
I await your comments, additions and formulas that you use. And, of course, subscribe to the SEO Blog of the sectarian. It’s very simple and without formulas