removal of first digit on left in cell

  • Thread starter Thread starter Penny
  • Start date Start date
P

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?
 
Penny,

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

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

Mike
 
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
 
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
 
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
Click to expand...

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
 
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
 
Back
Top