1

I do have a problem which I cannot solve, although I've searched a lot.

I have the following address: 1021 CHATEAU WOODS PKWY DR, CONROE, TX 77385

I would like it to look like this: 1021, CHATEAU WOODS PKWY DR, CONROE, TX, 77385

I've found some formulas on the web which use the find and replace functions in Excel, and all solutions assume that I should select as "old text" cell A1. However in Excel 2010 that returns an error.

Is there any way that I could add a comma after the first "word", as it is variable in length, and before the last one, which is a zip code?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Alexander Corvin
  • 35
  • 1
  • 5
  • I've looked on Microsoft's Excel 2010 reference, however it does not work on the Mac edition of Excel 2010. http://office.microsoft.com/en-us/excel-help/substitute-function-HP010342927.aspx?CTT=5&origin=HP010342953 – Alexander Corvin Jun 01 '12 at 15:44
  • Why do you want to add commas in these locations? Are you looking to parse the address and split into components? – Matt Jun 01 '12 at 16:22
  • Yes. I looked up using mid left right functions which work quite well to a certain extent. It will not work well when I have a list of addresses, some of which lacking apartment number, or having only a street name and state. so far I was unable to find the right combination with the right functions to create a certain .... algorithm to extract the data correctly. – Alexander Corvin Jun 02 '12 at 04:49

1 Answers1

1

I'm going to post an answer to what I understand is the real question here, based on your comments above, mainly because the task of parsing an address using Excel functions alone is too tedious. (Trust me, I work in the address verification industry for SmartyStreets and have seen too many failed attempts with simple functions and regular expressions.)

You can continue to try adding commas like this, but you'll eventually find that it's not reliable because addresses come in many different formats, valid and invalid.

We've posted a simple algorithm for breaking an address into its components. It's a different route than where you're going, but you get the same -- actually a more accurate -- result. The problem with your existing addresses is that they are not yet standardized, so performing uniform operations on all of them will yield inconsistent results. (For example, in the address you give above, the "Pkwy" part should be expanded out to "Parkway" because it's not the street suffix; it's actually part of the name. Also, some streets names are numbers, which can be confusing when combined with a house number.)

You said you're missing information with some of your addresses. There are CASS-certified services that are authorized by the USPS to "fill out" the missing data, and standardize them. You can even get mailing discounts by processing your lists through such services. There are many providers, and I encourage you to find an affordable, easy one that you prefer.

One such service is LiveAddress, which will process Excel lists pretty easily and standardize your addresses. Parsing an address into components may be more easily accomplished using the API (if you have some programming knowledge -- but you can hook into it with VBA right in Excel).

Edit: I should let you in on a little secret... earlier last week we deployed a change that allows you to submit just one line (street line 1) to our API and it will still do its thing. This means that you don't have to program that algorithm I mentioned by yourself; instead, just perform the basic request using VBA (just a few lines of code).

If you have other questions about managing your addresses, comment and I'll see what I can do to help you further.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Matt
  • 22,721
  • 17
  • 71
  • 112