Jeffrey Green

Well-known Member
Joined
Oct 24, 2007
Messages
1,021
00080236528-GP0KA

I need the leading zeros to go away.
leaving me with
80236528-GP0KA

thanks

<tbody>
</tbody>

<tbody>
</tbody>
Note, there are varying numbers of 0s' and the length of the string isnt the same all the time.
 
Last edited:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Will the entries always be all numeric values, followed by a dash, followed by a string?, i.e.
number-string
 
Last edited:
Upvote 0
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))
 
Upvote 0
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)
 
Upvote 0
AB
100080236528-GP0KA80236528-GP0KA

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



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","")
 
Upvote 0
Hi,

How about:


Book1
AB
100080236528-GP0KA80236528-GP0KA
200080230008-GP0KA80230008-GP0KA
300080036500-GP0KA80036500-GP0KA
Sheet94
Cell Formulas
RangeFormula
B1=LEFT(A1,FIND("-",A1)-1)+0&MID(A1,FIND("-",A1),255)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top