cleaning address list of stray characters and bad spacing

[mikenola]

Hey All,

Here is my problem, I have millions of addresses in multiple workbooks that were supplied to me.

The data is currently in two columns, colA Name colB Address.

ColA is fine like it is. ColB is the problem.

Right now I am trying to clean up ColB so it can be separated out as Street, City, Zip

The problem is cleaning the contents of misspellings and missing spaces between (words concatenated) by the source export (or data input)

Using the built in Excel text to columns, sort and filter has not really reduced the problems.

here is an example of the biggest problem
[TABLE="width: 500"]
<tbody>[TR]
[TD]

<colgroup><col width="408"></colgroup><tbody>
[TD="width: 408"]429 CHRISTOPHER AVE T2L14GAITHERSBURG MD 20879
[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


That 14Gaithersburg could be anything 14GGaithersburg, 14LeGaithersburg,
253b-tGaithersburg, etc.

I do have a list of all the cities that should be a possible valid entry in each state book. What I am thinking is to find a way to use that list as a range search and then place a space between the legitimate City Name and the mistyped ones.

any suggestions on how I can consistently make this happen? The good news is that the state code is always correct in each workbook and is the second to last "word" (from the right) in each string. The zip is always the last word.

any help would be appreciated

Thanks

Mike

 

[JLGWhiz]

I really do appreciate all the effort.

This version throws invalid argument or procedure call.

I don't know if it is legal to do here but if you want to create a throwaway email addy I will send you this book so you can see what it is doing....

If you can tell me what line of code is highlighted when the debug button is clicked on the error message dialog box, it would help me to isolate the cause. I don't really need to see your workbook for this type of procedure. I just need to know which columns you are working with, and you have provided that information along with the data content of the columns. The error message that it displayed indicates that it cannot find a requisite value of some type in order to complete an execution. If I know which line it occured on, I can find the problem. Also, specify which version of the code you were running at the time. I believe the last one was version 3.

 

[mikenola]

JLGWhiz,

I rebooted the machine and the error went away.

That code words, mostly. Your work has saved me hundreds of hours. I really do thank you for the effort.

I am going to filter out everything but the lines and see if there is some pattern to the ones it misses. I will post those results if any here.

In case any one else reads this thread what I have found so far

If you run the code against a list once and it does not completely fix everything, you can run it again, but that introduces additional blank spaces and may not catch what it missed the first time.
If that happens run =trim() on the results to clean out all the excess spaces.

check the city list for spelling and order very carefully.

check the data for things like MT and Mount for the city names.

check for singular and plural common names like Silver Spring vs. Silver Springs... that last s makes a difference.

after you run the code, use the text tools to split the data into NEW cells. Then use Filter to see if any of the offending data was missed. Make a list of the city names that failed to split.

 

[JLGWhiz]

JLGWhiz,

I rebooted the machine and the error went away.

That code words, mostly. Your work has saved me hundreds of hours. I really do thank you for the effort.

I am going to filter out everything but the lines and see if there is some pattern to the ones it misses. I will post those results if any here.

In case any one else reads this thread what I have found so far

If you run the code against a list once and it does not completely fix everything, you can run it again, but that introduces additional blank spaces and may not catch what it missed the first time.
If that happens run =trim() on the results to clean out all the excess spaces.

check the city list for spelling and order very carefully.

check the data for things like MT and Mount for the city names.

check for singular and plural common names like Silver Spring vs. Silver Springs... that last s makes a difference.

after you run the code, use the text tools to split the data into NEW cells. Then use Filter to see if any of the offending data was missed. Make a list of the city names that failed to split.

Thanks for the feedback, mikenola. I had not considered that there would be more than one instance of a city name in a single cell. The code, indeed, would not affect the second instance unless ran twice, or more. This was a first attempt to use some of the code in the fashion that it is used in this procedure, so it is a learning experience for both of us. As in all cases, the better the description of the problem, the better the solution.

Regards, JLG

 

[arthurbr]

Cross post http://www.excelkey.com/forum/viewtopic.php?f=2&t=1807