Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
search for date in long string of text
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 -- ryan00davis ------------------------------------------------------------------------ ryan00davis's Profile: http://www.excelforum.com/member.php...o&userid=37344 View this thread: http://www.excelforum.com/showthread...hreadid=570458 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
search for date in long string of text
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" wrote: 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 -- ryan00davis ------------------------------------------------------------------------ ryan00davis's Profile: http://www.excelforum.com/member.php...o&userid=37344 View this thread: http://www.excelforum.com/showthread...hreadid=570458 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
search for date in long string of text
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: -------------------- =IF(L200="","",MIN(J188:J200)) -------------------- i just need to figure out what i can plug in instead of J188 -- ryan00davis ------------------------------------------------------------------------ ryan00davis's Profile: http://www.excelforum.com/member.php...o&userid=37344 View this thread: http://www.excelforum.com/showthread...hreadid=570458 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
search for date in long string of text
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 i take out the address part and put in J7 it works, and the address part put into another cell returns J7, so i cant figure out why exactly it doesnt want to work, ill keep playing with it, but maybe someone else out there can help. the second problem is that i want the match to work backwards, right now it returns the highest cell in the range with "Client Aged A/R", i want it to return the lowest cell in the range with value "Client Aged A/R", that is why i have $A$1 as the upper limit of the range, because it should search all the way up for the first possible value, anybody know how to do this? -- ryan00davis ------------------------------------------------------------------------ ryan00davis's Profile: http://www.excelforum.com/member.php...o&userid=37344 View this thread: http://www.excelforum.com/showthread...hreadid=570458 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
search for date in long string of text
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 match run backwards, that would be awesome.... right now it searches from A1 and finds the first "client aged a/r" on the way down to A198; i want it to start at A198 and find the first "client aged a/r" on the way up to A1 oh, and while it seems to be the logical solution just changing a1:a198 to a198:a1 doesnt work, it automatically changes it back -ryan -- ryan00davis ------------------------------------------------------------------------ ryan00davis's Profile: http://www.excelforum.com/member.php...o&userid=37344 View this thread: http://www.excelforum.com/showthread...hreadid=570458 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract specific value from a long text string | Excel Worksheet Functions | |||
Format cells with a formula (7 conditions). | Excel Discussion (Misc queries) | |||
How do i insert a Address Bar kind of drop down list within MS Exc | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
Find nth instance of a character in a string | Excel Discussion (Misc queries) |