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

 

[Ron Coderre]

With your sample data in Col_A
Try this approach:

B2: =SUBSTITUTE(TEXT(--(LEFT(A2,FIND(" ",A2))),"m/d/y"),"/0","/")

Is that something you can work with?

 

[Rick Rothstein]

With your sample data in Col_A
Try this approach:

B2: =SUBSTITUTE(TEXT(--(LEFT(A2,FIND(" ",A2))),"m/d/y"),"/0","/")

Is that something you can work with?

Will that work correctly in locales that use the reverse day-month order from your locale? In case not, here is an alternative...

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

 

[Ron Coderre]

Unless I'm missing something...It seems that if the m/d order is reversed from the US convention, then this would work:

B2: =SUBSTITUTE(TEXT(--(LEFT(A2,FIND(" ",A2))),"d/m/y"),"/0","/")

 

[Rick Rothstein]

Unless I'm missing something...It seems that if the m/d order is reversed from the US convention, then this would work:

B2: =SUBSTITUTE(TEXT(--(LEFT(A2,FIND(" ",A2))),"d/m/y"),"/0","/")

I am under the impression that if, say, the value in front of the space is, say, 6/1/20, then in the US this part of the formula...

TEXT(value,"d/m/y")

would yield June 1, 2020 whereas in England it would yield January 6, 2020 so that your "d/m/y" would produce different results. Am I not correct in that impression? I have absolutely no experience with international date issues, so I do not know for sure myself, but that is what I gathered from other discussion about dates in the past.

 

[ExcelPowerSq]

With formula, =SUBSTITUTE(TEXT(--(LEFT(A2,FIND(" ",A2))),"M/D/y"),"/0","/")

Following values yield #VALUE ! error.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]2-0-0 [XE SM][/TD]
[TD]#VALUE ![/TD]
[/TR]
[TR]
[TD]0-0-02 [GE SM][/TD]
[TD]#VALUE ![/TD]
[/TR]
[TR]
[TD]0-0-03 [GE SM][/TD]
[TD]#VALUE ![/TD]
[/TR]
</tbody>[/TABLE]


It appears that as long as the Day is 0 value, formula will not be able to process data.

 

[ExcelPowerSq]

Will that work correctly in locales that use the reverse day-month order from your locale? In case not, here is an alternative...

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

=SUBSTITUTE(MID(SUBSTITUTE("-"&LEFT(A1,FIND(" ",A1)-1),"-0","-"),2,8),"-","/") seems to trim single digit 0s

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]2-0-0 [XE SM][/TD]
[TD]2//[/TD]
[/TR]
[TR]
[TD]0-0-01 [GE SM][/TD]
[TD]//1[/TD]
[/TR]
[TR]
[TD]7-0-00 [GE SM[/TD]
[TD]7//0[/TD]
[/TR]
</tbody>[/TABLE]


Any suggestions on how to include single 0s as well in your formula? By the way these are not date field. These are supposed to be ports on a network device.

Thanks

 

[Rick Rothstein]

=SUBSTITUTE(MID(SUBSTITUTE("-"&LEFT(A1,FIND(" ",A1)-1),"-0","-"),2,8),"-","/") seems to trim single digit 0s

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]2-0-0 [XE SM][/TD]
[TD]2//[/TD]
[/TR]
[TR]
[TD]0-0-01 [GE SM][/TD]
[TD]//1[/TD]
[/TR]
[TR]
[TD]7-0-00 [GE SM[/TD]
[TD]7//0[/TD]
[/TR]
</tbody>[/TABLE]


Any suggestions on how to include single 0s as well in your formula? By the way these are not date field. These are supposed to be ports on a network device.

The formula you quoted was developed to reverse the month and day fields for dates... there are no single zero days or months so my formula did not have to worry about them. Instead of grabbing a formula meant for reversing date parts and wondering why it doesn't work for non-dates, why don't you tell us what your specific problem is... what does your original values look like, what should they look like after the formula processes them (showing us several specific examples of these two situations would be most useful).

 

[ExcelPowerSq]

The formula you quoted was developed to reverse the month and day fields for dates... there are no single zero days or months so my formula did not have to worry about them. Instead of grabbing a formula meant for reversing date parts and wondering why it doesn't work for non-dates, why don't you tell us what your specific problem is... what does your original values look like, what should they look like after the formula processes them (showing us several specific examples of these two situations would be most useful).

I will gladly present what I need.

I have bunch of ports which are formatted in a special way so they can be sorted properly in Excel and them bulk imported into an application. The problem is Excel-formatted ports are not what application will recognize.

Some examples below:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Excel-Formatted data (current)[/TD]
[TD]Requested data (new format)[/TD]
[/TR]
[TR]
[TD]2-0-0 [XE SM][/TD]
[TD]2/0/0[/TD]
[/TR]
[TR]
[TD]0-0-01 [XE SM][/TD]
[TD]0/0/1[/TD]
[/TR]
[TR]
[TD]0-0-02 [XE SM][/TD]
[TD]0/0/2[/TD]
[/TR]
[TR]
[TD]0-0-03 [GE SM][/TD]
[TD]0/0/0[/TD]
[/TR]
[TR]
[TD]7-1-00 [GE SM][/TD]
[TD]7/1/0[/TD]
[/TR]
[TR]
[TD]7-1-01 [XE SM][/TD]
[TD]7/1/1[/TD]
[/TR]
[TR]
[TD]2-1-10 [XE SM[/TD]
[TD]2/1/10[/TD]
[/TR]
[TR]
[TD]2-0-00 [GE SM][/TD]
[TD]2/0/0[/TD]
[/TR]
[TR]
[TD]7-0-16 [GE SM][/TD]
[TD]7/0/16[/TD]
[/TR]
[TR]
[TD]7-0-02 [GE SM[/TD]
[TD]7/0/2[/TD]
[/TR]
[TR]
[TD]02-01-21 [GE MM][/TD]
[TD]2/1/21[/TD]
[/TR]
</tbody>[/TABLE]

As you can see from above example x/y/z, x and y will always be single-digit, whereas z can be double-digit or single-digit. Neither of the x,y or z can have a leading zero.

Hope that helps.

Thanks

 

[Rick Rothstein]

As you can see from above example x/y/z, x and y will always be single-digit, whereas z can be double-digit or single-digit. Neither of the x,y or z can have a leading zero.

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)