Removing Leading 0's in string

[shawnterry]

So, I have a string like so:

02-022.auditorium
22-222.auditorium

If a first number, or first number after the dash are 0's, I need to remove them to look like:

2-22.auditorium
22-222.auditorium

I have two formulas to remove the zeros but I'm having trouble with the logic to run them both at once. They are (A4 holds the full string 02-022.auditorium):

Remove the first 0 if exists (yes, I know I could use LEFT but I'd rather keep things as similar as possible)...
=IF(MID(A4,FIND("-",A4)-2,1)="0",REPLACE(A4,1,1,""),A4)
DISPLAYS: 2-022.auditorium

Remove the first 0 after the dash if exists...
=IF(MID(A4, FIND("-", A4)+1, 1)="0", REPLACE(A4,4,1,""), A4)
DISPLAYS: 02-22.auditorium

So... How can I combine these to remove both 0's if they exist?

Thanks for the help!

[Moo the Dog]

Couldn't you just use:

Please Login or Register  to view this content.

- Moo

[menem]

I hope that your data pattern is

nnn-nnnn.xxxxx

Please Login or Register  to view this content.

Regards.

[AlKey]

Try this

Please Login or Register  to view this content.

v	A	B
1	02-022.auditorium	2-022.auditorium
2	22-222.auditorium	22-222.auditorium

[Fluff13]

Maybe
=SUBSTITUTE(IF(LEFT(A4)="0",MID(A4,2,100),A4),"-0","-")

[Rick Rothstein]

For the OP (and future readers of this thread), the difference between AlKey's and Fluff13's formulas has to do with text that does not have a leading number. Fluff13's formula will return the contents of that cell exactly as is whereas AlKey's formula will return an empty text string ("") if there are no numbers in the text at all or, if there are numbers later on in the text, the text starting at that internal number will be returned. For example, if cell A1 contains "ONE234FIVE", Fluff13's formula will return "ONE234FIVE" whereas AlKey's formula will return "234FIVE". As a further example, if cell A1 contains "NoNumbers" then Flull13's formula will return "NoNumbers" whereas AlKey's formula will return "".

[Root_]

Another (shorter) maybe :-)

=REPLACE(SUBSTITUTE("-"&A4,"-0","-"),1,1,"")

[Rick Rothstein]

For the OP (and future readers of this thread), the difference between AlKey's and Fluff13's formulas has to do with text that does not have a leading number.

Another key difference between AlKey's and Fluff13's formulas (beside what I pointed out in Message #6) is that AlKey's formula does not remove leading zeros from the second number (after the dash) which the OP indicated was required whereas Fluff13's formula does do that.

Another (shorter) maybe :-)

=REPLACE(SUBSTITUTE("-"&A4,"-0","-"),1,1,"")

This formula does not handle trailing zeros correctly (it removes them) so this 12-030.text should become 12-30.text but the above formula returns 12-3.text instead.

[Root_]

...=REPLACE(SUBSTITUTE("-"&A4,"-0","-"),1,1,"") This formula does not handle trailing zeros correctly (it removes them) so this 12-030.text should become 12-30.text but the above formula returns 12-3.text instead.

Are you sure? It does return 12-30.text in my Excel 2010 test sheet.

[Rick Rothstein]

Yikes! You are correct... your code does remove the leading zeros. Sorry about that... I thought I had copied your formula when I pasted it for my test and, as it turns out, a different formula got pasted and I did not realize it. That is the good news... now for the possibly bad news. If that second number has two leading zeros, your code only removes the first zero and leaves the second zero. Now, that may be alright... we cannot tell from the limited examples the OP posted, so we will have to wait for the OP to tell us if such number are possible and, if they are, how they should be handled.