Parsing out data that is inconsistent in length

[zookeepertx]

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

[Dal123]

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.

[zookeepertx]

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

[Marc L]

Hi !

Easy with at least an original source data file …

[zookeepertx]

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

[Marc L]

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 …

[zookeepertx]

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!

[Jeff Ho]

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.

[Marc L]

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 !

[zookeepertx]

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