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 cellA1is: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.
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!
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.
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:
That link didn’t display correctly: http://docs.oracle.com/cd/E19455-01/806-0169/overview-9/index.html
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)?
Excel(lent)!
yes, but what if you want to strip the last 4 charactes of the string REGARDLESS of the string length
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))
can we use RIGHT function here ?
Hey thanks for this! How do I remove first AND last characters from a string?
Best,
James
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
This worked great!! Thanks :)
Great !!!
There are 6 characters in the word “Income”, not 5.
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?
=CONCATENATE(RIGHT((A1), 2), ” “, LEFT(A1, LEN(A1)-2))
=RIGHT(A1,2)&” “& LEFT(A1,LEN(A1)-2)
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?
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.