I have a column of numbers that I use for Vlookup. These numbers need to be 7 digits long in order to reference the database correctly. The problem I have is that some are 5 or 6 digits long and some are 8 or 9 digits long.

Is there a way to add a zero(s) to the numbers that are 5 or 6 digits long and take zeros off the end of numbers that are 8 or 9 digits long.

E.g 45897 would become 4598700

658974500 would become 6589745

452365 would become 4523650

etc etc

Thanks for any help you can offer with this.