How to Trim the Last Digit Off a Number in Excel 2013

I spend a good amount of time working with UPC numbers in Microsoft Excel spreadsheets. One common situation that I encounter is when I have a complete UPC number, but need to remove the last digit of the number. This is typically referred to as a check digit, and is automatically generated based upon the rest of the numbers in the UPC. While this is a simple task with one or two UPC numbers, it is very tedious when dealing with hundreds or thousands of them.

Fortunately Excel has a formula that will automatically remove the last digit of a number. Simply type the formula into a different cell, and your end result is number minus its last digit. That formula can then be copied down to fill the rest of the cells in a column.

 

Remove the Last Digit from a Number in Excel 2013

The steps in this article are going to show you how to use a formula to remove the last digit from a number in a cell of your Excel spreadsheet. This means that a cell containing the number “1234” will be trimmed down to “123.” While we will be focusing specifically on removing one digit, you can choose to remove as many digits as you would like by changing the last part of the formula.

Step 1: Open the spreadsheet containing the cell that you wish to trim.

 

Step 2: Click inside the cell where you wish to display the number that has had its last digit removed.

select a cell

 

 

Step 3: Type the formula =LEFT(A1, LEN(A1)-1) into the cell, but replace each A1 with the location of the cell that contains the number for which you want to remove a digit. You can then press Enter on your keyboard to calculate the formula.

trim a digit from a number in excel 2013

 

 

You can then copy the cell containing the formula and paste it into any other cell that contains a number you wish to shorter by one digit. For example, in the image below, I am pasting the formula into cells B2 – B9.

paste the formula into other cells

 

 

If you wish to shorten a string of characters by more than one digit, then change the number “1” in the formula to the number of digits that you want to remove. For example, if I wanted to remove 4 digits, I would change the formula to =LEFT(A1, LEN(A1)-4).

This formula can also be used to remove characters from text strings as well.

Tip – If you are also using this formula for UPC reasons, and your number is displaying as scientific notation, then you probably need to change the formatting. This article will show you how to see the formatting applied to a cell, then you can switch to Number or Text formatting instead.

solveyourtech.com newsletter
vintage t rex t-shirt

Disclaimer: Most of the pages on the internet include affiliate links, including some on this site.