+ Reply to Thread
Results 1 to 7 of 7

Extracting text into desired format in Excel

  1. #1
    Registered User
    Join Date
    01-06-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    19

    Extracting text into desired format in Excel

    Hi there,

    I have a text importing query.
    Please see the text quoted below.
    What I want to do (may not be possible) is end up with the following output:

    A1 - TIMOCRACY
    A2 - WOR ROM (IRE)
    A3 - PORTSIDE (IRE)
    etc..

    B1 - Robert Walford, the rider of TIMOCRACY, reported that the gelding was unsuited by the good to firm ground. C34
    B2 - Mr P.J. Tolman, the rider of WOR ROM (IRE) reported that the gelding hung left throughout. D49
    B3 - Robert Thornton, the rider of PORTSIDE (IRE), which was pulled-up, reported that the gelding was never travelling. D49
    etc..

    Added bonus would be to get the Date entered into C1, C2, C3 etc.

    Few problems I forsee with this are that not all the setences are on 1 line. Pasting that text into excel would return some setences over 2 lines and 2 separate cells. I would need them combined in 1 cell.


    Uttoxeter, 4 October 2009

    Robert Walford, the rider of TIMOCRACY, reported that the
    gelding was unsuited by the good to firm ground. C34

    Mr P.J. Tolman, the rider of WOR ROM (IRE) reported that the
    gelding hung left throughout. D49

    Robert Thornton, the rider of PORTSIDE (IRE), which was pulled-up, reported that the gelding was never travelling. D49

    J.W. Farrelly, the rider of COOLDINE LAD (IRE) reported that the
    gelding bled from the nose. C34

    Kelso, 4 October 2009

    Stewards considered the running of SIERRA PEAK (IRE) ridden by G Lee and trained by M Todhunter, who started joint favourite and pulled up and having received a report that the trainer was unable to offer any explanation and in the absence of any other relevant information, they decided not to hold an enquiry. The Stewards ordered SIERRA PEAK (IRE) to be routine tested.

    T Murphy, the rider of MARCEL (FR), reported that the gelding lost its action and pulled up.

    P Callaghan, the rider of SYD’S PRINCESS, reported that the filly suffered interference on the stands bend.

    B Hughes, the rider of FLYING FEATHERS (IRE), reported that the mare ran too free.
    Please let me know if you think this is possible and if you know how to solve the problem. I would be very grateful.

    Happy to answer more questions
    (original link to text: http://www.bha.org/inside_horseracin...hyranbadly.asp )

    Thanks very much in advance.

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

    Re: Extracting text into desired format in Excel

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    I suspect based on your narrative you may be able to use a RegExp UDF to extract the info you want but first we would need to see how the data is really stored etc... your post is not that clear (to me at least).

    Not saying I will be able to answer this but whomever does will find a sample file quite helpful.

  3. #3
    Registered User
    Join Date
    01-06-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Extracting text into desired format in Excel

    No problem Donkey Ote.
    I have attached a sheet with Before and After tabs.
    In the Before tab you see the text as copied and pasted from the following site:
    http://www.bha.org/inside_horseracin...hyranbadly.asp
    I forsee problems with some of the lines as the sentence is broken over 2 rows, hopefully this is not an issue.
    I will be pasting in text daily, so if it's possible to make it repeat the process when text is pasted in, that would be superb.

    In the after tab you can see how ideally I would like the text structured if possible at all.

    I guess this might be quite a complex problem, but I hope not.

    Thanks in advance,
    Martin

    Any more questions please ask, apologies if its not clear.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-06-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Extracting text into desired format in Excel

    Bump, does anyone know the answer to this please?

  5. #5
    Registered User
    Join Date
    01-06-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Extracting text into desired format in Excel

    Bumping to see if anyone can solve.

  6. #6
    Forum Contributor
    Join Date
    12-02-2008
    Location
    India
    Posts
    118

    Re: Extracting text into desired format in Excel

    Wow! this is gonna get really problematic in future if 2007 n 2003 versions continue to remain uncompatible!

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Extracting text into desired format in Excel

    See if the attachment works for you. Do Alt+F8, run macro x.
    Attached Files Attached Files
    Entia non sunt multiplicanda sine necessitate

+ 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