Text to Columns With Inconsistent Data

[mbates]

Hello,

I searched for this issue but couldn't find it. I know it's bound to come up often.

Anyway, what I have is a column with rows of data which is not consistent throughout the file. Text to columns will break it up based on a space delimited, but my issue is that not every row is structured alike. For instance, I'm working with geographical locations. I have city names, state abbr., zip codes, followed by a company name. The problem is some cities have two names, such as "Idaho Falls". In another row it could be "Reno"...ir could even be spelled in all caps, like "RICHMOND".

I've attached a spreadsheet with an example of what I have and what I want. I believe I have covered each different row scenario. It even has Canadian zip codes which have an empty space in them. I have a few rows of data that also have leading blank spaces, but I can repair those in Access. Also, if it's easier or better to break this column of concatenated fields up in Access rather than Excel, that's not a problem.

I appreciate any help!

[mikerickson]

In situations like this, I find that there are too many variations to make a dependable macro that will do it all.

BUT, I do find these two event codes useful.
Double Click on a cell and the last word of that cell will become the first word of the cell to the right.

Right Click on a cell and the first word of that cell will become the last word of the cell to the left.

Please Login or Register  to view this content.

[mbates]

Hmm...thanks, but I don't think that will help me much. Would it be possible to simply identify the two letter state abbreviation and copy it to a new cell? That would help me some.

[mbates]

The constants that I can see would be:

If after the first space, the next two letters are followed by a space, then it's a state abbr.

Otherwise, if after the second space, the next two letters are followed by a space, then it's a state abbr.

Anything in front of those two conditions would be the city name. If I could get that much it would be a home run. I suspect it's doable. I just don't know how to write the code.

[mikerickson]

I think this UDF will help. It applies the following rules

1) The State is the first two letter word
2) The City is everything preceeding the state
3) The Zip follows after the City and the Zip is longer than 4 characters and composed of whole words.
4) The Company is everything following the zip.

Please Login or Register  to view this content.

In the attached, C11:F11 holds the array formula =Divide(A11), which is dragged down to row 16.

[mbates]

mikerickson,

You sir, are a genius. That's just awesome. You can't imagine how much easier you just made my work day. Sweet!!!

Thank you very very much!!!

[snb]

@mikerikson

FYI:

Split(mixedString) is equivalent to Split(mixedString, " ")

Join(Words) is equivalent to Join(Words, " ")

[mikerickson]

Split(mixedString) is equivalent to Split(mixedString, " ")

Join(Words) is equivalent to Join(Words, " ")

Yes they are. I find it easier to specify the default values than it is to remember what they are or to expect the maintainer of the code to remember all the defaults.

[shg]

I agree with Mike.

Ditto for using literal numbers instead of enumerations, and for passing arguments by position instead of by name.

[mbates]

I am very pleased with the code provided to me in this thread. It's going to save me lots of headache and time.

I did run across a few more variations in my file the the code doesn't handle. For instance I city name like "El Paso" will be put into the State Column as "El" and place the rest according to that placement.

I'm certainly not complaining. The number of records that I would have to manually change is likely around 50. I would have to do this only once a week so that's not terrible. The added functionality of the file is certainly worth the extra effort.

I'm only posting again to see if anyone of you genius coders would like to try and crack this by modifying the code that mike gave me to include a couple of conditions.

The part that searches through the string and selects the first two letter word to call the state needs to be conditional. With the extra variations I've found it may not even be doable.

I've included a spreadsheet with examples of the new variations and the code above with it's results.

Again, I am very pleased with the results thus far and I'll be glad to modify the remaining records manually if need be. It would be cool to have the code that covered all the conditions, too!

Thanks either way!

[snb]

Please Login or Register  to view this content.

[mikerickson]

To avoid confusing El Paso with a state, you could do something like

Please Login or Register  to view this content.

If nothing else, it would make the trouble inputs obvious. (i.e. no state returns an un-split string rather than an oddly split string)

[mbates]

Mike...your modified code works almost perfectly. It's close enough that I used a work-around to solve my last remaining issue. In situations where there is a city like Mount Sterling or Mount Rainier and the end-user that created the record used 'MT' for 'Mount', which is the case, then the code can't distinguish that from the state of Montana (MT).

Since I am already running this file through Access to do some other manipulations, I created an update query to find all instances of 'Mt' as an abbr for Mount and changed them to 'Mt.' and now the code works for every row.

Thank you all for your help! You are very much appreciated!