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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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