Add zeroes as Place holders

ambra19

New Member
Joined
May 9, 2003
Messages
40
I have encountered a problem, where I have several columns of invoice numbers that were entered into Excel. The columns were in Number format and as such a 001125 became 1125.


The field is 12 characters long. I need the macro to go through each record and if the number of characters in the cell is less than 12, to add as many 0 (zeroes) as necessary at the front of the number (which will be in "text" format).

1234 becomes 000000001234
12345678 becomes 000012345678

If anyone can help me write a macro for this, it would be greatly appreciated.
Thanks.
ambra19
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hi,

Can you not just apply a custom format to the data concerned?

Not really my forte, but "000000000000" seems to do what you want.

HTH
 
Upvote 0
Is there a way to do this with text and spaces as well?

Heres my code

=TEXT(CU5, )&CV5&CW5&CX5&CY5&TEXT(CZ5,"00000")&DA5&REPT("0",3-LEN(DA5)) with 6 spaces in that first part

It keeps bringing up

AMBA 140322P00032500

with "AMBA " having 7 characters instead of 6. Is there a way to have it only have 6 characters?
 
Upvote 0
It is not quite clear what you are trying to achieve. Can you elaborate? And "AMBA " has 5 characters?
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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
Back
Top