+ Reply to Thread
Results 1 to 7 of 7

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

  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

  2. #2
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

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

    Hi use the text to columns command with spacebar as separator
    Regards

    tom1977

    If You are satisfied with my solution click the small star icon on the left to say thanks.

  3. #3
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

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

    Have you tried using "text to columns"? You can find it under the "Data" ribbon.
    Sincerely
    S?ren Larsen

    "Give a man a fish, and you'll feed him for a day. Give a man a fishing rod, and he'll steal your yacht!"

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

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

    Thanks Tom & Soren
    the text to columns works really well except where the destinations have multiple words - eg:
    17/11/2011 12:16 2085005426 Outer London 00:00:43 0.01
    17/11/2011 16:00 2085005426 Outer London 00:01:23 0.02
    23/11/2011 10:05 1248724247 Bangor (N Wales) 00:00:35 0.01
    23/11/2011 13:01 2085005426 Outer London 00:00:52 0.01
    24/11/2011 10:50 2085005426 Outer London 00:00:54 0.01
    25/11/2011 12:30 2076449044 Inner London 00:00:27 0.01

    Any idea how to get around that one??

    Thanks
    Andy

  5. #5
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

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

    You would have to manipulate the data afterwards; should be simple. If you upload a sample, I'll give you an example.

  6. #6
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

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

    After using text to columns select columns D (with the first word of city name) and run this code
    Please Login or Register  to view this content.
    If You have the city name which will be divide in more than 3 cells this code should be change a little

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

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

    Thanks Søren, i should be ok. Not used the text to columns function before. It'll come in handy in the future!

    cheers!

+ Reply to Thread

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