Register a SA Forums Account here!
JOINING THE SA FORUMS WILL REMOVE THIS BIG AD, THE ANNOYING UNDERLINED ADS, AND STUPID INTERSTITIAL ADS!!!

You can: log in, read the tech support FAQ, or request your lost password. This dumb message (and those ads) will appear on every screen until you register! Get rid of this crap by registering your own SA Forums Account and joining roughly 150,000 Goons, for the one-time price of $9.95! We charge money because it costs us money per month for bills, and since we don't believe in showing ads to our users, we try to make the money back through forum registrations.
The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread
 
  • Post
  • Reply
shotgunfilibuster
Jul 16, 2008
 I have a problem that I feel like should have an obvious, simple solution, but am coming up short. I have a column with address information in the format of "[City] [State] [Zip]" and need to separate them into their own columns in order to export it from Access into ODBC. It would normally be a simple matter of delimiting the column by spaces, but the problem is that cities like San Francisco don't allow for something so easy.

Example:
code:
Albuquerque NM 87106
Schaumburg IL 60195
Santa Rosa CA 95402
Taos NM 87571
Franklinville NY 8322
South Lake Tahoe CA 96150
Moses Lake WA 98837
Cotzti CA 95401
Additionally not all the zip codes are in 5-digit format, some of them are the full 9-digit code. My thought is that I need to get the numbers out first, then it would just be a matter of using TRIM and RIGHT (I think?) to extract the state abbreviations. Any ideas?

* # ¿ Sep 30, 2009 19:49
  • Quote
Adbot
ADBOT LOVES YOU

# ¿ Apr 28, 2024 16:14
  • Quote
shotgunfilibuster
Jul 16, 2008
 gently caress yeah, that worked! Thanks! A minor bit of tweaking of my table and some clean-up after running the script and it did just what I wanted.

* # ¿ Oct 1, 2009 19:50
  • Quote
  • 1
  • 2
  • 3
  • 4
  • 5
  • Post
  • Reply
The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread