Using zeros as placeholders

  • Thread starter Thread starter Seth
  • Start date Start date
S

Seth

Is there a formula to add zeros (or any character) to a
cell with a fixed width?
I am currently using a formula, =A1&REPT(" ",20-LEN(A1)),
which will extend the character length of a cell to 20,
this works for items that are left justified and entries
that contain fewer than 20 characters just have spaces to
fill up the remaining charaters.
What I am looking for is a way to add characters to the
front of an entry and still enforce the fixed length.
Example:
A cell contains 123456789
I would like the cell to have a fixed length of 20
chracters, and the "missing" characters are filled with
zeros placed in front of the entry. To look like this.
00000000000123456789
I am working with entries between 6 and 10 characters in
length.

Thank you in adavance for your time.
 
Hi,

You might try custom format "00000000000000",
but I'm not sure that's what you really are
after....

jeff
 
I don't see why you can't use the current formula, just modified..
unless
you do not want to have an intermediate column.

So something like: =REPT("0",20-LEN(A1) )&A1

Even with a function you'd still have to call it from the cell o
reference it..
so =MYFUNC(A1) or =MYFUNC(123456789)

and if you wanted to do it that way, you would add this code in a ne
Module:


Code
-------------------

Function MyFunc(val As String, width As Integer, fill As String) As String

MyFunc = String((width - len(val)), fill) & val

Debug.Print MyFunc

End Function

-------------------


and if you called it like this:

debug.Print myfunc(123456789,20,"0")

you would get:

00000000000123456789


Obviously, you can see the similarity ot the actual function.

I hope this helped and if not, then I don't think I understood you
question.

Fran
 
Back
Top