Removing the last number of characters from a string in Excel and Google Spreadsheets

Sometimes you want to remove some number of last letters from a string in your spreadsheet. For this you can use a very handy function: LEFT()

LEFT() returns the first X characters in a string, based on the amount of characters you specify. Like: LEFT(text,num_chars)

Say you want to remove the last 3 characters from a string in cell A1 you can use:

=LEFT(A1,LEN(A1)-3)

Or more generally:

=LEFT(A1,LEN(A1)-[number of characters to remove])

How does this LEFT() function work?

Say for example that in cell A1 you have a string “IncomeMAX” and you want to remove the last three characters (“MAX”):

  • First, LEN(A1) returns how long the string in cell A1 is: 8 characters
  • Then it subtracts 3, in order to leave out the last 3 characters: 8-3=5
  • Then LEFT() makes sure that only the first 5 characters from the left are returned: Income

That’s it! As always, let me know in the comments when you have any questions!

  • Jay

    Very helpful, thank you!

  • James Clark

    Hello!

    I created the following formula, my goal was for the sheet to delete any lines that had any user ID that started with numbers (Hopefully it would read that the first character in line K was a number and then delete the whole row). Any idea how I can transform it to do so? The above looks close, I think my largest issue is integration.

    With ActiveWorkbook.Worksheets(“MTD”).Range(“K:K”)
    For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 1 Step -1
    With .Cells(i, 1)
    If CStr(.Value) Like {1,2,3,4,5,6,7,8,9,0} Then .EntireRow.Delete
    End With
    Next i
    End With

    Thanks!

  • Lou

    Hi, I have a question. I want to remove the last character from a long column of numbers, which is a zero. E.g (2171000 -> 217,100) and I have no idea how to do it!!!

    • Jay

      “LEFT() returns the first X characters in a string, based on the amount of characters you specify. Like: LEFT(text,num_chars)”

      The example on this page is to automate a variable amount of characters, but say you know that your dataset consistently has 7 characters (including the extra 0). In that case, you would want to return only the first 6 characters (leaving off the last 0).

      Make a column next to your data, and for that cell you would extract the characters of your original column:
      “=LEFT(original,6)”

      For a variable amount, see the “How does this LEFT() function work?” example at the bottom.

      • Lou

        Thank you! The problem is, if the column on the left is 20,000 it will convert it to 2000 and eliminate the comma‚Ķmy values refer to seconds so 20,00 seconds are not the same as 2000 seconds…

        • Jay

          Oh, like decimal places? It sounds like there is a confusion between how the numbers are displayed. My guess would be that Excel/Google Sheets’ defaults would be UK/US usage of “.” for decimals, but the thousands separators “,” are being read by you as decimals. List of decimal syntax differences between countries: (http://docs.oracle.com/cd/E19455-01/806-0169/overview-9/index.html)

          If this is the case, it would simply be a matter of finding a setting for how many decimal places are displayed. But if your data is formatted without thousand place separators (“20000 seconds” instead of the desired “20.000 seconds”), then perhaps you could make an extra column to multiply by 1000 or divide by 1000, or whatever factor is necessary to equalize your data.

          After a search for “excel change decimal format,” it looks like it would really depend on your version of Excel and your computer settings. In Excel 2010, I have this button to determine how my formatting is set up:

        • Jay
  • jojo

    What if I have a database with lots of names of varying lengths – how do I remove the last character only if it’s a special character (decimal, or anything but letters)?

  • nyITguy

    Excel(lent)!

  • dosmastr

    yes, but what if you want to strip the last 4 charactes of the string REGARDLESS of the string length

    • EstimatedProphet

      That’s what the posted formula does. The only normal case in which the following formula:

      =left(A1,len(A1)-4)

      ..would NOT do that, is if the length of the string is 0,len(A1)-4,0))

  • VaIBHAV

    can we use RIGHT function here ?

  • Jamie Carmichael

    Hey thanks for this! How do I remove first AND last characters from a string?

    Best,

    James

  • Jemini Benhur Baucan

    What if i want to remove 2 last commas

    14 Lower Street, Kettering, Northamptonshire, NN16 8DH
    i want to remove Northamptonshire, NN16 8DH (last 2 commas with words)
    14 Lower Street, Kettering will only remain

    note: there are 4 to 6 commas in my columns

  • HappyFeet

    This worked great!! Thanks :)

  • Lakhan Garg

    Great !!!

  • MannyYunker

    There are 6 characters in the word “Income”, not 5.

  • Andrew Kinsella

    Thanks for this, its helped out loads. I need to go a bit further and take the letters I’ve removed from the end and add them to the front like this: 1# of 10ZS

    to ZS 1# of 10, how would I do this please?

    • Jake

      =CONCATENATE(RIGHT((A1), 2), ” “, LEFT(A1, LEN(A1)-2))