+ Reply to Thread
Results 1 to 10 of 10

Parsing out data that is inconsistent in length

  1. #1
    Forum Contributor
    Join Date
    06-25-2013
    Location
    Longview, TX
    MS-Off Ver
    Excel 2010
    Posts
    335

    Parsing out data that is inconsistent in length

    Hello!

    I'm working on a real bear of a macro and am stuck on a problem.

    Part of the report I'm starting with has a name on one row twice (separated by multiple spaces), address on the next twice (separated by multiple spaces), city state zip twice (separated by multiple spaces) on the next row and phone # twice (separated by multiple spaces) on the last row. Sometimes the data isn't exactly the same within each row - for example: the initial name on the row may not be the same as the secondary name on the row-like John Smith Mary Smith. Same situation with the address row, the city/state/zip row and the phone # row.

    I need to end up with each "block" of data together on another sheet. I'

    On the original data:
    Each row has room for a total of 131 characters.
    In the "name" row, fields 10-63 are for the initial name, then a true blank spot, then 14 unneeded fields, then the next 53 fields are available for the secondary name on that row.
    On the "address" row, fields 10-63 are for the initial address, then a true blank spot, then 14 unneeded fields, then the next 53 fields are available for the secondary address on that row.
    NOW COMES THE PROBLEM!
    The city state zip for the initial set of data populates fields 10-63 first, then the second instance of city state zip populates fields 79-131
    Each city, each state and each zip need to be placed into separate cells of the results. The state is always 2 characters and the zip is always 5 characters and there's always 1 true blank space between city and state and between state and zip. BUT, I never know how long the city name is or if it's 1 word or 2, so I don't know how to tell the macro what criteria to use to break them up. I was thinking ther might be some way to tell it to start at the end, look backwards to the zip and pull those 5 digits to put in the results, then skip a cell (still going backwards), then get the next 2 letters to put in the results, then get everything else as the city.

    The macro renames the original sheet as "Data" and creates a new sheet named "Result".
    Here's the coding I have so far. It just puts the whole city state zip in each cell on the Result tab because I had to put SOMETHING so I could test it, LOL.

    Edited to say that the 2 red lines (that start with "Sheets") successfully put the name and address where I need them to go. The 2 green lines successfully put the second name and address where I need them to go. The rest needs to be corrected to parse out the city state zip into 3 cells for each block of data.

    Please Login or Register  to view this content.
    I'm attaching a small sample workbook.

    Any help will be GREATLY appreciated!

    Jenny
    Attached Files Attached Files
    Last edited by zookeepertx; 06-01-2018 at 09:54 AM. Reason: To (hopefully) add clarity

  2. #2
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    282

    Re: Parsing out data that is inconsistent in length

    Could you use the left function as you know the Zip will always be 5 digits?

    EDIT: Sorry I just re-read your post and it'd be the right function.

    Actually if I'm reading it correctly you've got 3 results in a single cell; City, State, ZIP; in that order I think. It may be best to use the split function which returns an array of items separated by a delimiter of your choosing. You could use a blank space as the delimiter (thing that separates them) and have the 1st subscript/ index as the City, the Second as the State and the 3rd as the ZIP.
    Last edited by Dal123; 05-31-2018 at 05:40 PM.

  3. #3
    Forum Contributor
    Join Date
    06-25-2013
    Location
    Longview, TX
    MS-Off Ver
    Excel 2010
    Posts
    335

    Re: Parsing out data that is inconsistent in length

    Hi there

    The "last" part of each will always consist of 1 blank space, 2 characters for the state, 1 blank space, 5 digits for the zip. The variable is always going to be the city: it may be any length and there may or may not be more than 1 word in the city name. (Paris, White Oak, Winston-Salem, Pike's Peak) So, it's hard to figure out how to use a space as a delimiter, as it may split up the city name. That's what made me start thinking about counting spaces from the right and going backwards, but I can't figure out how to write the code for that.

    I may be missing something in your reply, though; you may have already addressed that problem.

    Jenny

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,607

    Re: Parsing out data that is inconsistent in length


    Hi !

    Easy with at least an original source data file …

  5. #5
    Forum Contributor
    Join Date
    06-25-2013
    Location
    Longview, TX
    MS-Off Ver
    Excel 2010
    Posts
    335

    Re: Parsing out data that is inconsistent in length

    Quote Originally Posted by Marc L View Post

    Hi !

    Easy with at least an original source data file …
    Hello!

    There's a workbook named "Address Problem2" attached to the first post here that has a sheet called "Data" that shows how the original data is configured and a second sheet called "Result" that shows how I need it to end up. Let me know if I need to give more information.

    Thank you for looking at my problem!

    Also, I've edited the code in my original post to indicate that the 2 red lines (that start with "Sheets") successfully put the name and address where I need them to go. The 2 green lines successfully put the second name and address where I need them to go. The rest needs to be corrected to parse out the city state zip into 3 cells for each block of data.

    Jenny
    Last edited by zookeepertx; 06-01-2018 at 09:53 AM.

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,607

    Re: Parsing out data that is inconsistent in length


    I doubt the orignal file is a workbook like this !

    Of course I can create a beginner level demo according to your attachment but it will fail with the real original file !
    As I'm pretty sure it is consistent in length …

  7. #7
    Forum Contributor
    Join Date
    06-25-2013
    Location
    Longview, TX
    MS-Off Ver
    Excel 2010
    Posts
    335

    Re: Parsing out data that is inconsistent in length

    Oh, no! The original report is the biggest mess I've ever seen! If it was a csv file, this whole thing would be easy, but instead of SOME type of delimiter, all the "gaps" between data are filled with however many SPACES it takes to fill the available room for that piece of data. And, no matter how many people we've talked to, there's NO way to get the information pulled as a delimited file!

    This has been one of the most difficult macros I've worked on, but I'm down to 1 weird problem to figure out, then I THINK I'm finished. YAY!

  8. #8
    Registered User
    Join Date
    04-14-2018
    Location
    Vermont, USA
    MS-Off Ver
    Office 365
    Posts
    72

    Re: Parsing out data that is inconsistent in length

    I can't say I understand how the sample of code you provided works because I don't see how the Mid function is extracting the right data from the Data sheet.

    But I just thought about the essential issue of constructing the city name by backing to it. In order to test the special cases you mention, I changed the Sold To address to "The City GA 55555" on the Data sheet. This code parses zip, state, and city from row 5, then deposits them directly below in row 6.
    Please Login or Register  to view this content.
    To be honest, I know this is rather clunky code (I really expected someone else to have provided a more elegant solution by now). And I have no idea if this addresses your issue adequately, but if it works, you can certainly better adapt it to your workbook than I could.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,607

    Re: Parsing out data that is inconsistent in length

    Quote Originally Posted by zookeepertx View Post
    Oh, no! The original report is the biggest mess I've ever seen! If it was a csv file, this whole thing would be easy, but instead of SOME type of delimiter, all the "gaps" between data are filled with however many SPACES
    As it's just an easy text file with a fixed length for each field, at child level logic so at very beginner level … Just attach it !

    And you ever do not need any code just using an Excel basics inner feature as I use each time I receive such a text file !

  10. #10
    Forum Contributor
    Join Date
    06-25-2013
    Location
    Longview, TX
    MS-Off Ver
    Excel 2010
    Posts
    335

    Re: Parsing out data that is inconsistent in length

    YIPPEE!! I've got it working!! (I must admit, I didn't figure out how to code it myself, but found an applicable solution online. It just needed a little tweaking.)

    In case anyone can use this information, here's the solution:

    From the code I posted above, I deleted the rows I've made blue below, since all they were doing was to put the entire City State Zip into the cells available for each City and State column -
    Please Login or Register  to view this content.
    Then, at the end of the macro, everything was filled out as it should be except that columns E and K each had the City State Zip in them, while F, G, L and M were blank.
    So, I added the following lines of code at the end of the macro:

    Please Login or Register  to view this content.
    So far, it's working on every order except the few where the original data is formatted:
    Name
    Address
    Unit #
    City State Zip

    But if they're not going to give me the information in a not-at-all consistant format, they're going to have to fix those on their own, LOL!

    Thanks for the input!

    Jenny
    Last edited by zookeepertx; 06-01-2018 at 02:33 PM.

+ 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. [SOLVED] Help Parsing variable length test
    By RM05067 in forum Excel General
    Replies: 11
    Last Post: 06-15-2016, 10:16 PM
  2. [SOLVED] Text parsing with variable length
    By dtrimble in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-11-2014, 03:36 PM
  3. Parsing A Number from An Inconsistent Note
    By ibillings00 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-12-2012, 11:45 PM
  4. Replies: 10
    Last Post: 08-15-2012, 10:20 AM
  5. Vlookup with inconsistent lookup value length
    By JuJuBe in forum Excel General
    Replies: 4
    Last Post: 02-05-2011, 03:32 PM
  6. Parsing inconsistent text strings into columns in an organized manner
    By jagman1990 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-17-2010, 03:48 PM
  7. Replies: 2
    Last Post: 10-23-2010, 04:54 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