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

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175
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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

wavemehello

Board Regular
Joined
Jan 24, 2006
Messages
221
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.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Do you want the results as text or real numbers ( lookups treat them differently even though they look the same )?
 

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175

ADVERTISEMENT

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

Thanks
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

Board Regular
Joined
Jan 24, 2006
Messages
221

ADVERTISEMENT

Yes I would do as said by pgc01
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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

Board Regular
Joined
Apr 23, 2005
Messages
175
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.
 

Forum statistics

Threads
1,136,517
Messages
5,676,314
Members
419,619
Latest member
jalme

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top