Remove Date form text string

[SteveT57]

hi,
I am using Excel 2007 and am looking for away to extract the date from the following string
WC 01/08/11 WK 6 (in cell B1)
please note the 1st part WC is constant however the WK 6 changes eg could be WK & any number between 1-52

Thanks in advance

 

[Jonmo1]

If the WC mm/dd/yy is always constant, you can use

=MID(A1,4,8)+0
Format the cell with the formula as a Date


Hope that helps

 

[SteveT57]

Thanks jonmo1. It worked for most of my data, however I found some of the dates are are DD/MM/YYYY instead of DD/MM/YY is there a way to accomodate where there is this an anomaly

 

[Haseeb Avarakkan]

Hello Steve, try any of these.

=TRIM(MID(A1,FIND(" ",A1),FIND(" ",A1,4)-FIND(" ",A1)))+0

=TRIM(MID(SUBSTITUTE(TRIM(A1)," ",REPT(" ",100)),100,100))+0

 

[SteveT57]

Thanks Haseeb

 

[Jonmo1]

Try

=MID(SUBSTITUTE(A1," "," ",2),4,10)+0

That was supposed to be 2 spaces where It is RED. But the forum software removed one of the spaces.
Idea is to replace single space with 2 spaces.

 

[barry houdini]

Here's another....

=LOOKUP(10^9,MID(A1,4,{8,9,10})+0)

 

[SteveT57]

Please see more sample data below. Whwn I look at it is more complicated than perhaps I have orginally implied. I have bolded the data I would like to be able to extract. It looks like 7 characters from the left however the right is variable
<TABLE style="WIDTH: 256pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=341 border=0><COLGROUP><COL style="WIDTH: 256pt; mso-width-source: userset; mso-width-alt: 12470" width=341><TBODY><TR style="HEIGHT: 36pt" height=48><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 256pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 36pt; BACKGROUND-COLOR: #666699" width=341 height=48>Product Description</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: white" height=24>2717142 PARRAMATTA SOX V1701 309 9-1</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: white" height=24>2717159 PARRAMATTA SOX V1701 309 13-</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: white" height=24>2717166 PARRAMATTA SOX V1701 309 2-8</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: white" height=24>2717173 ST GEORGE SOX V1701 310 9-1</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: white" height=24>2717180 ST GEORGE SOX V1701 310 13-</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: white" height=24>2717197 ST GEORGE SOX V1701 310 2-8</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: white" height=24>2717296 NTHRN EAGLES SOX V1701 26G 9-1</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: white" height=24>2717302 NTHRN EAGLES SOX V1701 26G 13-</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: white" height=24>2717319 NTHRN EAGLES SOX V1701 26G 2-8</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: white" height=24>2717333 STH SYDNEY SOX V1701 380 13-</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: white" height=24>2717357 WESTN TIGERS SOX V1701 26H 9-1</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: white" height=24>2717364 WESTN TIGERS SOX V1701 26H 13-</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: white" height=24>2717371 WESTN TIGERS SOX V1701 26H 2-8</TD></TR></TBODY></TABLE>

 

[Michael M]

If you want to pull the "product name" from within the string
AND they are in Upper Case, try this UDF, copied into a standard module

Function UC1(s As String) As String 'extracts uppercase string out of text
With CreateObject("VBScript.RegExp")
  .Pattern = "(\b[A-Z ]{2,}\s\b)"
  If .Test(s) Then UC1 = .Execute(s)(0).SubMatches(0)
End With
End Function

and then use

=UC1(A1)

as your function