search for date in long string of text

[ryan00davis]

ok, ive done a lot of searching and cant seem to come up with what im
looking for.....

i need to be able to pull a date out of a long string of text, the date
is not always in the same place in the string.

for example, here is one of the lines:

TANANA RIVER BRIDGE EA, 4033050001, Berg, David William, 8212813,
06/30/2006, Tim Woster, -

all of this is in one cell.

what i eventually want to do is pull out the date and be able to search
a section of the sheet for the earliest date

the second part of this problem is to make it search the rows up until
the office changes.... for example, there will be a cell that just has
an office name in it and nothing else, it would not be difficult to
locate this cell with an isnumber search function, but how would i make
the search for the date only go up until it sees the previous office. i
was thinking something along the lines of a for loop (i know how they
work in c, but dont know if they exist in excel) that would just say if
previous cell has an earlier date, replace current date in memory with
previous cell, and do this until a cell says one of the office names
(there are only about 15 of them so i could just type all of these
out). but i have no idea how to do a for loop in excel or even if one
exists, but this i could probably search and figure out, the big
problem is pulling out the date

i have no experience with macros, but any and all advice is greatly
appreciated, ive been trying to figure this out for a while.


thanks in advance

ryan

 

[CLR]

To get the date, assuming always two place month and no other use of the /

=MID(A1,FIND("/",A1,1)-2,10)

Vaya con Dios,
Chuck, CABGx3

 

[ryan00davis]

Thank you so much for the quck reply

in case anybody wants to know exactly what i used... here it is (for
row 2):


Code:
--------------------
=IF(ISNUMBER(SEARCH("??/??/????", A2)),DATEVALUE(MID(A2,SEARCH("/??/",A2,1)-2,10)), "")
--------------------


i first needed to search to make sure that there was a date on that
line in the first place, i also needed to use wildcards because there
were slashes on other lines, and because i used wildcards i needed to
change the find to search. last i used datevalue to make it a number
value so i could find the minimum, and just changed the format to date
so it would still look like a date.


now to find a minimum date:
if it were done by hand i coud just say

Code:
--------------------
=MIN(J188:J199)
--------------------

but i would like it to start on any row that has a value in the k
column and go up to any row that has a value of "client aged A/R" in
the A column

the start column would be fine, just make it an if statement that only
works for rows that have values in the K column, but how would i make
it automatically stop

here is what i have so far:


Code:

 

[ryan00davis]

ok, i have almost figured it out, here is what i have so far:

Code
-------------------
IF(L200="","",MIN(ADDRESS(MATCH("Client Aged A/R", $A$1:A198, 0), 10):J200)
-------------------


but there is a formula error somewhere, i dont understand because if
take out the address part and put in J7 it works, and the address par
put into another cell returns J7, so i cant figure out why exactly i
doesnt want to work, ill keep playing with it, but maybe someone els
out there can help.

the second problem is that i want the match to work backwards, righ
now it returns the highest cell in the range with "Client Aged A/R",
want it to return the lowest cell in the range with value "Client Age
A/R", that is why i have $A$1 as the upper limit of the range, becaus
it should search all the way up for the first possible value, anybod
know how to do this

 

[ryan00davis]

ok, this was kinda stupid, and im not sure why they make you do this
but i needed to add indirect() around the address

Code
-------------------
=IF(M200="","",MIN(INDIRECT((ADDRESS(MATCH("Client Aged A/R", $A$1:A198, 0), 10))):J200)
-------------------

anyways, now if anybody can help me figure out how to make the matc
run backwards, that would be awesome...

right now it searches from A1 and finds the first "client aged a/r" o
the way down to A198; i want it to start at A198 and find the firs
"client aged a/r" on the way up to A

oh, and while it seems to be the logical solution just changing a1:a19
to a198:a1 doesnt work, it automatically changes it bac

-rya