ExcelBanter - Leading Zeros

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Leading Zeros (https://www.excelbanter.com/excel-worksheet-functions/159776-leading-zeros.html)

I have a column that has leading zeros. I want to get rid of all leading
zeros. Some have 3, some have 2...etc....SOme of the cells have letters in
them also. SO what can I do? Thanks

Try this UDF:

Function nozero(r As Range) As String
v = r.Value
n = Len(v)
For i = 1 To n
If Left(v, 1) = "0" Then
v = Right(v, Len(v) - 1)
Else
Exit For
End If
Next
nozero = v
End Function
--
Gary''s Student - gsnu200747

"Ben Watts" wrote:

I have a column that has leading zeros. I want to get rid of all leading
zeros. Some have 3, some have 2...etc....SOme of the cells have letters in
them also. SO what can I do? Thanks

If A1 contains the string to be altered
Example:
A1: 00asdf20

Perhaps this formula in a helper column:
B1:
=RIGHT(A1,LEN(A1)+1-MATCH(FALSE,INDEX(MID(A1,ROW($A$1:INDEX($A:$A,LEN( A1),1)),1)="0",0),0))

In the above example, B1 returns: asdf20

That formula is durable against blank cells, numeric cells, and cells with
no leading zeros.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

"Ben Watts" wrote:

I have a column that has leading zeros. I want to get rid of all leading
zeros. Some have 3, some have 2...etc....SOme of the cells have letters in
them also. SO what can I do? Thanks

Ron, that almost worked but it left 1 zero at the beginning of each cell.
Here is an example of my first 3 cells in the column

00013623
e3000211
00005629
When I put the code in it gave me this

013623
e3000211
05629
WHich is right except there is still one zero left at the beginning!

"Ron Coderre" wrote:

If A1 contains the string to be altered
Example:
A1: 00asdf20

Perhaps this formula in a helper column:
B1:
=RIGHT(A1,LEN(A1)+1-MATCH(FALSE,INDEX(MID(A1,ROW($A$1:INDEX($A:$A,LEN( A1),1)),1)="0",0),0))

In the above example, B1 returns: asdf20

That formula is durable against blank cells, numeric cells, and cells with
no leading zeros.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

"Ben Watts" wrote:

I have a column that has leading zeros. I want to get rid of all leading
zeros. Some have 3, some have 2...etc....SOme of the cells have letters in
them also. SO what can I do? Thanks

Almost, this what I had in my 1st 3 cells
00013623
e3000211
00005629
This is what I got with your formula
013623
e3000211
05629
It left a zero at the beginning, I need to get rid of that. Thanks, that
was still awesome.

"Ron Coderre" wrote:

If A1 contains the string to be altered
Example:
A1: 00asdf20

Perhaps this formula in a helper column:
B1:
=RIGHT(A1,LEN(A1)+1-MATCH(FALSE,INDEX(MID(A1,ROW($A$1:INDEX($A:$A,LEN( A1),1)),1)="0",0),0))

In the above example, B1 returns: asdf20

That formula is durable against blank cells, numeric cells, and cells with
no leading zeros.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

"Ben Watts" wrote:

I have a column that has leading zeros. I want to get rid of all leading
zeros. Some have 3, some have 2...etc....SOme of the cells have letters in
them also. SO what can I do? Thanks

This is my formula for cell B1:
=RIGHT(A1,LEN(A1)+1-MATCH(FALSE,INDEX(MID(A1,ROW($A$1:INDEX($A:$A,LEN( A1),1)),1)="0",0),0))

Note: In case text-wrap impacts the display, there are no spaces in that
formula.

Using your data, this is what the formulas are returning:
Orig value Formula result
00013623 13623
e3000211 e3000211
00005629 5629

It's seeming like a data issue is causing you to get different results.
Check the source cells carefully....Is anything odd there that I'm not
catching?

***********
Regards,
Ron

XL2003, WinXP

"Ben Watts" wrote:

Almost, this what I had in my 1st 3 cells
00013623
e3000211
00005629
This is what I got with your formula
013623
e3000211
05629
It left a zero at the beginning, I need to get rid of that. Thanks, that
was still awesome.

"Ron Coderre" wrote:

If A1 contains the string to be altered
Example:
A1: 00asdf20

Perhaps this formula in a helper column:
B1:
=RIGHT(A1,LEN(A1)+1-MATCH(FALSE,INDEX(MID(A1,ROW($A$1:INDEX($A:$A,LEN( A1),1)),1)="0",0),0))

In the above example, B1 returns: asdf20

That formula is durable against blank cells, numeric cells, and cells with
no leading zeros.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

"Ben Watts" wrote:

I have a column that has leading zeros. I want to get rid of all leading
zeros. Some have 3, some have 2...etc....SOme of the cells have letters in
them also. SO what can I do? Thanks

I put that on a blank sheet and it worked just fine, my column is E and the
values start in E2. I substituted all the A's in your formula to E and all
the A1's to E2. Is that right?

"Ron Coderre" wrote:

This is my formula for cell B1:
=RIGHT(A1,LEN(A1)+1-MATCH(FALSE,INDEX(MID(A1,ROW($A$1:INDEX($A:$A,LEN( A1),1)),1)="0",0),0))

Note: In case text-wrap impacts the display, there are no spaces in that
formula.

Using your data, this is what the formulas are returning:
Orig value Formula result
00013623 13623
e3000211 e3000211
00005629 5629

It's seeming like a data issue is causing you to get different results.
Check the source cells carefully....Is anything odd there that I'm not
catching?

***********
Regards,
Ron

XL2003, WinXP

"Ben Watts" wrote:

Almost, this what I had in my 1st 3 cells
00013623
e3000211
00005629
This is what I got with your formula
013623
e3000211
05629
It left a zero at the beginning, I need to get rid of that. Thanks, that
was still awesome.

"Ron Coderre" wrote:

If A1 contains the string to be altered
Example:
A1: 00asdf20

Perhaps this formula in a helper column:
B1:
=RIGHT(A1,LEN(A1)+1-MATCH(FALSE,INDEX(MID(A1,ROW($A$1:INDEX($A:$A,LEN( A1),1)),1)="0",0),0))

In the above example, B1 returns: asdf20

That formula is durable against blank cells, numeric cells, and cells with
no leading zeros.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

"Ben Watts" wrote:

I have a column that has leading zeros. I want to get rid of all leading
zeros. Some have 3, some have 2...etc....SOme of the cells have letters in
them also. SO what can I do? Thanks

I figured it out
you
had=RIGHT(A1,LEN(A1)+1-MATCH(FALSE,INDEX(MID(A1,ROW($A$1:INDEX($A:$A,LEN( A1),1)),1)="0",0),0))
I had
=RIGHT(E1,LEN(AE1)+1-MATCH(FALSE,INDEX(MID(E1,ROW($E$1:INDEX($E:$E,LEN( E1),1)),1)="0",0),0))
should be
=RIGHT(E1,LEN(E1)+1-MATCH(FALSE,INDEX(MID(E1,ROW($A$1:INDEX($A:$A,LEN( E1),1)),1)="0",0),0))

"Ron Coderre" wrote:

This is my formula for cell B1:
=RIGHT(A1,LEN(A1)+1-MATCH(FALSE,INDEX(MID(A1,ROW($A$1:INDEX($A:$A,LEN( A1),1)),1)="0",0),0))

Note: In case text-wrap impacts the display, there are no spaces in that
formula.

Using your data, this is what the formulas are returning:
Orig value Formula result
00013623 13623
e3000211 e3000211
00005629 5629

It's seeming like a data issue is causing you to get different results.
Check the source cells carefully....Is anything odd there that I'm not
catching?

***********
Regards,
Ron

XL2003, WinXP

"Ben Watts" wrote:

Almost, this what I had in my 1st 3 cells
00013623
e3000211
00005629
This is what I got with your formula
013623
e3000211
05629
It left a zero at the beginning, I need to get rid of that. Thanks, that
was still awesome.

"Ron Coderre" wrote:

If A1 contains the string to be altered
Example:
A1: 00asdf20

Perhaps this formula in a helper column:
B1:
=RIGHT(A1,LEN(A1)+1-MATCH(FALSE,INDEX(MID(A1,ROW($A$1:INDEX($A:$A,LEN( A1),1)),1)="0",0),0))

In the above example, B1 returns: asdf20

That formula is durable against blank cells, numeric cells, and cells with
no leading zeros.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

"Ben Watts" wrote:

I have a column that has leading zeros. I want to get rid of all leading
zeros. Some have 3, some have 2...etc....SOme of the cells have letters in
them also. SO what can I do? Thanks

THAT's the information I needed.
(this may be a dbl-post....my computer did something "funny" the first
attempt)

Your edits were correct EXCEPT for changing $A$1 to $E$2
Here's why:
In my original formula:
=RIGHT(A1,LEN(A1)+1-MATCH(FALSE,INDEX(MID(A1,ROW($A$1:INDEX($A:$A,LEN( A1),1)),1)="0",0),0))

This part: $A$1:INDEX($A:$A,LEN(A1),1)
creates an array of cells, beginning with $A$1 and continuing down Col_A for
the number of characters in the referenced cell.

For instance, if A1 contains "abcd"....the length is 4...
so the constructed array is:
A1:A4

That constructed array is wrapped in the ROW function, which returns the row
number of the reference.

Still using my example, ROW(A1:A4) returns {1,2,3,4}

That array is used by the overall formula to pick individual characters from
"abcd"

Anyway....
all of the above is the LONG way of saying that your formula referencing
cell E2 should be:
=RIGHT(E2,LEN(E2)+1-MATCH(FALSE,INDEX(MID(E2,ROW($A$1:INDEX($A:$A,LEN( E2),1)),1)="0",0),0))

Does that help?
***********
Regards,
Ron

XL2003, WinXP

"Ben Watts" wrote:

I put that on a blank sheet and it worked just fine, my column is E and the
values start in E2. I substituted all the A's in your formula to E and all
the A1's to E2. Is that right?

"Ron Coderre" wrote:

This is my formula for cell B1:
=RIGHT(A1,LEN(A1)+1-MATCH(FALSE,INDEX(MID(A1,ROW($A$1:INDEX($A:$A,LEN( A1),1)),1)="0",0),0))

Note: In case text-wrap impacts the display, there are no spaces in that
formula.

Using your data, this is what the formulas are returning:
Orig value Formula result
00013623 13623
e3000211 e3000211
00005629 5629

It's seeming like a data issue is causing you to get different results.
Check the source cells carefully....Is anything odd there that I'm not
catching?

***********
Regards,
Ron

XL2003, WinXP

"Ben Watts" wrote:

Almost, this what I had in my 1st 3 cells
00013623
e3000211
00005629
This is what I got with your formula
013623
e3000211
05629
It left a zero at the beginning, I need to get rid of that. Thanks, that
was still awesome.

"Ron Coderre" wrote:

If A1 contains the string to be altered
Example:
A1: 00asdf20

Perhaps this formula in a helper column:
B1:
=RIGHT(A1,LEN(A1)+1-MATCH(FALSE,INDEX(MID(A1,ROW($A$1:INDEX($A:$A,LEN( A1),1)),1)="0",0),0))

In the above example, B1 returns: asdf20

That formula is durable against blank cells, numeric cells, and cells with
no leading zeros.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

"Ben Watts" wrote:

I have a column that has leading zeros. I want to get rid of all leading
zeros. Some have 3, some have 2...etc....SOme of the cells have letters in
them also. SO what can I do? Thanks