Excel parsing data problem $100 dollars to whoever can solve this

**velocitygraphix** · 04-25-2013, 08:53 PM

Ok forum, I need a spread sheet to be parsed. I have attached an example. The data on the left of sheet 1 needs to be parsed into separate columns like on sheet 2. There are 3 address fields where data must go if its available, and the order ID number needs to be placed to the right in a separate cell. Please also note that there is an "Order ID:" and an "Order Item ID". I only need the Order ID. The new columns should be like this
Name---Street Address 1----Street Address 2----Street Address 3----City,State----Zipcode----Order ID.
There have been many tried and come close but not exactly what I need. Anyone who comes up with the solution, I will paypal 100 dollars to. This would help our business out so much, that I am asking for some help!

**JBeaucaire** · 04-25-2013, 11:39 PM

This macro seems to replicate your data correctly:

Please Login or Register  to view this content.

**Ed_Collins** · 04-26-2013, 12:20 AM

Send the money to Jerry. His code looks to be fine. Nice job.

I have a question on the data format. A SHIP TO address and a SHIPPING ADDRESS are both listed. Why? And if they differ, which address takes precedent?

Note that in the original example given, the first SHIP TO address (for John Smith) is Anywhere Town TN, but the SHIPPING ADDRESS for him is Anywhere Town, FL. (But it's the same zip code, so I assume one is simply a typo.)

Too bad I didn't see this post first. Although my code would have looked completely different, I also could have duplicated the data exactly. I also could have used the $100.

**velocitygraphix** · 04-26-2013, 12:28 AM

Great! You pretty much have it. The only tweak is the zipcode. When a nine-digit zipcode (90210-1250) is in the address, it leaves 4 digits in the City/ST column and has -1111 for example in the zipcode column. If you can solve that issue you have 100 dollars coming your way. PM me your paypal address. Thanks so much for your help! I do appreciate it so much!

**velocitygraphix** · 04-26-2013, 12:33 AM

Originally Posted by Ed_Collins

I have a question on the data format. A SHIP TO address and a SHIPPING ADDRESS are both listed. Why? And if they differ, which address takes precedent?

When the data is processed through an order form, the buyer may elect to have the item shipped somewhere else for a gift. So the Ship TO is really a billing address for some reason. Not sure why the packing slips are generated that way.
Other than the zip code issue, the code above works perfect!

**Ed_Collins** · 04-26-2013, 12:37 AM

Ah, you must have run the code on actual data. The reason Jerry didn't account for a nine digit zip code is because your three examples didn't have a nine digit zip code.

I know Jerry will be able to fix this easily. He's only grabbing the last five digits to arrive at the zip.

**velocitygraphix** · 04-26-2013, 12:56 AM

Originally Posted by Ed_Collins

Ah, you must have run the code on actual data. The reason Jerry didn't account for a nine digit zip code is because your three examples didn't have a nine digit zip code.

Yep, wanted to test to see and noticed on a few rows the zip was different. I am sure it will be an easy fix for someone who could parse this mess

**JBeaucaire** · 04-26-2013, 11:08 AM

I've added a test to spot the "-" character and use 10-digits for zip in that instance.

Please Login or Register  to view this content.

If that takes care of your question, please select Thread Tools above and mark this thread as SOLVED.

**JBeaucaire** · 04-27-2013, 10:05 AM

Originally Posted by velocitygrafix

I wanted to see if you had a quick solution to replacing the full state names in those columns to abbreviations when not already abbreviated? I have a sheet with all the full state names in one column and the abbreviation in another. I didnt know if its something that could be added to the current macro or have to run a whole new. Thanks again for your help!

Possibly. Add examples of this to your sample sheet and add that reference sheet with states and abbreviations. We can hide that sheet, but it's easier if it's there. Repost that sample file.

**velocitygraphix** · 04-27-2013, 11:16 AM

Thanks it works great now! I noticed that some people include the whole name for their state such as Florida. Others include just the abbreviation of FL. Is there a way to find the full state names in that column and replace with the abbreviated one? I have all the states and abbreviations on another sheet in separate columns if we can link to it somehow. Perhaps add to the current macro or have a formula for only the column that shows city, State. Thanks~

**velocitygraphix** · 04-27-2013, 12:43 PM

I attached the file with the states and abbreviations on sheet 3. My current code for finding and replacing the full names is

Please Login or Register  to view this content.

looking for matches from the sheet of abbreviations named States.

If the search and find can be integrated to the column with city,st within the macro, that would be ideal. Not sure how to code the macro to include that since some cities have state names (New York, NY) for example. If you can think of any solutions, I am very opened to trying them. Thanks~

**JBeaucaire** · 04-27-2013, 05:53 PM

We could just split the City and State into separate fields....?

**velocitygraphix** · 04-27-2013, 07:00 PM

Originally Posted by JBeaucaire

We could just split the City and State into separate fields....?

Yes we could if possible. However, extracting the city and state separately may be a problem since some cities have two names or more, and some states have two names. Not sure how the macro would know which part to separate....however if you have a solution or know of a code that could be used, I would gladly implement it. Thanks again!

**JBeaucaire** · 04-27-2013, 07:24 PM

Hm, yeah, some cities are multiple words and some states are two words, too. Interesting puzzle. Not all that trivial. Have I mentioned how much parsing address is a pain?

**JBeaucaire** · 04-27-2013, 07:40 PM

Ok, try adding this to the end of the macro:

Please Login or Register  to view this content.

NOTE: This WILL mess up any addresses that have states as City Names, like Oklahoma City, OK.

**velocitygraphix** · 04-27-2013, 09:22 PM

Originally Posted by JBeaucaire

Ok, try adding this to the end of the macro:

Please Login or Register  to view this content.

Is that supposed to update the state name to an abbreviation? It put the city and state together in a separate column to the right, but states were not changed. The full names stayed full names and abbreviated one stayed abbreviated...

**velocitygraphix** · 04-27-2013, 09:24 PM

Have I mentioned how much parsing address is a pain?

I completely understand bro~

**JBeaucaire** · 04-28-2013, 12:27 AM

I added that code as shown to the original macro. Post another example if needed, it works on the one posted so far. That's with the states on Sheet3.

Please Login or Register  to view this content.

Note: you can add common misspellings to the Sheet3, like Tenessee=TN

**protonLeah** · 04-28-2013, 12:41 AM

I didn't realize that you double posted this problem: here. So I guess I wasted my time.

**velocitygraphix** · 04-28-2013, 07:13 PM

Originally Posted by protonLeah

I didn't realize that you double posted this problem: here. So I guess I wasted my time.

It was originally posted in the wrong forum. Sorry about that. I do apologize and do appreciate your help!

**JBeaucaire** · 04-30-2013, 05:55 PM

So, your original request plus two additional features all completed. Ready to close this out?

**velocitygraphix** · 05-08-2013, 05:43 PM

Originally Posted by JBeaucaire

So, your original request plus two additional features all completed. Ready to close this out?

Yep, thanks for all your help! Let me know the next step. Thanks again!

**JBeaucaire** · 05-08-2013, 07:36 PM

Click on the USERCP link above in the menu bar to see your user Control Panel including private messages. I've sent you a private message.

You can click the link in my signature below, too, to access my private Excel site and contact me through there. Glad it's working for you.

Excel parsing data problem $100 dollars to whoever can solve this

LinkBack

Thread Tools

Rate This Thread

Display

Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Re: Excel parsing data problem $100 dollars to whoever can solve this

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions