extract hidden date

[Sunil Patel]

I have date in range a1 to a2000
On only one line there will be a date. Is there a simplecommand that can find this date. At present i am using the following code.


Set CHECKRANGE = Columns(1).Cells
For Each CELL In CHECKRANGE
If CELL.Text Like "*##/##/20##*" Then
........
.........
NEXT CELL

an example of the date in column is " 146.01 153.80 n/a 0.06 -1.85 ( -1.3%) n/a 03/03/2006 0 £0.00 £0.00 n/a "
i need to find and extract that date.


Thanks

[davesexcel]

you can use vlookup
If that doesn't work check out data, filter
I am not sure what you are doing with this date, are you just looking to see if its there, or are you going to extract data from the column beside it

[Toppers]

A slight modification of your code:

Dim v As Variant
Set CHECKRANGE = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each cell In CHECKRANGE
v = Split(cell.Text, " ")
For i = LBound(v) To UBound(v)
If v(i) Like "*##/##/20##*" Then
MsgBox v(i)
GoTo Nextaction:
End If
Next i
Next cell
Nextaction:

HTH

"Sunil Patel" wrote:

> I have date in range a1 to a2000
> On only one line there will be a date. Is there a simplecommand that can find this date. At present i am using the following code.
>
>
> Set CHECKRANGE = Columns(1).Cells
> For Each CELL In CHECKRANGE
> If CELL.Text Like "*##/##/20##*" Then
> ........
> .........
> NEXT CELL
>
> an example of the date in column is " 146.01 153.80 n/a 0.06 -1.85 ( -1.3%) n/a 03/03/2006 0 £0.00 £0.00 n/a "
> i need to find and extract that date.
>
>
> Thanks

[JE McGimpsey]

One way:

Dim rCell As Range
Dim sExtract As String
For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If .Text Like "*##/##/20##*" Then
sExtract = Mid(.Text, InStr(.Text, "/20") - 5, 10)
MsgBox .Address(False, False) & ": " & sExtract
End If
End With
Next rCell

Note: this is based on your example. If you had data like:

146.01 153.80 n/a 0.06 1/20 (-1.3%) n/a 03/03/2006 0 ...

this would need more sophisticated pattern matching


In article <[email protected]>,
"Sunil Patel" <[email protected]> wrote:

> I have date in range a1 to a2000
> On only one line there will be a date. Is there a simplecommand that can find
> this date. At present i am using the following code.
>
>
> Set CHECKRANGE = Columns(1).Cells
> For Each CELL In CHECKRANGE
> If CELL.Text Like "*##/##/20##*" Then
> .......
> ........
> NEXT CELL
>
> an example of the date in column is " 146.01 153.80 n/a 0.06 -1.85 (
> -1.3%) n/a 03/03/2006 0 £0.00 £0.00 n/a "
> i need to find and extract that date.
>
>
> Thanks