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!

• Very helpful, thank you!

• Thanks Jay, glad you liked it.

• 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!!!

• “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…

• 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:

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

• I am No Judge

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

• Faiz

I can’t make this formula work, I type : =LEFT(A1,LEN(A1)-4) and it gives error everytime.

My A1 column has the following data: ELDB-AK-S-42228-001

Why isn’t this working?

• Christopher Derrell

If you’re doing it in Google sheets, it has this weird thing where you may have to use a semicolon instead of a comma. i.e =LEFT(A1;LEN(A1)-4).

I don’t know the reasoning behind it.