removal of first digit on left in cell

[Penny]

I have a row of numbers that have one to many zero's. I need to remove the
first
zero on the left. Can this be done by a formula?

 

[Mike H]

Penny,

With leading zeroes I assume these must be text. If they er try this

=MID(A1,2,LEN(A1))

Mike

 

[Ronald R. Dodge, Jr.]

Sure, just use the following formula in another column, then copy from there
and paste special as values in the column with the error.

If A5:A3897 is the column with one too many zero on the left side, and
column I is empty, use the following formula in I5, then fill down to I3897.

=RIGHT(TRIM(A5),LEN(TRIM(A5))-1)

The TRIM function is included in case there's any leading/trailing spaces in
the values.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Gord Dibben]

Numbers don't have leading zeros unless they are text.

Maybe they are numbers just formatted that way like Custom format of 000000

will return 001234 if 4 digits in cell. Remove one 0 from the formatting.



Gord Dibben MS Excel MVP

 

[Andrew]

Sure, just use the following formula in another column, then copy from there
and paste special as values in the column with the error.

If A5:A3897 is the column with one too many zero on the left side, and
column I is empty, use the following formula in I5, then fill down to I3897.

=RIGHT(TRIM(A5),LEN(TRIM(A5))-1)

The TRIM function is included in case there's any leading/trailing spacesin
the values.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician

This formula - =RIGHT(TRIM(A5),LEN(TRIM(A5))-1) works. I tried to do
the same with vba code and I got stuck. What I would like to know is
there a way to address individual characters in a string. For
example, in C, if I had a string called STR. I could write out STR as
a string, or I could use an index and take each character such. Is
there anything in vba which would allow the same kind of access to
individual characters? If I wanted to find the 4th character in the
string "stringfinder", how would that be done in vba?

thanks,
Andy

 

[Ronald R. Dodge, Jr.]

Within VBA code, you would use similar function, but a few differences.

STR = VBA.Trim(STR)

This removes any leading/trailing spaces

VBA.Mid(STR, 4, 1)

This gets just the 4 character provided STR is at least 4 characters long

Combine that with a For...Next recursive statement, you can get the
following code:

Dim l_strSingleCharacter As String, l_lngNthCharacter As Long

For l_lngNthCharacter = 1 to VBA.Len(STR) Step 1
l_lngNthCharacter = VBA.Mid(STR,l_lngNthCharacter,1)
<Perform other statements>
Next l_lngNthCharacter

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000




This formula - =RIGHT(TRIM(A5),LEN(TRIM(A5))-1) works. I tried to do
the same with vba code and I got stuck. What I would like to know is
there a way to address individual characters in a string. For
example, in C, if I had a string called STR. I could write out STR as
a string, or I could use an index and take each character such. Is
there anything in vba which would allow the same kind of access to
individual characters? If I wanted to find the 4th character in the
string "stringfinder", how would that be done in vba?

thanks,
Andy