Add zeroes as Place holders

[ambra19]

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

 

[dpucane]

It is not quite clear what you are trying to achieve. Can you elaborate? And "AMBA " has 5 characters?

So I'm making a code that forms option symbols in OSI format. The code takes all of the different parts needed and combines them into one format, each with a different number of bytes required.

I have all of them ready to go except the first part, the equity symbol, which needs to be in 6 characters. For example, AAPL should read "AAPL__" with two spaces then the next part, while BWC would read "BWC___" with three spaces then the next part

Here's my code right now

=TEXT(CU5,"______")&CV5&CW5&CX5&CY5&TEXT(CZ5,"00000")&DA5&REPT("0",3-LEN(DA5)) with 6 spaces where those underlines are. However it keeps coding it as

AMBA___140322P00032500 with 3 spaces when there should only be two

<tbody>
</tbody>

 

[stefankemp]

So why not just use CU5&REPT(" ",6-LEN(CU5))?

 

[dpucane]

that's bringing back a value error

 

[stefankemp]

What is your formula now? It should be:

=CU5&REPT(" ",6-LEN(CU5))&CV5&CW5&CX5&CY5&TEXT(CZ5,"00000")&DA5&REPT("0",3-LEN(DA5))

 

[dpucane]

What is your formula now? It should be:

=CU5&REPT(" ",6-LEN(CU5))&CV5&CW5&CX5&CY5&TEXT(CZ5,"00000")&DA5&REPT("0",3-LEN(DA5))

that's what I have in there and it's giving me a #VALUE error?

 

[stefankemp]

I think I know what the problem is. I suspect the entry in CU5 is "AMBA" followed by three spaces. Replace CU5 with TRIM(CU5) in my formula.

 

[dpucane]

I think I know what the problem is. I suspect the entry in CU5 is "AMBA" followed by three spaces. Replace CU5 with TRIM(CU5) in my formula.

Now that's only giving me 5 digits

 

[stefankemp]

What is in cell CU5? Can you send me the workbook?