In this article we will talk about:
- LEFT function in Excel
- RIGHT function in Excel
- Character count with CARCAR NUMBER
- LEFT, RIGHT and FIND
- Example of use of functions
That the Excel is one of the most used management tools in companies, you should know by now. But did you know that most people do not use half the potential of this powerful tool because their details give the appearance of a seven-headed animal?
Yeah! So today we will show you how to deal with those details. It's easier than it looks!
Let's say you have a company, organize your stock of products in a Excel, and need a list with product codes, but you want only the first six digits of them. In this case, the functions LEFT e RIGHT fit perfectly. If you want to see explanations about these video functions, I recommend Advanced Excel Course of LUZ.
The function LEFT(), extracts the entire contents of a cell from the specified number of characters. Complicated? So let's make a simple explanation.
To extract the six digits of the Notebook, choose the cell where you want the digits to appear and execute the function = LEFT (B3; 6). The number six serves to indicate the number of characters extracted to the left of the cell.
Applied the function, will result in the number 210396. It's exactly the six digits to the left of the cell in question.
The same can be done if you need only the information located on the right side. Just use the function RIGHT (). Just to practice the operation better, I'll use a similar example, but drawing a different amount of characters.
In this case, let's assume you need only the last three digits of a code, located to the right of a cell. Then you must apply the = RIGHT (B4; 3).
Getting the desired result.
Moele, is not it?
If you need to perform a character count on your spreadsheet, the Excel does this automatically. Great for making your job easier!
There are two ways, they are:
Counting characters with whitespace
Just apply the function = CARCODE NUMBER (desired_cell_number), and ready!
Posting characters without blanks
To make a more precise count, eliminating the white spaces between words, just work with the same function, but usingREPLACE together with ACCOUNT NUMBER, thus:
Separate the column where you want to get the result of the count by applying the function = REPLACE NUMBER (REPLACE (cell_description_desired; ''; ")), this will cause blanks not to be considered "characters".
Let's say you now want to work with a spreadsheet template to manage the employees of your company. And you need to separate the first name do last name of each of them.
To do this, you must use the following functions:
The functions RIGHT e LEFT, you already learned and saw how simple they are, is not it? Now we will continue to learn how to apply the functions TO LOCATE eACCOUNT NUMBER.
Using LEFT e TO LOCATE together, to separate the first name. In the image below we have a basic example with names of fictitious employees.
Applies the function = LEFT (A3; LOCATE (""; A3)).
So we will get only the first name Alexandre.
You must be asking yourself: "Theend, what does this mean?" It's simple!
The function LEFT () tells the Excel that you want the content that is on the left side of the cell in question. A3 represents the cell you are specifying to fetch. In conjunction with the TO LOCATE(" "; A3), this formula tells the Excel that you want to fetch what's inside the cell A3.
Following the example we used above, to separate only the employee's last name Alexandre Borges. We apply the function = RIGHT (A3; NUMBER (A3) - NUM.CARACT (C3)).
That will result in your last name Borges.
I explain what was done in detail:
The function ACCOUNT NUMBER, does the Excel understand that you want to work with the number of characters of a given cell.
Thus, = RIGHT (A3; NUMBER (A3) - NUM.CARACT (C3)), points to the Excel that you want the number of characters contained within the cell A3 (Alexandre Borges) subtracting the number of characters from the cell C3 (Alexandre), resulting in D3 (Borges).
What do you think of these functions?
Comment your opinion on the comments below and if you are interested in learning these text-manipulation functions in practice and many others, I recommend that you see the Advanced Excel course from light.