Characters in a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
Click to expand...
has one character then add zero's in front of it until it
has 3 characters.
 
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 said:
I need a macro that will look at a cell and if it only has one character
Click to expand...
then add zero's in front of it until it has 3 characters.
 
So would that be.

if activecell <> len("3") the
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 said:
So can I just say len(activecell) =3 and forget it? as long as the cell
Click to expand...
attribute is set to text?
 
Back
Top