Applying the LEFT and RIGHT functions in Business Sheets

29
40299
Left Function and Locate
Excel Spreadsheets

In this article we will talk about:

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!

Excel Online Course

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.

Function Left excel

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.

Left Function excel - left character application

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).

Right Function excel

Getting the desired result.

Right Function excel - right character application

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.

Left and right function - database

Applies the function = LEFT (A3; LOCATE (""; A3)).

Left Function and Locate

So we will get only the first name Alexandre.

Result Function left and locate

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.

right and left functions - last name

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).

Excel Online Course

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.

Excel Spreadsheets

29 COMMENTS

  1. Hi @Pedro 13/08/2020 at 15:57, you can use the solution below:

    Considering the example

    A1: Sérgio Ambrósio (1988-02-08)

    In B1: = STORAGE (LEFT (A1; SEARCH ("("; A1)))
    Em C1: =DATA.VALOR(EXT.TEXTO(A1;PROCURAR(“(“;A1)+1;10))

  2. 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?

  3. 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.

  4. Is it possible to make a logical test of an SE () with the right function ()?

  5. =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

  6. Hi Erik, yes you can, just use the right function to see the number searched and compare with the value of the formula

  7. 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.

  8. Hi Leonardo, just creating auxiliary columns to transform the 08 number into 08 text and then using LEFT on it

  9. 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.

  10. 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)

  11. And how do you get the numbers before the hyphen? for example: 02546- refrigerator, I'm breaking my head to solve this.

  12. 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.

  13. 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.

  14. 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

  15. Hi Max, I believe that it is possible, although I do not remember to have tested

  16. In this case you would need to use more text manipulation functions like FIND, EXT.TEXT together with the LEFT

  17. What if the staff has 3 or more names, how do I get just the last name?

  18. 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

  19. 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

  20. You can enter a range from a single cell. If you put more cells you will have a #VALOR problem

  21. Hello! I liked the blog!
    I have a doubt:
    can I insert a range in the LEFT function, in the TEXT syntax?

  22. I did the 1 part, and I only returned the letter A and not Alexander, why will it?

LEAVE AN ANSWER

Please, write your comment
Please enter your name