Comments on: Excel LEFT function with formula examples

The tutorial shows how to use the LEFT function in Excel to get a substring from the beginning of the text string, extract text before a certain character, force a Left formula to return a number, and more. Continue reading →

Table of contents

Comments page 2. Total comments: 110

  1. Kindly help me,

    I want to find a formula for if the cell starts with some character, I want to return some text, if it starts with something else I want to return something else. Like this I want to apply this to multiple starts with values in cells. How to do it.

  2. Hi, can you please let me know how do I combine LEFT function formula that would pick the 1st 3 characters of a word in the next column and add a preset 2 letters before then a sequential auto number after, the result = SI.ACC.0000 where ACC is picked up through the LEFT function & SI is always static at the beginning then the number is automatically generated.

    I hope I've explained this well. Thank you

  3. ASADIHXGJ-11053wNIeXuYGl9h354 >>>>>> XGJ
    CABW-9647HabKVrAxDTYg205 >>>>>>>>> ABW

    Can you please help me with this?

  4. Hi there.

    Thank you for the clear explanation on the left function. May I ask you to help me out on this one. If I want to omitted the company code in my data, in this case "A050" and just want the company name "AC Property Holidng Pty Ltd". Example as follow:

    A050 - AC PROPERTY HOLDINGS PTY LTD

    How could you still embedded with the left function in this exercise? Could someone assist me on this.

    Thank you kindly.

  5. In sheet 1, I have two columns Named "WORD" (which contains one word in each cell), and "ABBRIV" (that contains the abbreviation of the "WORD" column), In sheet 2 there is a "String" column with a string of words, and a column for the formula.
    This formula should compare the "WORD" with the "String" columns, and find the common word, and return the abbreviation equivalent to the "WORD" in the "ABBRIV". Thanks in advance

  6. E F

    400-100-230 - F11 [A] - ITEM 11 400-100-230 - F11 [A] - ITEM 11.pdf
    400-100-230 400-100-230.pdf

    In column F I have a directory listing of pdf files. In column E I have used LEFT function to remove the file extension. Now I want to use an IF function if there are less than 15 characters in column F, to return an empty cell in Column E. Can anyone help
    I've tried using =IF(LEN(F14>20),LEFT(F19,LEN(F19)-4),0), but it just returns the same value in the cell as shown "400-100-230"

    1. Hello!
      Please try the following formula:

      =IF(LEN(F14)>20,LEFT(F14,LEN(F14)-4),"")

      This should solve your task.

  7. I need to select all characters to the left of the second (or final dash ) '-'

    AAA-B900-42 data
    AAA-B900 desired result

    The following is sort of working, but is leaving off the final one or two characters in each case:

    =LEFT(A2,LEN(A2)-FIND("-",A2))

    All suggestions appreciated..

    1. Hello!
      Please try the following formula:

      =LEFT(A1,SEARCH("-",SUBSTITUTE(A1,"-","#",1))-1)

      Hope this is what you need.

      1. use this formula,

        =LEFT(A1,FIND("-",A1,FIND("-",A1)+1)-1)

  8. I want to get only text from cell expecting any formula to get it for large list..
    Cell value >>> Result
    ABC123qw1234ss >>> ABC
    ABCDEF1234bbb>>ABCDEF
    ABCD123 >>> ABCD
    in above example only left text extracted from given cell I used LEFT(A2,3) for first it is not generalize every time I have to change length.
    Any optimal solution will help

    1. Hello!
      To extract text up to the first digit, use the formula

      =LEFT(A1,MIN(IFERROR(SEARCH({1,2,3,4,5,6,7,8,9,0},A1,1),10000))-1)

      Hope this is what you need.

  9. Hi Everyone!

    Good Afternoon!

    Please help me.

    I want to create a formula in excel.

    The formula must be If the cell contains "3037","3032","3092","3050", the answer must be the first 10 characters from the left of that cell.

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =IF(SUM(--(D1={3037,3032,3092,3050})) > 0, LEFT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),10),"")

      I hope I answered your question. If something is still unclear, please feel free to ask.

  10. Hello,

    I have numbers begins with zeros on the left, when I use the LEFT function, excel ignores them and brings the digits after the zeros.

    So how to make this function considers the zeros as numbers?

    Thanks

  11. Hi guys,
    I am really struggling with splitting a 12 character text in 4 in the same cell , can someone help me with a formula please.

    ex: 1234567890AB in 123 456 789 0AB.

    Thank you

    1. Hello!
      To insert a space after every third character, use the LEFT and MID functions:

      =LEFT(A2,3)&" "&MID(A2,4,3)&" "&MID(A2,7,3)&" "&MID(A2,10,3)&" "&MID(A2,13,3)&" "&MID(A2,16,3)&" "&MID(A2,19,3)

      I hope my advice will help you solve your task.

  12. Hi there,
    I want to use the left function to extract a month from some text. I have a 4 digit number like this: 0401 with 04 being the month. I want to return APR in the cell. I have tried to use =TEXT(LEFT(B1,2),"mmm") but get "Jan" back when I want "Apr". Is there a way to do this without doing 2 separate formulas?
    Cheers.

  13. Hello Folks,
    I used the following formula from your website (thank you so much for handy tips):
    =LEFT(B8, SEARCH("·",B8)-1)
    What I used it for was to separate out characters prior to a "." in a word.
    Example: an·rufen ; ein·laden ; fern·sehen ; schmecken ; schützen
    Some words do not have this "."
    I end up with the letters before the dot in my adjacent cell (fantastic) but if there is not "." then I end up with "#VALUE!" Is it possible to have the cell 'blank' if the "." is not there?
    I realise that after it is all done and dusted I still have to copy and paste values. But this saves me a lot of work.
    Is this possible?
    Thank you kindly

  14. Hi,
    I take my blood pressure each day (Systolic/Diastolic), each time I take 3 readings to get the average and enter them separately into each cell:-
    A1: 152/97
    A2: 137/97
    A3: 135/96
    I would like to use Excel to automatically extract the Systolic (the three numbers before the "/") add them together and divide by 3, to get the average - result will be populated in cell A4.
    The same with the Diastolic - take the two numbers after the "/" add together, divide by 3 and enter the results in A5.
    I tried using Len, but can't work out how to use it with multipole cells - can anyone help?

    1. Hello!
      The first three digits can be extracted using the LEFT function.

      =LEFT(A1,3)

      The last two digits can be extracted using the RIGHT function.

      =RIGHT(A1,2)

      To convert from to a number, add a double minus before the formula:

      =--LEFT(A1,3)

      I hope it’ll be helpful.

  15. I would like to separate the TB, GB, PB, MB, KB, etc. from cells in a column see below which contain a mask format (e.g. #,##0.0 "TB").
    46.4 TB
    5.9 TB
    1.9 TB
    423.8 GB
    188.0 GB
    188.0 GB
    60.9 GB
    60.2 GB
    56.4 GB
    37.5 GB
    36.4 GB
    1.1 GB
    1.3 MB
    1.3 MB
    16.0 GB
    16.0 GB
    4.5 GB
    4.0 GB
    2.1 GB
    1.3 GB
    430.6 MB
    191.3 MB
    2.5 GB
    1.7 GB
    1.2 GB
    617.5 MB
    608.7 MB
    571.3 MB
    571.3 MB
    180.7 MB
    34.6 MB
    3.8 MB
    Just about everything I have tried fails. We are wanting to sort the rows (603,000 rows) by file size.

    1. Hello!
      If I understand your task correctly, to convert 1.9 TB, 423.8 GB to MB, you can use the formula LEFT and INDEX+MATCH:

      =VALUE(SUBSTITUTE(H2,RIGHT(H2,2),"")) * INDEX({1,1024,1048576},MATCH(RIGHT(H2,2), {"MB","GB","TB"},0))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. I have the same question, but I want my results in 2 columns with number value and units.
        Can I use the Left fx to split the invisible units to a new column?
        Thank you.

  16. Hi,
    I have almost 99 numbers in one cell & there is no space in those numbers & i want to segregate those numbers in 11 cells by distributing in 9 digit. Can anybody help me?

    Thanks

  17. Hello, if I have a string such at 6d 23h 17m 33s What wat could I break down the amount of numbers left of the D?

  18. how to split text which has . (dot) within

  19. I'm trying to create automatic referencing using LEFT example
    I have sheets labelled "Jan, Feb, Mar" etc.

    But my columns have the full name of months: "January, February, March" etc.
    To reference a cell in another sheet I would use =Jan!A1

    So I used left to obtain the first three letters and then used & to join it to the remainder of the formula as follows:

    =(LEFT(B1,3)&"!A12")
    If I use above formula to reference a cell in sheet January cell A12.
    Instead of getting the cell contents in Sheets January A12, I just get result of the formula itself, so it only goes one degree.... I only get Jan!A12.

    I want to avoid using Indirect if possible because it would mean an extra column somewhere.

    Any help here on how to make this work?

  20. If I have series number like this:
    3200
    3200/01
    3660/02
    3500/06/08
    How can I use the left function, so it can only catch the four and seven characters when I drag the formula?
    Thank you

  21. I want to split a 6 digit number in to 3 separate columns for example
    712325 = 71 23 25
    using formula. kingly reply on this...

  22. I am wanting to right a formula to say if when a cell with the left formula in contains certain text then true otherwise false however it is returning false when it should be true.
    eg.
    Cell B2 = ALMG 0.12 (this could change to Cu, Alup, Cusn etc)
    Cell B3 contains formula =LEFT(B2,5) to return only the text in this case "ALMG"
    I want cell B4 to look at if cell B3 contain the text "ALMG" then "true" other wise "false".
    I am using this formula =if(B3="ALMG","T","F")
    In my eyes it should be returning T for True but it keeps returning F for False, why is this?

  23. in the example below. how do i extract just the numbers on the left and leave the number on the right with the text?
    0941KONONGO 2

  24. If my data (number) has consistency:
    (1) Problem Descriptions
    2. Problem Descriptions
    4 Problem Descriptions
    5, Problem Descriptions

    How can I just return a number? Please advise.
    1
    2
    4
    5

    1. inconsistency***

  25. Hi. I have a small query... If First Name has hyphen in it, how can I just make the first letter capital? For example:

    First Name Last Name
    John-Mike Sylvester

    I want the name to be: John-mike sylvester

    I have tried PROPER and LEN functions but not able to achieve it? Any suggestions please?

  26. I have this type of line "1400000172 D.S.ACHARYA" in excel and want to copy first 10 digit in one column and ain another column want name copy than how can i make it with formulas in excel

    1. Hi Ritesh Parmar,
      You can use the left and wright function including Find Function as per below:
      for the 10 digit you use left and find function as =LEFT(C15,FIND(" ",C15)-1) also for the name you use right and find function as =RIGHT(C15,LEN(C15)-FIND(" ",C15))
      I hope this could solve your problem.
      Thank you

  27. I have a lookup reference to an =left output, but it keeps showing an error. But if I type the actual text, the data populates correctly. There are no spaces or anything like that. I tried to convert to a number... Still no. Is there something about the text output that would cause a problem using it as a lookup value?

  28. I want to use the left formula to extract the name of country from the first 1-3 in the phone number column.

    for example:
    A B
    1| 61438223476 |
    2| 972548484847 |
    3| 447724545487 |

    In column A we have the list of numbers,
    I want to be able to return a right value/text, 61 = Australia, 972 = Israel, 44 = United Kingdom:

    A B
    1| 61438223476 | Australia
    2| 972548484847 | Israel
    3| 447724545487 | United Kingdom

    What can I do?
    Thanks

  29. When I use the Left formula to remove the last 4 digits of a zip code in a new column and then try to replace the original column with the reduced zip code, I get the #Value code. The original column is listed as Special - Zip Code and it doesn't help to change to numbers. I just want to remove the last 4 digits of the zip code in a column of expanded zip codes!

  30. Hello,

    I want to sum in a raw (let's say F10:AJ10) numbers and something like this:

    7, 9, 11, I7, 5, I9, 3, 10, I2.

    So I want to sum all numbers and the digits after the "I" (7,9,2). These may appear random, no exactly place.

    1. Dan:
      I found this formula on the Microsoft site. I think it is what you're looking for.
      Where the data is in I29:R29 this is entered in an empty cell as an array. This means, in the formula bar enter this formula then with the cursor somewhere in the formula click CTRL Shift Enter together. This action will tell Excel this is an array and you'll see the curly brackets surrounding the formula.
      =SUM(IF(I29:R29"",IF(NOT(ISNUMBER(--LEFT(I29:R29,1))),--MID(I29:R29,2,256))))+SUM(I29:R29)

  31. Clarification:
    If I have a list of numbers, all with various amounts of digits (some 2, 3, 4, 5, or 6 digits long) and I want them all uniform in format, with a prefix (example below), what formula would I use?

    48492 = P00048492
    23 = P00000023
    600 = P00000600
    110291 = P00110291

    1. Hi Lori

      Please use below formula. I'm Assuming the length should be 9, you change it for your requirement.
      =IF(LEN(B2)<9,CONCATENATE("P",REPT(0,8-LEN(B2)),B2))

  32. If I have a list of numbers, all with various amounts of digits (some 2, 3, 4, 5, or 6 digits long) and I want them all to be uniform and add a prefix (like a P in front) so they are all P00000000, what formula would I use?

    1. You can use Concatenate function.

      =concatenate("P",A1)

  33. I have a column with names

    John Robbins
    Alok singh
    Rohit
    Raghu Prabhu

    if I use =LEFT(A2, SEARCH(" ", A2)-1)

    it is working fine with records 1,2 and 4 but with 3 it is showing an error. how do i incorporate this?

    regards

    Raghu Prabhu

    1. Raghu:
      I think this is what you're looking for:
      Sample ## will show #ERROR because there is no space in that record. So, you might want to add the IFERROR function in front of the formula like this:
      =IFERROR(LEFT(A5,SEARCH(" ",A5)-1),"No Space")
      The IFERROR function will return a message such as "No Space" if the formula would otherwise return #ERROR.
      I suppose you could use this formula:
      =IFERROR(LEFT(A5,SEARCH(" ",A5)-1),"A5")
      which would return the value of A5. If you copy this down the column you will see the A5 will change to the relative cell address.
      Other than this, I guess you'll need to add a space to the record.

  34. OMG, I love you. I could tell the =Left was text but didn't know what to do about it!

  35. I wants to know whether is there any function in excel to limit a cell to one text character only and string completed and cursor automatically moves to other right cell in excel to write in that cell, just as in form filling with one character in one cell and the other in the other cell,

    1. Hello,

      Please try using a VBA macro. We are always ready to help you, but we do not cover the programming area (VBA-related questions).
      You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry we can’t assist you better.

  36. This is very helpful for me.thank you

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)