Hi,
this is my first post on the forum, though I've found answers to numerous queries on here. I'm hoping some of you guys can help...
I'm working on some data that's been imported from text file and need to separate the data into separate columns - date/time/dialled no/destination/duration HHMMSS/cost.
below is a sample of some of the data:
29/11/11 19:19 01612827727 Manchester 00:00:36 0.01
30/11/11 16:53 01612827590 Manchester 00:00:13 0.00
30/11/11 17:01 01612827727 Manchester 00:00:11 0.00
30/11/11 19:20 01612827727 Manchester 00:00:26 0.00
01/11/11 10:32 01179466039 Bristol 00:01:01 0.02
02/11/11 12:52 01780751625 Stamford 00:00:20 0.01
04/11/11 11:12 01179466039 Bristol 00:00:36 0.01
07/11/11 08:46 01179466039 Bristol 00:01:24 0.02
07/11/11 15:35 01913742888 Durham 00:00:24 0.01
08/11/11 10:32 01377271528 Driffield 00:00:41 0.01
08/11/11 12:05 01377271528 Driffield 00:08:42 0.14
08/11/11 15:28 01403799101 Horsham 00:00:27 0.01
09/11/11 10:52 01283711288 Burton-on-Trent 00:00:46 0.01
10/11/11 09:43 01405740338 Goole 00:00:37 0.01
10/11/11 12:39 01179466039 Bristol 00:00:35 0.01
14/11/11 14:22 01618117946 Manchester 00:00:56 0.01
14/11/11 14:26 01618117946 Manchester 00:00:56 0.01
14/11/11 14:30 01618117946 Manchester 00:00:56 0.01
14/11/11 14:34 01618117946 Manchester 00:00:56 0.01
I can use =left, =right & =mid to get to most of the info, but since the text part of the data is of variable, i can't seem to extract the destination and duration info. In this sample,the text all seems to start at character 27 but that would not always be the case as some telephone numbers might have have either more or less than 11 digits eg international numbers etc...
Thanks
Andy
Bookmarks