Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

Just-In-Timeberlake: Aug 18, 2003

I'd just like to throw this out to anyone trying to figure out how to do something in Excel since at a job may years ago I wrote tons of Excel macros.

The easiest way to figure out how to code something in Excel is to start the macro recorder, do what you want, stop it and look at the code that gets generated. It will be overly verbose, but just find the part that is needed and adapt to your needs.

It'll save you tons of time searching documentation and the internet.

# ¿ Sep 22, 2009 21:47

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 19:05

Just-In-Timeberlake: Aug 18, 2003

shotgunfilibuster posted:

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?

There's probably a better way to do this, but as long as the zip is in ##### or #####-#### format (no spaces or this won't work), this works, it's quick and dirty, but it works:

code:

Sub ParseAddressFields()
    Dim i&
    Dim j&
    Dim zip$
    Dim state$
    Dim city$
    Dim str$
    
    For i = 1 To 8 'CHANGE THE 8 TO THE LAST ROW OF YOUR DATA
        str = Trim(Range("A" & i).Text)
                
        j = Len(str)
        
        Do While Mid(str, j, 1) <> " "
            zip = Mid(str, j)
            j = j - 1
            
        Loop
        
        str = Trim(Mid(str, 1, j))
        state = Trim(Mid(str, Len(str) - 1, 2))
        city = Trim(Mid(str, 1, Len(str) - 3))
    
        Range("C" & i).Value = city
        Range("D" & i).Value = state
        Range("E" & i).Value = zip
        
    Next i
    
End Sub

Just-In-Timeberlake fucked around with this message at 14:58 on Oct 1, 2009

# ¿ Oct 1, 2009 00:33

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread