Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Writing an if/than statement in excel

Apr 11, 2007 1:47AM PDT

I think this is what I want to do...

I'm using Office 2003.
What I'm trying to accomplish is this:
I have a column with a string of numbers and in some of the cells there is a leading zero missing, my list is extremely lengthy so adding this in manually or using a concatenate function is proving to be extremely time consuming and tedious.
I'm wondering if/how I can write a statement or formula to say "if the cell contains a string of 11 numbers, add a leading zero or a zero to the left of the string. if the string of numbers contains 12 numbers than leave as is." Can anyone tell me how I would do this?

Thanks,
Andrea

Discussion is locked

- Collapse -
Numbers? Use this.
Apr 11, 2007 1:53AM PDT
- Collapse -
removing formatting and maintaining string length
Apr 11, 2007 2:18AM PDT

Thank you.

This works in adding the leading zero however, how can I get this to show up as the full value in the cell? When I click on a cell where leading zeros were added, it still displays the number in the bar above as not having the leading zero however in the cell itself it does show this correctly? The reason I need this to happen is because I have several other steps that I'm trying to accomplish and when I try to copy and paste the number in the cell it will only paste as the number w/o these leading zeros unless that formatting is continuous in all spreadsheets, however this doesn't work for other things I need to do. It there any way around this?

Thanks again!
Andrea

- Collapse -
The only way would be to not use numbers.
Apr 11, 2007 6:23AM PDT

So the answer is to has a column next door to write some formula to convert it to a string and add the leading '0' character. Since any number to string would drop a leading zero it appears there is no if.

Bob

- Collapse -
Not really clear what you want.
Apr 11, 2007 6:34AM PDT

All I can say is that you can use a custom numeric format 000000000000 in a cell to force any number up to 12 digits to show in 12 digits with leading zero's.

There's a difference between strings and numbers. Each has their own pecularities and formats and operators and functions. You don't SEE the difference, but is surely IS there. Once you realize that, and have the necessary basic knowledge of Exdel (as I said, formats and functions and operators), I'm rather sure you can do what you want.

Hope this helps.


Kees

- Collapse -
The way I've done it, (needs preparation).
Apr 11, 2007 9:11PM PDT

I don't know if this will work for you.

If your long column of numbers is sacrosanct and you don't want to lose them, then you will need to leave them alone and use other columns to work on the numbers.

EG;

If column B holds all the numbers, (starting at B7).

Col C holds the formula;
=IF(LEN(B7)=11,("0"&TEXT(B7,"0")),B7)

Use the "Fill handle", (the small black cross at the bottom right of each cell), to drag the formula down to the bottom of Col C where Column B ends.

Then copy all of Col C and Paste Special > Values into Col D.

Col D should hold all the numbers with leading zeros where the length was 11, or no leading zeros where the length was 12.

You can work on the contents of Col D as you would any numbers. However, any calculation on any of the Col D numbers that include a leading zero will most likely lose that leading zero in the result.

LEN works on numbers as well as text apparently. So LEN B7 will return TRUE if B7 is a 11 figured number, or FALSE if not. If TRUE, then a "0" is added to the front of the TEXT conversion of B7. If FALSE, then B7 is used.

I hope that helps.

Mark

- Collapse -
formula to add leading zeroes
Apr 15, 2007 11:13PM PDT

your formula would look something like this: =if(+a1<100000000,"000"&a1,a1<1000000000,"00"&a1,a1)

put your smallest number 1st in the formula, and work your way to the largest number