How do i add figures in excel based upon a condition?

[finance123]

I'm trying to format security CUSIPs (must be 9 digits) in excel based upon the output a systems is giving me so that i can run vlookup formulas. The problem is that the system output will cut off the zeros if the cusip starts with zero. Ex: cusip 00817Y108 will be displayed as 817Y108. Sometime the system will cut off 1 or 2 zeros. I want to write an excel formula that will add a zero to the beginning of a cusip depending on how many zeros were cut off. So if i run a len formula on the cusip and it displays 7 or 8 i want the formula to add a zero to the beginning of the cusip so that it equals 9 digits.

[Domski]

Try: =REPT("0",9-LEN(A1))&A1

Dom

[finance123]

Thanks for the post, I just added your formula to my if statement and it worked!


=IF(F2=0,E2,CONCATENATE(REPT("0",9-LEN(E2))&E2))

[Domski]

No need to use Concatenate, the & does the same trick.

Dom