Creating specific number of characters from a variable

[alv7ter]

Hi

I have a list of numbers which vary from 1 to 4 in length but I need them all to be 4 by placing leading zeros in front of those that are 3 and under, eg 1 becomes 0001 and 20 becomes 0020. This needs to be a daily process, problem is the list changes by the day. Trust someone out there knows the answer.

Cheers
Al

 

[SkipVought]

Al,

Is this a number that you'l be doing arithmetic poeration on, or is it really a string of numeric digits, 4 characters long?

If arithmetic, then just Format/Cells/Number Tab -- Custom format -- 0000

If string of digits, then a new column with the formula
[tt]
=left("000",4-Len(a1))&a1
[/tt]
assuming that your data starts in a1. copy down.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[TonyJollans]

Hi alv7ter,

Nothing wrong with Skip's formula, but I prefer to use ..

[blue][tt] =RIGHT("0000"&A1,4)[/tt][/blue]

However, I would go with the Format option unless you have a reason not to.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[cottontop]

You may want to try:

=rept("0",4-len(B1))& B1

This will add the appropriate number of "0" to anything in cell B1.

Mind you that the result will be text.

Hope this helps!