Remove first character if it is "0" (Zero)

Question

I couldn't get the correct formula in order to remove the leading zero in a column. I built the following, but it returns FALSE instead of the value without zero:

=IF(RIGHT(Z2:Z19497,LEN(Z2:Z19497)-1)="0",MID(Z2:Z19497,2,LEN(Z2:Z19497)))

Hope you can help.

Answer 1

Assuming your data is in Z2 to Z19497, you would want to enter the following formula into cell AA2 then select from AA2 to AA19497 and do Fill > Down (or press Ctrl+D):

=IF(LEFT(Z2,1)="0",MID(Z2,2,LEN(Z2)-1),Z2)

Note: As discussed in the comments, the -1 after LEN is not necessary, as MID will work just fine even if you specify a length that is greater than the number of remaining characters. To reduce typing even further, you could use 999 (or a number that safely exceeds the longest cell) instead of LEN(Z2)-1.

There were a couple problems with your attempted formula:

1. This type of formula only works for one cell at a time, but you included the entire range (Z2:Z19497) instead of just one cell (Z2).
2. Your IF statement has the "if true" portion (MID(Z2:Z19497,2,LEN(Z2:Z19497))) but not the "if false" portion. That's why you end up with FALSE instead of the value in Z2.
3. It's easier to use LEFT than RIGHT for the test condition.

Note that when you copy your formula down, Z2 will automatically advance to Z3, Z4, etc.

Answer 2

There are a couple of issues with your formula.

The first is that you are attempting to apply the formula to a range of cells Z2:Z19497. Instead you need to evaluate each cell individually, checking if Z2 starts with a zero, Z3 starts with a zero, and so on. If you add your formula somewhere like cell AA2 and then fill it down to AA19497, you'll then have a list of values in column AA that do not have leading zeros.

The next issue is in your IF test statement. If we correct your formula to look at one cell, it looks like this: RIGHT(Z2,LEN(Z2)-1)="0". This formula says, tell me if everything but the first character in cell Z2 is equal to 0. See any problem with this? The only time it will return a true is when you have a two digit number in cell Z2 that ends in 0. Otherwise you're testing if a multi-character value is equal to 0, which it cannot be.

Instead, let's check if the first character is equal to 0. To do so, let's use the LEFT function to get the first character. LEFT(Z2,1)="0" Awesome, now we know if the first character is 0 or not.

So let's assume the first character is 0. We want to remove it, but how will we do that? The single cell revision of your current logic is MID(Z2,2,LEN(Z2)), which should work, but is slightly incorrect. For the sake of completeness let's fix it. Since you are removing the first character of cell Z2 you no longer want MID to use the length of Z2, you want it to use the length of Z2 minus 1. Let's rewrite this section of your formula as MID(Z2,2,LEN(Z2)-1) then.

Now on to the situation where your cell in Z2 does not start with a 0. If that's the case, you want the formula to simply return that value. We can do this by simply adding one more comma after the formula's instruction for when the number begins with 0 and inserting a Z2. That sounds a little confusing, but should make sense in the complete formula below:

=IF(LEFT(A1,1)="0",MID(A1,2,LEN(A1)-1),A1)

Once that is entered in a cell, presumably AA2, you should be able to click the little box in the bottom right corner of that cell to have it fill the formula down the column to the end of your data, presumably AA19497

Another option you have if all of the values in your Z column are numbers preceded by zeroes is to simply enter the formula =Z2*1 or =Z2/1 in another cell, AA2 if we're following the same pattern as the rest of this response. This will give you the numeric value of Z in your new column and is much easier to remember and type. Unfortunately it will not work if the values in your Z columns have numbers mixed in with the numbers.