Results 1 to 7 of 7

Extracting Text from a string of Text & Digits of variable length

Threaded View

  1. #1
    Registered User
    Join Date
    02-08-2012
    Location
    Lancaster
    MS-Off Ver
    Excel 2007
    Posts
    3

    Extracting Text from a string of Text & Digits of variable length

    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
    Last edited by hastex; 06-06-2012 at 06:40 AM. Reason: error in description

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1