Trekke ut en stat og et postnummer

April 17  by Eliza

Dan har en kolonne med celler, og hver celle inneholder tre elementer: by, stat og postnr. (Alle tre er i en enkelt celle, mye som du ser i en adresselinje.) Noen av postnumre er fem sifre og noen er ni. Dan trenger å trekke både to-tegns staten og den fem-sifret postnummer i egne celler, til høyre for den aktuelle data. Dan vet han kan bruke Tekst til kolonner verktøy, men mener at det innebærer mye arbeid siden han måtte forholde seg til flere ord bynavn og komma. Dan kan ikke hjelpe, men tror det kan være en formalistisk tilnærming som ville være lettere.

Det må være noen antagelser om dataene for å gjøre noen anbefalinger. La oss anta, for eksempel at alle dataene er i dette formatet:

Min by, CA 98765-4321

Den delen av dashbordet framover (den etterfølgende del av postnummer) er valgfritt, men plasseringen av komma er statisk det følger alltid navnet på byen-og staten består alltid av to tegn. I dette tilfellet er det lett å tenke ut to formler som henter staten forkortelse og de fem første sifrene i postnummer:

= MID (A1, FINN (",", A1) +2,2)
= MID (A1, FINN (",", A1) +5,5)

Begge formlene tasten på komma; det fungerer som et skilletegn mellom byen og de to elementene virkelig ønsker. Hvis det ikke er noen komma i dataene, eller hvis det er flere komma, deretter formlene vil ikke returnere den ønskede informasjonen.

Formlene antar også at det ikke er noen ekstra mellomrom i din data; på de fleste det er en enkelt mellomrom etter komma og mellom stat og postnummer. Dette er, selvfølgelig, lett nok til å håndheve-bare bruke Finn og erstatt for å erstatte to områder med et enkelt mellomrom hvor som helst i regnearket.

Selvfølgelig, hvis dataene er dette strukturert, du kan fortsatt stole på Tekst til kolonner verktøy for å gjøre jobben din. Alt du trenger å gjøre er å kjøre verktøyet og dele dine data basert på komma. Dette vil forlate byen i en celle og sette staten og postnummer sammen i neste celle. Deretter kan du bruke Tekst til kolonner igjen, denne gangen på den andre cellen (ikke navnet på byen) og dele innholdet basert på plass.

Hvis dataene er ikke så strukturert, kanskje den har flere komma i adressen eller ekstra mellomrom-da en helt annen tilnærming kalles for. For å hanskes med dette grunnleggende teknikk innebærer trimming dataene for å fjerne overflødig mellomrom (ledende, bakre og indre), og deretter å bestemme posisjonen til den siste plass og den nest siste plass.

Du kan trekke ut de fem sifre i postnummer, som er definert som umiddelbart etter den siste plassen i dataene, ved hjelp av denne formelen:

= MID (TRIM (A1), FINN (CHAR (1), ERSTATNINGS (TRIM (A1), "",
CHAR (1), LEN (TRIM (A1)) - LEN (STEDFORTREDER (TRIM (A1), "", "")))) + 1,5)

Med to tegn state forkortelse kan returneres ved å trekke ut de to tegnene umiddelbart etter den nest siste plass:

= MID (TRIM (A1), FINN (CHAR (1), ERSTATNINGS (TRIM (A1), "", CHAR (1),
LEN (TRIM (A1)) - LEN (STEDFORTREDER (TRIM (A1), "", "")) - 1)) + 1,2)

Hvis dataene er enda mindre strukturert, kanskje den inneholder adresser som ikke alle har to-tegns statlige forkortelser (NJ stedet for NJ) -så du beste ville være tjent til å bruke en makro til å dele opp dataene. Grunnen til dette er at VBA har en mye rikere sett av tekst håndtering funksjoner enn hva du kan gjøre ved hjelp av Excel formler. Følgende makro oppretter en brukerdefinert funksjon som kan returnere enten staten eller postnummer:

Fungere GetStateZIP (rstrAddress As String, iAction As Integer) As String
Dim arr Som Variant
Dim sState As String
Dim sZIP As String
Dim J As Integer
Dim K As Integer

Application.Volatile
rstrAddress = Trim (rstrAddress)
Hvis Len (rstrAddress) = 0 Then Exit Function

sState = "?"
sZIP = "?"
For J = Len (rstrAddress) Til en Step -1
Hvis Mid (rstrAddress, J, 1) = "" Og sZIP = "?" Deretter
sZIP = Mid (rstrAddress, J + 1, 5)
rstrAddress = Trim (Venstre (rstrAddress, J))
For K = Len (rstrAddress) Til en Step -1
Hvis Mid (rstrAddress, K, 1) = "" Og sState = "?" Deretter
sState = Mid (rstrAddress, K + 1, 20)
rstrAddress = Trim (Venstre (rstrAddress, K))
End If
Neste K
End If
Neste J
Hvis iAction = 1 Da
GetStateZIP = sState
End If
Hvis iAction = 2 Deretter
GetStateZIP = sZIP
End If
End Function

For å bruke denne funksjonen, bare gi en cellereferanse og enten 1 (hvis du vil staten) eller 2 (hvis du vil at postnummer). Her er et eksempel på ber om postnummer uansett adresse er i celle A1:

= GetStateZIP (ã1,2)

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (9598) gjelder for Microsoft Excel 97, 2000, 2002, og 2003. Du kan finne en versjon av dette tipset for Båndet av Excel (Excel 2007 og senere) her: Trekke en stat og et postnummer.