Need Leading Zeros

[1kmonro]

I have a text column of 7 to 9 digits in length. Some items have 2 leading zeros and some do not. My formula in other areas will not work on the items without the 2 leading zeros. I know how to Custom Format to get them BUT some of the 5 digit items look like this (2BE21, 25E51, etc.). If I use 0000000 in custom format it coverts those into Scientific. I can't seem to find a way around it. Please help.

 

[bunbury]

either insert a new column and covert the digits to text using the following formula, or embed the formula into the other formula that's giving you trouble.

=TEXT(A1,"000000000")

 

[1kmonro]

I am learning - but it's such a slow process sometimes. Here is the formula that's giving me fits. I can't figure out where to add this and the problem is that I can't add a column due to management constraints.

=IF(ISNA(VLOOKUP(C536,'Payment Info'!A$2:D$10000,4,FALSE)),0,VLOOKUP(C536,'Payment Info'!A$2:D$10000,4,FALSE))

Would appreciate your advice.

 

[barry houdini]

Are you saying that that formula returns a 7 to 9 character string and you always want it to be 9 characters, padded with leading zeroes if necessary?

Try

=IF(ISNA(VLOOKUP(C536,'Payment Info'!A$2:D$10000,4,FALSE)),"000000000",RIGHT("00"&VLOOKUP(C536,'Payment Info'!A$2:D$10000,4,FALSE),9))

 

[bunbury]

I'm guessing the value in column C has 7 or 9 digits, but payment info worksheet has all 9 digits with leading zero for account numbers? which is causing the issue?

=IF(ISNA(VLOOKUP(TEXT(C536,"000000000"),'Payment Info'!A$2:D$10000,4,FALSE)),0,VLOOKUP(TEXT(C536,"000000000"),'Payment Info'!A$2:D$10000,4,FALSE))

 

[1kmonro]

Barry,
C536 needs to be 7 characters (5 text, 2 leading zeros), but when I try your solution (and I used 9 just because you had that) I get all zeros in the destination field. The destination field looks up data related to C536 and returns a dollars and cents answer.

 

[1kmonro]

Bunbury,
That didn't work either but I'm not certain why not. C536 (and all the others in this column) sometimes has a 5 digit code and sometimes a 7 digit code. The VLOOKUP sends it to the Payment Info worksheet where it looks for an exact match of the code. The Worksheet always downloads with 7 digits in each field. So those codes with only 5 digits get 2 leading zeros. That could still be compensated for easily with formatting IF it weren't for the fact that some of the 5 digit codes have E smack dab in the middle of them. If I try to format using 0000000 I get a scientific number and that sends me into a No Match position.

 

[bunbury]

This should work now, I had it set a 9 digits. just take out 2 zeros:

=IF(ISNA(VLOOKUP(TEXT(C536,"0000000"),'Payment Info'!A$2:D$10000,4,FALSE)),0,VLOOKUP(TEXT(C536,"0000000"),'Payment Info'!A$2:D$10000,4,FALSE))

12345 will show as 0012345; while 1234567 will be 1234567

 

[barry houdini]

But TEXT function won't add leading zeroes to an alphanumeric string like 2BE21,

I think you'd need to do it using RIGHT function like my original suggestion, i.e.

=IF(ISNA(VLOOKUP(RIGHT("00"&C536,7),'Payment Info'!A$2:D$10000,4,FALSE)),0,VLOOKUP(RIGHT("00"&C536,7),'Payment Info'!A$2:D$10000,4,FALSE))