Removing Zeros in the middle of a drawing number

[Fola]

Hi All, how do I remove the zeros after the second "- " of my drawing numbers? Thanks

ST0042-E8020-0373 to be ST0042-E8020-373
ST0042-E9006-0021 to be ST0042-E9006-21
ST0041-E8060-0001 to be ST0042-E8060-1

Can I have a single formula to fix this? Thanks.

 

[DanteAmor]

Try :

=LEFT(A2,13)&SUBSTITUTE(RIGHT(A2,4),"0","")

 

[rlv01]

Do you really want always to get rid of all the zeros? What if the drawing number was ST0041-E8060-1001? Would you really want to convert it to ST0041-E8060-11

 

[Fola]

Do you really want always to get rid of all the zeros? What if the drawing number was ST0041-E8060-1001? Would you really want to convert it to ST0041-E8060-11

You are correct, not get rid of all the zeros but to convert it. Thanks.

 

[rlv01]

Maybe this

=IF(--LEFT(RIGHT(A1,4),1)>0,A1,LEFT(A1,13) & SUBSTITUTE(RIGHT(A1,4),"0",""))

 

[Scott Huish]

=TEXTBEFORE(A1,"-",-1)&TEXT(RIGHT(A1,4),"-0")

 

[Fola]

=TEXTBEFORE(A1,"-",-1)&TEXT(RIGHT(A1,4),"-0")

Thank you. It's close but some cells had errors. See below examples

Before After

ST0042-E2012-54	ST0042-E2012-19756
ST0042-E4023-39	ST0042-E4023-14305
ST0042-E4023-40	ST0042-E4023-14671
ST0042-E4023-41	ST0042-E4023-15036
ST0042-E4023-42	ST0042-E4023-15401
ST0042-E4023-43	ST0042-E4023-15766
ST0042-E4023-44	ST0042-E4023-16132
ST0042-E4023-45	ST0042-E4023-16497
ST0042-E4023-46	ST0042-E4023-16862
ST0042-E4023-47	ST0042-E4023-17227
ST0042-E4023-48	ST0042-E4023-17593
ST0042-E4023-50	ST0042-E4023-18323
ST0042-E4023-51	ST0042-E4023-18688

It should have remained the same but the numbers at the end completely changed

 

[Fola]

Maybe this

=IF(--LEFT(RIGHT(A1,4),1)>0,A1,LEFT(A1,13) & SUBSTITUTE(RIGHT(A1,4),"0",""))

Thank you a lot of cells that needed to remain unchanged came back as error.

ST0042-E9501-404	#VALUE!
ST0042-E9501-405	#VALUE!
ST0042-E9501-410	#VALUE!
ST0042-E9501-411	#VALUE!
ST0042-E9501-412	#VALUE!
ST0042-E9501-413	#VALUE!
ST0042-E9501-414	#VALUE!
ST0042-E9501-415	#VALUE!
ST0042-E9501-416	#VALUE!
ST0042-E9501-417	#VALUE!
ST0042-E9501-418	#VALUE!
ST0042-E9501-419	#VALUE!
ST0042-E9501-420	#VALUE!
ST0042-E9501-421	#VALUE!

 

[rlv01]

Thank you. It's close but some cells had errors. See below examples

Thank you a lot of cells that needed to remain unchanged came back as error.

You realize that you are changing the rules, yes? Your original post indicated there would always be 4 digits in the last grouping.

 

[Fola]

Try :

=LEFT(A2,13)&SUBSTITUTE(RIGHT(A2,4),"0","")

Please see below some errors
Before After

ST0042-E9501-1	ST0042-E9501-1-1 Extra -1
ST0042-E9501-7	ST0042-E9501-1-7 Extra -1
ST0042-E9501-8	ST0042-E9501-1-8 Extra -1
ST0042-E9501-18	ST0042-E9501-1-18
ST0042-E9501-19	ST0042-E9501-1-19
ST0042-E8010-2	ST0042-E8010-1-2
ST0042-E8020-369	ST0042-E8020--369
ST0042-E8020-370	ST0042-E8020--37 Double - & 37 should be 370
ST0042-E8020-371	ST0042-E8020--371
ST0042-E8020-372	ST0042-E8020--372
ST0042-E8020-350	ST0042-E8020--35 Double - & 35 should be 350
ST0042-E8020-351	ST0042-E8020--351