Need numbers 7 digits long (some are 5, some are 9)

[dixon1983]

Hi,
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.

 

[wavemehello]

how about this logic? convert the value to string, then extract the lenghth and if it is less than 7 digits concatenate with 2 zeros and convert the whole to value again.

 

[wavemehello]

sorry I mean concatenate with 9-len zeros.

 

[GlennUK]

Do you want the results as text or real numbers ( lookups treat them differently even though they look the same )?

 

[dixon1983]

Whichever is easier Glenn. The databse values can be either so the easiest method will do.

Thanks

 

[pgc01]

Hi dixon1983

Please try
=--LEFT(A1&"0000000",7)

for a number. If you want the result to be a string delete the 2 minus signs.

Hope this helps
PGC

 

[wavemehello]

Yes I would do as said by pgc01

 

[GlennUK]

Whichever is easier Glenn. The database values can be either so the easiest method will do.

Thanks

What do you mean - database values can be either? Does that mean you haven't got the database yet? Or do you mean it's a mixture of text and real numbers?

 

[dixon1983]

Thanks for your help Pcg. Ive done that and it works.

Glenn - apologies for my earlier post, i got confused, the database is in real numbers so it would need to be in that format.

 

[GlennUK]

Great, so Pcg's formula will do it unchanged.