Remove leading zero from partial string

[ExcelPowerSq]

Hi,
I have a complicated string where I need to remove leading zero from partial string and change the string with different formatting. Example below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Original[/TD]
[TD]New[/TD]
[/TR]
[TR]
[TD]02-01-22 [G MM][/TD]
[TD]2/1/22[/TD]
[/TR]
[TR]
[TD]3-05-01 [X SM][/TD]
[TD]3/5/1[/TD]
[/TR]
[TR]
[TD]07-6-10 [E SM][/TD]
[TD]7/6/10[/TD]
[/TR]
</tbody>[/TABLE]

I have tried using NumberValue but that returns an integer value. I have tried TEXT with mm/dd/yy but with that data is all incorrect due to there being no 0 days or 0 months.

I can replace the '-' with '/' easily by using substitute function but that still does not solve my original problem of removing leading zero.

Wish there was regex support in Excel. Your help is much appreciated!

Thanks,
K

 

[ExcelPowerSq]

Give this formula a try...

=0+LEFT(A1,FIND("-",A1)-1)&"/"&0+SUBSTITUTE(MID(A1,FIND("-",A1)+1,2),"-","")&"/"&0+MID(A1,FIND("-",A1,FIND("-",A1)+1)+1,2)

Awesome! Works like a charm. You, sir, are a genius