Home |
Search |
Today's Posts |
#1
|
|||
|
|||
converting text in cell to a date time
I have a range say A4:A200 in cell A3 is the date and in the range there is a
bunch of cells that have a 3 up to 7 character text that equate to a time, event, who and where code. I need something that will take the code and put that code in a oneliner ie code is 11P4 one liner should read today's date the 11 = 11am the P =practice and the 4 is who and because there no character after the 4 the location is known as Courtice with each peice of info in the one liner going in it's own seperate cell any help on this would be appreciated |
#2
|
|||
|
|||
converting text in cell to a date time
Can you give a bit more detail about the 3-7 character text items.
I'm assuming you can have something like 1P4 meaning 01:00 a.m., but how do you get up to 7 characters? Is the "P" code always a one character or more? and how many codes are there in this position. Similarly with the "4" code. how many people are there? up to 99 or more? Finally what is "Courtice". Is this always the same or is this to be derived from a Lookup table based on the who? Rgds On Fri, 21 Oct 2005 05:49:11 -0700, Herman wrote: I have a range say A4:A200 in cell A3 is the date and in the range there is a bunch of cells that have a 3 up to 7 character text that equate to a time, event, who and where code. I need something that will take the code and put that code in a oneliner ie code is 11P4 one liner should read today's date the 11 = 11am the P =practice and the 4 is who and because there no character after the 4 the location is known as Courtice with each peice of info in the one liner going in it's own seperate cell any help on this would be appreciated __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#3
|
|||
|
|||
converting text in cell to a date time
you are correct
some of the other codes are as follows 7AP4 = 7AM PRACTICE WITH 4 103H2 = 10:3OAM HOME GAME VS. 2 73AG3 = 7:30 AM GAME @ 3 73HN1N = 7:30PM HOME GAME VS N1 IN NEWCASTLE The last character always refers to location and if no alpha character then default is default location A there are 5 locations with only 4 being identified as 5 = blank time can be up to 4 characters with default always being a PM time except as follows 7A = 7am, 73A = 7:30 am, 8A = 8am, 9A = 9am, 10 = 10am as no PM on Saturdays or Sundays same applies to 11. 103 = 10:30 am, 11 = 11am, 113= 11:30 am, 12 = 12pm, 123 = 12:30pm, 1 = 1pm, 13 = 1:30pm and so on the next character in the code of which there is only 3, refers to the event P = practice H = Home game G = Away game the next character(s) refer to the team which go anything from 1-22 as well as N1, N2, N3, O1 and O2 hence you 7 charater code 103GN1O which equates to 10:30 away game vs. Newcastle 1 in Orono and the date at the top of the column tells ya the date.. hope this helps.. "Richard Buttrey" wrote: Can you give a bit more detail about the 3-7 character text items. I'm assuming you can have something like 1P4 meaning 01:00 a.m., but how do you get up to 7 characters? Is the "P" code always a one character or more? and how many codes are there in this position. Similarly with the "4" code. how many people are there? up to 99 or more? Finally what is "Courtice". Is this always the same or is this to be derived from a Lookup table based on the who? Rgds On Fri, 21 Oct 2005 05:49:11 -0700, Herman wrote: I have a range say A4:A200 in cell A3 is the date and in the range there is a bunch of cells that have a 3 up to 7 character text that equate to a time, event, who and where code. I need something that will take the code and put that code in a oneliner ie code is 11P4 one liner should read today's date the 11 = 11am the P =practice and the 4 is who and because there no character after the 4 the location is known as Courtice with each peice of info in the one liner going in it's own seperate cell any help on this would be appreciated __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#4
|
|||
|
|||
converting text in cell to a date time
Because of the varying length or the code this is an extremely
difficult one to slice into its correct constituent parts. Particularly the last character of the code which appears from the example you cite may be a place "Orino", or nothing, and in this case the last character(s) represent the team numbers. It seems to me that it would be far more practical to have a consistent code, where the same characters mean the same sort of thing The best I can come up with so far, is to use lookup tables. Hopefully someone else could come up with a simpler suggestiom One table called "Times" as follows 7A 7 AM 73A 7:30 AM 73 7:30 PM 8A 8 AM 83A 8:30 AM 9A 9 AM 93A 9:30 AM 10 10 AM 103 10:30 AM 11 11 AM 113 11:30 AM 12 12 PM 123 12:30 PM 1 1 PM 13 1:30 PM 2 2 PM 23 2:30 PM 3 3 PM 33 3:30 PM 4 4 PM 43 4:30 PM 5 5 PM 53 5:30 PM 6 6 PM 63 6:30 PM And the other table called Game Type as follows P Practice With H Home Game vs. G Away Game @ with the code in A1 put this in B1 =VLOOKUP(LEFT(A1,IF(ISERROR(SEARCH("P",A1)),0,SEAR CH("P",A1)) +IF(ISERROR(SEARCH("H",A1)),0,SEARCH("H",A1))+ IF(ISERROR(SEARCH("G",A1)),0,SEARCH("G",A1))-1),times,2,FALSE)&" "&VLOOKUP(MID(A1,IF(ISERROR(SEARCH("P",A1)),0,SEAR CH("P",A1)) +IF(ISERROR(SEARCH("H",A1)),0,SEARCH("H",A1)) +IF(ISERROR(SEARCH("G",A1)),0,SEARCH("G",A1)),1),G ame_Type,2,FALSE) It returns the things below, but it does not yet have the last team information because of the difficulties I noted above. It may give you some ideas though since you understand the codes better than I do. You may be able to slice off the appropriate last characters of the code, and do another lookup in another table, and concatenate this last bot onto the formula above. Hope this helps in some way. 7 AM Practice With 10:30 AM Home Game vs. On Fri, 21 Oct 2005 07:35:07 -0700, Herman wrote: you are correct some of the other codes are as follows 7AP4 = 7AM PRACTICE WITH 4 103H2 = 10:3OAM HOME GAME VS. 2 73AG3 = 7:30 AM GAME @ 3 73HN1N = 7:30PM HOME GAME VS N1 IN NEWCASTLE The last character always refers to location and if no alpha character then default is default location A there are 5 locations with only 4 being identified as 5 = blank time can be up to 4 characters with default always being a PM time except as follows 7A = 7am, 73A = 7:30 am, 8A = 8am, 9A = 9am, 10 = 10am as no PM on Saturdays or Sundays same applies to 11. 103 = 10:30 am, 11 = 11am, 113= 11:30 am, 12 = 12pm, 123 = 12:30pm, 1 = 1pm, 13 = 1:30pm and so on the next character in the code of which there is only 3, refers to the event P = practice H = Home game G = Away game the next character(s) refer to the team which go anything from 1-22 as well as N1, N2, N3, O1 and O2 hence you 7 charater code 103GN1O which equates to 10:30 away game vs. Newcastle 1 in Orono and the date at the top of the column tells ya the date.. hope this helps.. "Richard Buttrey" wrote: Can you give a bit more detail about the 3-7 character text items. I'm assuming you can have something like 1P4 meaning 01:00 a.m., but how do you get up to 7 characters? Is the "P" code always a one character or more? and how many codes are there in this position. Similarly with the "4" code. how many people are there? up to 99 or more? Finally what is "Courtice". Is this always the same or is this to be derived from a Lookup table based on the who? Rgds On Fri, 21 Oct 2005 05:49:11 -0700, Herman wrote: I have a range say A4:A200 in cell A3 is the date and in the range there is a bunch of cells that have a 3 up to 7 character text that equate to a time, event, who and where code. I need something that will take the code and put that code in a oneliner ie code is 11P4 one liner should read today's date the 11 = 11am the P =practice and the 4 is who and because there no character after the 4 the location is known as Courtice with each peice of info in the one liner going in it's own seperate cell any help on this would be appreciated __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set up Auotomatic date and time entry in an adjacent cell | Excel Discussion (Misc queries) | |||
Text in formula bar is not displaying in cell | Excel Discussion (Misc queries) | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
How to convert DATE and TIME to text | Excel Worksheet Functions | |||
Help inserting a Cell Value in a Text Cell | Excel Worksheet Functions |