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 sevenheaded 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.
LEFT function in Excel
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.
RIGHT function in Excel
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?
Automating the character count using the NUM.CARACT function
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".
Using the RIGHT, LEFT functions in conjunction with LOCATE to simplify your business spreadsheets
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:

RIGHT

LEFT

TO LOCATE

ACCOUNT NUMBER
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.
Separating only the last name from your spreadsheet template
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 textmanipulation functions in practice and many others, I recommend that you see the Advanced Excel course from light.
Hi @Pedro 13/08/2020 at 15:57, you can use the solution below:
Considering the example
A1: Sérgio Ambrósio (19880208)
In B1: = STORAGE (LEFT (A1; SEARCH ("("; A1)))
Em C1: =DATA.VALOR(EXT.TEXTO(A1;PROCURAR(“(“;A1)+1;10))
I would like to know how to exclude characters from the right. Example: let's imagine that I have names accompanied by numbers in a column, example (João 55,33) and I want to exclude the last 5 characters of each line, what is the function to achieve?
Colleague, I need to 'add' only the numbers in a cell 'range'. I tried with the following formula, but to no avail:
= SUM (LEFT (A1: A3))
The result of this is:
#VALUE!
For testing purposes, if I remove the 'range' A1: A3 from the formula (keeping only A1, for example) it will give me the number correctly extracted from the cell. That is, it can extract the cell number correctly but cannot do so in a range.
I need to do this because the idea is to put a list in each cell of the range; list items have, as the first character, a number that will represent a value. So, as the list items are selected in each cell, I will have the sum of the values in a result cell.
Thankful.
Is it possible to make a logical test of an SE () with the right function ()?
I understand, what value is in the lines B2, C2 and A1?
=IF(B2=$B$2,RIGHT(LEFT(C2,4),3)/1,A1)
I can support with this formaula since it is very complex for me apart that I do not know anything English pfv gcs
<9101  Cat Monterrey Perishables
Department 1520  SWEETS AND STATIONERY
Date Inventory Item # VALUE!
43625 341379
43625 423895
Department 3730  LACTEOS
Hi Erik, yes you can, just use the right function to see the number searched and compare with the value of the formula
Can I do a PROCV + RIGHT?
I have an 471749 number in a table, however in the array I want to get it it is found as follows "Advance Revenue. #471749 ".
I need to bring the information if the number in the cell I'm looking for is in the array.
Hi Leonardo, just creating auxiliary columns to transform the 08 number into 08 text and then using LEFT on it
Good evening, in the LEFT function, how do I get Excel to present me the 0 number? Example: I need to separate the 08 number that is in the same cell. I've separated with the LEFT formula, but they can not return the 0 number, it does not recognize the 0 number, I need the cell to be formatted as NUMBER, I can not leave text, I'd have some other way of separating those two numbers or some formula within the LEFT that recognizes 0? Thank you very much.
You can use the LOCATE function to find the hyphen and then use the LEFT function to get the amount of characters on the left side (which will be LOCALIZE  1)
And how do you get the numbers before the hyphen? for example: 02546 refrigerator, I'm breaking my head to solve this.
Hi Alex, maybe it's possible in formatting that cell's number
Good evening Rafael, I needed to make the following change:
APRIL 13, 1996
Yes,
April 13 1996
Do you know how??
Hi Gabriel, not that I know that quickly. I believe until it is possible to use some other structure, but if it works, I would keep it.
Good afternoon Rafael
Could you tell me if you had any simpler formula for doing this?
Example:
A2  GABRIEL SALES
The formula that I'm using to get the 6 characters of the last name + 1 of the last name (if the last name does not have 6 characters, add + 1 of the name, for example: salesGA. (MID (A2, SEARCH ("", A2,1), LEN (A2))) & LEFT (A6), LEN (A2) TRIM (MID (A2,1, SEARCH ("", A2), LEN (A2,7)))), TRIM (MID (A2, SEARCH ("", A2,1), 2)) & LEFT (A2) but I wonder if there is a way to summarize.
Thanks for sharing your solution Bruno!
I was able to use the Right with Find function. You must count the number of characters in your cell and subtract the value from the find. For my problem, I solved it this way:
= RIGHT ($ A $ 2; NUM.CARACT ($ A $ 2) PROCATE (""; $ A $ 2) 1)
Note: the 1 number being subtracted at the end was due to my particular problem, because shortly after the hyphen came a space, and I did not want that space to appear in the final result, thus reducing 1 to the final result
Hi Max, I believe that it is possible, although I do not remember to have tested
can not use find in right function?
In this case you would need to use more text manipulation functions like FIND, EXT.TEXT together with the LEFT
What if the staff has 3 or more names, how do I get just the last name?
Hi Luana, you can use the LOCATE function to find the first blank space along with the EXT.TEXTO function to pick up words in the middle
Oie,
I was able to apply the formula left / right + find ... gave almost super right ... I would like to know if I have to limit the search to just 1 word ... for example: in a shopping list that is in text like this: 1 3 pasta ... a formula works for the item number (1) when I move to the next column that is the product (Noodles), in the column appears the item number + the product. Can you leave only the product in the column?
That,
Luana
Forgot to open a space between the quotation marks of Find!
You can enter a range from a single cell. If you put more cells you will have a #VALOR problem
Hello! I liked the blog!
I have a doubt:
can I insert a range in the LEFT function, in the TEXT syntax?
I did the 1 part, but instead of returning the whole name, just returned the letter A, why will it?
https://uploads.disquscdn.com/images/95fe6527e2ef999792e9aeded6e1c1d498b5d29558f2a6827941d79beed8e5ed.png
I did the 1 part, and I only returned the letter A and not Alexander, why will it?