strip off leading zeros

[Jeffrey Green]

[TABLE="width: 203"]
<tbody>[TR]
[TD="class: xl65, width: 203"]00080236528-GP0KA

I need the leading zeros to go away.
leaving me with
[TABLE="width: 203"]
<tbody>[TR]
[TD="class: xl65, width: 203"]80236528-GP0KA

thanks[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Note, there are varying numbers of 0s' and the length of the string isnt the same all the time.

 

[Joe4]

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

 

[wideboydixon]

Book1
	A	B
1	00080236528-GP0KA	80236528-GP0KA
Sheet1

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

WBD

 

[Special-K99]

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

 

[Joe4]

If my initial assumption is correct (in my first reply), here is the formula I came up with:

=VALUE(LEFT(A1, FIND("-",A1)-1)) & MID(A1,FIND("-",A1),LEN(A1))

 

[Tetra201]

@ wideboydixon:

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

 

[Tetra201]

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)

 

[Special-K99]

@ wideboydixon:

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

Darn I liked that solution.

 

[dave2018]

	A	B
00080236528-GP0KA	80236528-GP0KA

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=SUBSTITUTE(TRIM(SUBSTITUTE($A1,"0"," "))," ","0")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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","")

 

[jtakw]

Hi,

How about:

Book1
	A	B
1	00080236528-GP0KA	80236528-GP0KA
2	00080230008-GP0KA	80230008-GP0KA
3	00080036500-GP0KA	80036500-GP0KA
Sheet94

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND("-",A1)-1)+0&MID(A1,FIND("-",A1),255)