Thanks:  0

1. ## strip off leading zeros

00080236528-GP0KA

I need the leading zeros to go away.
leaving me with
 80236528-GP0KA thanks
Note, there are varying numbers of 0s' and the length of the string isnt the same all the time.

2. ## Re: strip off leading zeros

Will the entries always be all numeric values, followed by a dash, followed by a string?, i.e.
number-string

3. ## Re: strip off leading zeros

AB
100080236528-GP0KA80236528-GP0KA

Sheet1

Worksheet Formulas
CellFormula
B1=SUBSTITUTE(TRIM(SUBSTITUTE(\$A1,"0"," "))," ","0")

WBD

4. ## Re: strip off leading zeros

=left(a1,find("-",a1)-1)+0&right(a1,len(a1)-find("-",a1)+1)

5. ## Re: strip off leading zeros

If my initial assumption is correct (in my first reply), here is the formula I came up with:
Code:
`=VALUE(LEFT(A1, FIND("-",A1)-1)) & MID(A1,FIND("-",A1),LEN(A1))`

6. ## Re: strip off leading zeros

@ wideboydixon:

With your solution, 00080230008-GP0KA --> 802308-GP0KA

7. ## Re: strip off leading zeros

Here is my take on it -- removes up to 9 leading zeros:

=MID(A1,1+COUNT(FIND(CHAR(1)&REPT(0,{1,2,3,4,5,6,7,8,9}),CHAR(1)&A1)),99)

8. ## Re: strip off leading zeros

Originally Posted by Tetra201
@ wideboydixon:

With your solution, 00080230008-GP0KA --> 802308-GP0KA
Darn I liked that solution.

9. ## Re: strip off leading zeros

Originally Posted by wideboydixon
A B
1 00080236528-GP0KA 80236528-GP0KA
Sheet1

Worksheet Formulas
Cell Formula
B1 =SUBSTITUTE(TRIM(SUBSTITUTE(\$A1,"0"," "))," ","0")

WBD

What about 0's to the right of the string? u remove them as well with trim!!

u can add IF to the end... would this be the way u would do it?

=SUBSTITUTE(TRIM(SUBSTITUTE(\$A1,"0"," "))," ","0")&IF(RIGHT(\$A1,1)="0","0","")

10. ## Re: strip off leading zeros

Hi,

AB
100080236528-GP0KA80236528-GP0KA
200080230008-GP0KA80230008-GP0KA
300080036500-GP0KA80036500-GP0KA

Sheet94

Worksheet Formulas
CellFormula
B1=LEFT(A1,FIND("-",A1)-1)+0&MID(A1,FIND("-",A1),255)