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

#### dixon1983

##### Board Regular
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

### 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
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

##### Board Regular
sorry I mean concatenate with 9-len zeros.

#### GlennUK

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

#### dixon1983

##### Board Regular

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

Thanks

#### pgc01

##### MrExcel MVP
Hi dixon1983

=--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

Yes I would do as said by pgc01

#### GlennUK

##### Well-known Member
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
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

##### Well-known Member
Great, so Pcg's formula will do it unchanged.

Replies
4
Views
99
Replies
1
Views
242
Replies
2
Views
187
Replies
11
Views
198
Replies
5
Views
198

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.

### Which adblocker are you using?

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

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