+ Reply to Thread
Results 1 to 6 of 6

Extracting specific data from large inconsistent strings

  1. #1
    Forum Contributor
    Join Date
    06-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2019 Version1808
    Posts
    300

    Extracting specific data from large inconsistent strings

    Hello Experts,

    I have large data running in row but in single column as follows :-

    BELLA TRAVEL 17/34 Redmyre Rd Burwood NSW 5612 Phone:02 4567 1234 Fax: 02 4567 1111 Manager: King Wood Director: Paul Front Email: [email protected] Licence No: 4576
    ROLAND TRAVEL
    Member Eagle Business Group
    Email: [email protected] Phone 02 7645 0956 Fax:7645 4455 Manager: Debbie Short
    71 River St NSW 2034 Licence No: 76447956


    I am tring to extract data for meaningful use by putting "Phone:" under Phone heading , "Fax:" under FAX heading, "Manager:" under Manager heading, and so for director, Emails and Licence number.
    All data is very inconsistent but one thing is common company name are in uppercase and other details end with (semilcolon) :
    lease see test2 file as ansample (Excel 2007)
    Can you please edit functions?
    Thanks
    Karnik
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Extracting specific data from large inconsistent strings

    Hi Karmik

    I think it must have been you I was speaking to about 10 years ago. I was trying to get this person to type in some code, and it was only after a very long, frustrating time I realised that we were each using 1 name to mean a different thing! This is a semicolon";" and this is a colon ":"

    There is no quick fix for your situation, because as soon as you "tell" Excel the rule - the rule gets broken.

    One tip that just might speed things up a little is to search and replace all occurrences of "Phone:" with say "^", all occurrences of "Fax:" with say "+" (and so on for all the constants). You can then use text-to-columns using the separators in turn.

    Whilst writing this I have been thinking of a VBA solution.
    Run a find and replace as above for all the constants, but replacing by (say) |#11,|#12 etc (The"|" will make a visual break)
    VBA can find the end of the company name (by checking the 2nd character after a space and if it is not a capital then the Name must have ended)
    Probably some VBA can be written to fing the position of the Post code (zip code?)
    Someone then will have to go through and decide on a title for such things as the "Member of" and mark the start of all such occurrences with (say) #1 (Volunteers for this job, please line up)
    Do the same for any other types of data
    Then some VBA will run through and put everything neatly into their respective column. Job done

    Let me know if you think it's feasible (well yes, of course it's feasible - I've just done the feasibility study ) - is it something you want to run with?

    Regards
    Alastair
    Last edited by aydeegee; 08-30-2013 at 10:20 AM.

  3. #3
    Forum Contributor
    Join Date
    06-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2019 Version1808
    Posts
    300

    Re: Extracting specific data from large inconsistent strings

    Hi Aydeegee,
    Thanks for your time and efforts
    yes as rightly stated I need to look in to VB more closely, I have borrowed VB book for Dummies and hopefully I will be able have some direction.
    Thank you for your suggestion
    Cheers
    Karnik

  4. #4
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Extracting specific data from large inconsistent strings

    Hi Karnik

    Good luck with your VB book!

    I just love to prove a point - and I was right - there is no quick fix!

    However, I have gone some way to getting a solution (in that it works for the 4 samples that you have given).

    I have got round the problem of having no order in the file. BUT my system relies on the name being in CAPITALS, followed either by a group (NOT in capitals) or the address (NOT in capitals). For each line with a group, you have to mark the end of the group and beginning of the address with a carat (^) This has to be done before running the program. If the group is placed anywhere else, then tough!

    If you look on the Replace sheet you will see my attempts at dealing with Manager / Managers, Licence No: / Licence No. / Licence / No.

    If you copy your data on to any sheet (except Replace(!) ) and run the macro (press Ctrl+Shift+Q ) you will see how it works. (Be aware that my macro changes the data so make sure that you do not work on data that has not been backed up!)

    To see the code, press Alt+F8 > Step into

    If you want any of the logic or VBA explaining - let me know.

    Regards
    Alastair
    Attached Files Attached Files

  5. #5
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: Extracting specific data from large inconsistent strings

    see if this helps.
    Attached Files Attached Files
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  6. #6
    Forum Contributor
    Join Date
    06-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2019 Version1808
    Posts
    300

    Re: Extracting specific data from large inconsistent strings

    Hi IcestationZbra,
    I appreciate your time and efforts, your suggestion and function/array works perfectly, it saved numerous man hours !
    Thanks genius,
    Cheers
    Karnik

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Extracting specific data from large raw data
    By tekobayashi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-21-2012, 06:23 AM
  2. Extracting numbers from inconsistent text strings
    By netguru in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-25-2012, 03:20 AM
  3. [SOLVED] Extracting specific rows from a large spreadsheet
    By MelindaCapri in forum Excel General
    Replies: 5
    Last Post: 09-06-2012, 11:44 AM
  4. Extracting Large strings of text from a cell?
    By FrancoDuckRiver in forum Excel General
    Replies: 0
    Last Post: 07-20-2011, 11:38 AM
  5. String splitting for inconsistent strings
    By Richard in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-21-2006, 11:00 PM

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