I need a macro that will look at a cell and if it only has one character then add zero's in front of it until it has 3 characters.

Example: if cell "A1" = "10" then the macro would make it say "010"
if cell "A1" = "BA" then the macro would make it say "0BA"

Is there a setting that looks at the number of characters???

Thanks,
Kevin

len("string")
works either in a worksheet or a VBA function


-----Original Message-----
I need a macro that will look at a cell and if it only
has one character then add zero's in front of it until it
has 3 characters.

Example: if cell "A1" = "10" then the macro would make
it say "010"
if cell "A1" = "BA" then the macro would
make it say "0BA"

Is there a setting that looks at the number of
characters???

Thanks,
Kevin

.

len(Range("A1")) will tell you how many characters.

if you cell contains a number, then you will either have to store it as a
text string to see a leading zero or produce the leading zero by using a
number format like "000"

--
Regards,
Tom Ogilvy

"KevGrn114" wrote in message
...
I need a macro that will look at a cell and if it only has one character
then add zero's in front of it until it has 3 characters.

Example: if cell "A1" = "10" then the macro would make it say "010"
if cell "A1" = "BA" then the macro would make it say "0BA"

Is there a setting that looks at the number of characters???

Thanks,
Kevin

So would that be.

if activecell < len("3") the
then something

or just active cell=len("3") ?

if len(activeCell) = 3 then

--
Regards,
Tom Ogilvy

"KevGrn114" wrote in message
...
So would that be..

if activecell < len("3") then
then something?

or just active cell=len("3") ??

So can I just say len(activecell) =3 and forget it? as long as the cell attribute is set to text?

if the cell contains text or numbers, you can use len.

Range("A1").Value =10
? len(range("A1").Value)
2

You can use

len(range("A1').Text) to check the number of characters being displayed
regardless of what is in the cell.

? range("A1").Text
#N/A
? len(Range("A1").text)
4

If the cell will never hold more than 3 characters you could just do

Dim sVal as String
sVal = "'" & Right("000" & ActiveCell.Text,3)
ActiveCell.Text = sVal

--
Regards,
Tom Ogilvy



"KevGrn114" wrote in message
...
So can I just say len(activecell) =3 and forget it? as long as the cell
attribute is set to text?

Thanks Tom, this helps out alot. Its working for me now..