Parsing A Code Back into Useable information

Eric Deneault

New Member
Joined
Dec 11, 2008
Messages
10
I have a code 0ddmyLxx where after the leading 0 is the day, month, year, location;xx is a machine number. The Location is constant as "S".
The year is simple by added the number to 2000, and the months of the year by convention are: 1,2,3,4,5,6,7,9, X, N,D where X is Oct, N is Nov and D is Dec

I have used Left & Right functions to parse rebuild an actual date
24-May-2008 but the constructed date is still not real - how do I make it so - glad for any help on this one.

Thank You
Eric
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,917
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you include the parsed sections in the DATE function, you will get a real date - i.e.
=DATE(year_bit,month_bit, day_bit)
 

Eric Deneault

New Member
Joined
Dec 11, 2008
Messages
10
Rory thank you Tip for the here is the outcome -Eric



<TABLE style="WIDTH: 800pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1066 border=0 x:str><COLGROUP><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6838" width=187><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 187pt; mso-width-source: userset; mso-width-alt: 9106" width=249><COL style="WIDTH: 138pt; mso-width-source: userset; mso-width-alt: 6729" width=184><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7680" width=210><TBODY><TR style="HEIGHT: 39.75pt; mso-height-source: userset" height=53><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 140pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 39.75pt; BACKGROUND-COLOR: transparent" width=187 height=53>Colum M</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 84pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>Column N</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 93pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=124>Data Column O</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 187pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=249>Column AA</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 138pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=184>Column AC</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 158pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=210>Column AE</TD></TR><TR style="HEIGHT: 72pt" height=96><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 140pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 72pt; BACKGROUND-COLOR: transparent" width=187 height=96>IF(AE3="","?",+AE3)</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 84pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=112 x:str="'+AC3">+AC3</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 93pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=124>00298S22</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 187pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=249>IF(MID(O3,4,1)="X",10,IF(MID(O3,4,1)="N",11,IF(MID(O3,4,1)="D",12,VALUE(MID(O3,4,1)))))</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 138pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=184>DATE((2000+MID(O3,5,1)),AA3,MID(O3,2,2))</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 158pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=210>MID(O3,FIND("S",O3)+1,2)</TD></TR></TBODY></TABLE>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,132,685
Messages
5,654,748
Members
418,149
Latest member
amamiche67

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
Top