+ Reply to Thread
Results 1 to 7 of 7

Extract data from a string - Varying length, between spaces

  1. #1
    Registered User
    Join Date
    09-06-2009
    Location
    Sussex, England
    MS-Off Ver
    Excel 2003 / Pocket Excel
    Posts
    6

    Angry Extract data from a string - Varying length, between spaces

    Hi,

    I have a string (as below - Call them A1:A4) which I would like to seperate into 4 columns (Call them B1:E4).

    I have successfully seperated the first part using MID (It's always 5 digits) but the second part has a varing length which then impacts on the third and fourth parts of the string.... Any ideas?

    87261 WIMBLEDON 10:08 10:10
    87169 NEWMALDEN PASS 10:13
    87171 SURBITON PASS 10:15
    87177 HMPTNCTJN PASS 10:16

    To add to this I am using the POCKET PC version of Excel which does not have all functions so at the moment I am limited to which functions I can use (Can you add functions to the PPC?).

    As you will see one thing common to the above strings is seperation by spaces.

    Thanks for any help. L.
    Last edited by Lee.shop; 09-11-2009 at 04:55 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Extract data from a string - Varying length, between spaces

    Does Pocket PC have Text to Columns feature ? If so you can run Text to Columns on A1:A4 and use Space as delimiter.

    if not...(again untested given don't have PocketPC)

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-06-2009
    Location
    Sussex, England
    MS-Off Ver
    Excel 2003 / Pocket Excel
    Posts
    6

    Cool Re: Extract data from a string - Varying length, between spaces

    It sort of works

    So just to understand what has been done and hopefully rectify the last column which won't copy along....

    Row 1 gives
    A1 87261 WIMBLEDON 10:08 10:10
    B1 87261
    C1 WIMBLEDON
    D1 10:08
    E1


    TRIM - Removed the spaces either side of the individual word
    (so tidied up what the code found)

    MID - Locates a start point (in this case the point being " " (space))

    SUBSTITUTE - Hmmm Not sure what part this plays?

    REPT - Repeats a section.... so tells MID to continue until a space comes along??? - Does the 100 indicate a max of 100 digits?

    COLUMNS - I am totally lost by this point?


    Sorry to be pain!!!


    BTW PPC doesen't apear to have Text to Columns but all of the above functions are available. L.

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Extract data from a string - Varying length, between spaces

    The formula should work assuming you have a single space between each piece of text. If there might be additional spaces try adding another TRIM function to Donkeyote's suggestion, i.e.

    =TRIM(MID(SUBSTITUTE(TRIM($A1)," ",REPT(" ",100)),1+(COLUMNS($B1:B1)-1)*100,100))

  5. #5
    Registered User
    Join Date
    09-06-2009
    Location
    Sussex, England
    MS-Off Ver
    Excel 2003 / Pocket Excel
    Posts
    6

    Re: Extract data from a string - Varying length, between spaces

    It's not happy about the brackets now...

    I'm off to bed before I do something silly and delete the lot.... LOL!

    L.

  6. #6
    Registered User
    Join Date
    09-06-2009
    Location
    Sussex, England
    MS-Off Ver
    Excel 2003 / Pocket Excel
    Posts
    6

    Wink Re: Extract data from a string - Varying length, between spaces

    SORTED!!!!

    Cheers for all your help guys, I had another look at this using a PC and Excel 2003 - The first formula worked as soon as I opened the file, I then copied it over to the PPC and it's looks to be working on there too now!

    Excellent forum!!!! L.

  7. #7
    Registered User
    Join Date
    06-11-2009
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    Excel 2003
    Posts
    9

    Smile Re: Extract data from a string - Varying length, between spaces

    Quote Originally Posted by daddylonglegs View Post
    The formula should work assuming you have a single space between each piece of text. If there might be additional spaces try adding another TRIM function to Donkeyote's suggestion, i.e.

    =TRIM(MID(SUBSTITUTE(TRIM($A1)," ",REPT(" ",100)),1+(COLUMNS($B1:B1)-1)*100,100))
    Daddylonglegs:
    Every solution can be shared/used by multiple users. I was looking for exactly same (with slight difference) code & it solved my problems without posting!
    Last edited by Xalaal2009; 10-01-2009 at 02:51 PM. Reason: fix typo
    Abdi J. (Xalaal2009)
    Raiser's Edge Database App. Support/DBA
    Children's Hospital Foundation
    Saint Paul, Minnesota, USA
    http://www.araarso.com

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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