How do I remove trailing zeroes in Excel 2010? I want to remove the last two zeroes from the entire column. For example I want 703100 to look like 7031. Thanks!

There are multiple ways to remove 2 trailing zeros. It depends on whether you want to leave the new values in the original column or move them elsewhere.

To leave them in the same column, follow these steps:

1 - Select an empty cell and enter 100

2 - Copy the Cell

3 - Select your original values

4 - Right Click, choose Paste Special

5 - Check the Divide box

6 - Click OKTo have them end up someplace else, any of these methods, as well as many others, will work:

Divide by 100:

=A1/100

Use the LEFT function. Since LEFT returns a text value, multiply the result by 1 to turn it back into a number if required.

If the original value will always be 6 digits, extract the first 4:

=LEFT(A1,4)*1

If the original value will vary in length, return just the digits to the LEFT of the last 2:

=LEFT(A1,LEN(A1)-2)*1

After performing any of the "new location" methods described above, you can replace the original values as follows:

1 - Select the new values

2 - Copy

3 - Select the original values

4 - Right-click, choose Paste Special

5 - Check the box next to Values

6 - Click OK

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Thanks much for the info! That will work.

Just curious...which method are you referring to when you say "That will work"?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Sorry, what I meant to say was that I solved the issue by copying the cells in the very next column. I didn't realize they were the same numbers, before I posted the question. Does that clarify my answer?

If you want to get rid of ANY number of trailing zeros (chrisman9076 just wants to delete 2 of then) in a non-decimal value - i.e. simply get rid of all the zeros after the rightmost non-zero digit, this is the approach I use (number to be shortened is in cell G10): =(10^(LEN(RIGHT(VALUE(CONCATENATE("0.", G10)),LEN(VALUE(CONCATENATE("0.", G10)))-FIND(".",VALUE(CONCATENATE("0.", G10)))))))*VALUE(CONCATENATE("0.", G10))

I know, there's too many brackets in there.

How it works is this:

1) Your original number is e.g. 3500 and you want 35

2) This value is concatenated using CONCATENATE with a leading "0." to make it look like a decimal. This returns 0.3500.

3) CONCATENATE always returns a result as text, regardless of whatever you concatenate. The VALUE function is applied to convert your 'text' 0.3500 to a 'number' 0.3500

4) The default 'General' number format in Excel automatically reduces this to 0.35

5) Next step is to get 0.35 back to 35. You need to multiply by the correct factor of 10 to move the decimal place.

5) To do this, calculate the number of digits after the decimal point. This returns 2 in this case.

6) 10^2 = 100

7) Multiplying 0.35 by 100 gets you back to 35.

It's a little convoluted, but it works. An alternative route could be, after step 4, to convert the 'number' 0.35 back to 'text' 0.35, delete the "0." part using the RIGHT function to get you 'text' 35. If you need 35 as a number, use the VALUE function again to get 'number' 35.If your original number happens to be a single zero, #VALUE! will be returned, so be prepared for that.

message edited by awdargan

I should add, if you use the alternative route, the simplest way to convert a number to text is, oddly enough, to use the CONCATENATE function. Just enter one argument for concatenate. Using the TEXT function will prompt you for a format. CONCATENATE will just convert it to text as it appears.

Just a comment on your interesting method for removing trailing zeros... Another method for converting a "text number" to an actual number is to multiply by 1 or add 0.

Replacing the VALUE functions and their associated parentheses with *1 results in a shorter formula:

=(10^(LEN(RIGHT(CONCATENATE("0.", G10)*1,LEN(CONCATENATE("0.", G10)*1)-FIND(".",CONCATENATE("0.", G10)*1)))))*CONCATENATE("0.", G10)*1

Further, using the Concatenation Operator instead of the CONCATENATE function, shortens the formula even more:

=(10^(LEN(RIGHT(("0."&G10)*1,LEN(("0."& G10)*1)-FIND(".",("0."& G10)*1)))))*("0."& G10)*1

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll