Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Herman
 
Posts: n/a
Default 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   Report Post  
Richard Buttrey
 
Posts: n/a
Default 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   Report Post  
Herman
 
Posts: n/a
Default 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   Report Post  
Richard Buttrey
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I set up Auotomatic date and time entry in an adjacent cell Geoffrey Excel Discussion (Misc queries) 2 September 1st 05 01:03 PM
Text in formula bar is not displaying in cell Mike Excel Discussion (Misc queries) 0 August 29th 05 09:47 PM
How do I calculate if a date is in a certain time frame? Pe66les Excel Worksheet Functions 19 August 27th 05 11:07 PM
How to convert DATE and TIME to text ice_pack Excel Worksheet Functions 3 August 15th 05 10:30 AM
Help inserting a Cell Value in a Text Cell Dave Excel Worksheet Functions 5 March 5th 05 10:06 PM


All times are GMT +1. The time now is 06:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"