Het verplaatsen van de Huisnummer zijn eigen Cell

Tom werkt met veel lijsten met adressen. Een ding dat hem gek drijft is wanneer het adres is in een enkele cel. Tom moet het huisnummer in een cel en de straat in een andere cel. Dus, als het adres "1234 Maple Glen Ave.," dan moet hij "1234" in een cel en "Maple Glen Ave." in andere. Hij kan het huisnummer in een kolom geplaatst vóór de straatnaam te kopiëren, maar het is een lang proces om de nummers opnieuw te typen en vervolgens te elimineren uit de straatnaam. Tom vraagt ​​zich af of er een eenvoudigere manier dat zou "splitsen" het huisnummer van de straatnaam.

Er zijn een paar manieren waarop u kunt gaan over deze taak. Sommigen zullen misschien denken dat het gebruik van de Tekst naar kolommen gereedschap (op het tabblad Gegevens van het lint) zou het gewenste resultaat geven. Helaas, het is niet de beste manier om te gaan over het scheiden van het huisnummer. De reden is simpel: Als u het gereedschap gebruikt door het opgeven van een begrenzer, de enige die elke zin is de ruimte. Maar als je dat doet, zal je eindigen met "1234 Maple Glen Ave." wordt verspreid over vier kolommen in plaats van de gewenste twee. Je zou dan moeten komen met een manier om de naam van de straat weer in elkaar te plakken.

De andere manier om mogelijk gebruik maken van de Tekst naar kolommen tool is met een vaste kolombreedte (in plaats van een begrenzer). Deze benadering werkt alleen als alle adressen hetzelfde aantal cijfers in het huisnummer. In de meeste datasets uiteraard zal dit niet het geval huisnummer kan vrijwel elk aantal cijfers, en ze kunnen zelfs omvatten niet-numerieke tekens (zoals "1234A", "1234-B", of "1234-1236").

Een benadering die zal werken is om formules te gebruiken om uit elkaar trekken van de oorspronkelijke adressen. Je formules kunnen intoetsen op de eerste plaats in het adres, terugkerende hetzij het gedeelte links van de ruimte of het gedeelte rechts. Hier is de weg te trekken uit alles wat voor de ruimte, gegeven een adres in cel A1:

= LINKS (A1, FIND ("", A1) -1)

Als u absoluut zeker dat de adressen niet alle niet-numerieke tekens bevat, kunt u de formule in de functie WAARDE zodat je eindigt met het huisnummer als een numerieke waarde wrap:

= WAARDE (LINKS (A1, FIND ("", A1) -1))

Om het deel van het adres na de eerste ruimte die u kunt deze formule gebruiken grijpen:

= MID (A1, FIND ("", A1) + 1, LEN (A1))

Je kan, als je dat liever, bedenken een formule die het huisnummer u uitgetrokken met de eerste formule gevonden. Stel bijvoorbeeld dat uw huis-nummer formule is in kolom B; je zou het volgende in kolom C:

= TRIM (plaatsvervanger (A1, B1,))

Met formules in de kolommen B en C (de adressen in kolom A), kan de formule voor hoeveel rijen nodig kopieer. C bereik en gebruik Plakken speciaal om de waarden weer in die cellen plakken: dan kan je dat B selecteren. Na dit te doen kunt u veilig de originele adressen in kolom A. verwijderen

Als u vaak te veel adressen te doen, zou je beter gediend om een ​​macro gebruiken om te doen behalve het trekken. De volgende voorbeeld macro werken op wat bereik dat u hebt gekozen toen u het uit te voeren. Het voegt een kolom lege cellen links van de selectie, stopt die cellen met het huisnummer, en past dan de adressen zodat het huisnummer niet meer opgenomen.

Sub SplitAddress ()
Dim c als Waaier
Dim j As Integer
Dim n As String
Dim addr als Koord

Selection.Insert Shift: = xlToRight
Selection.Offset (0, 1) .Selecteer

Voor Elke c In Selection
j = InStr (1 c "")
n = Links (c, j)
c.Offset (0, -1) = n
addr = Trim (Rechts (c, Len (c) - j))
c = addr
Volgende
End Sub

Als u wilt, kunt u een door de gebruiker gedefinieerde functie die alleen het huisnummer terug te creëren:

Functie GrabHouseNumber (Raw als Koord) als tekst
Dim x als Variant
Dim House als Variant

x = Split (Raw, "") 'gebruik ruimte char om elementen in serie te splitsen
Huis = x (0) 'eerste element van de array-
Als Left (Huis, 1) Vind je "#" Dan
'Eerste char is numerieke cijfers
GrabHouseNumber = House 'set return waarde als huisnummer
Anders
GrabHouseNumber = "" 'Eerste char is de tekst, dus niet een huisnummer
End If
End Function

Een voordeel van het gebruik door de gebruiker gedefinieerde functie is dat het controleert of het eerste deel van het oorspronkelijke adres daadwerkelijk begint met een cijfer of niet. Als dit niet gebeurt, dan wordt ervan uitgegaan dat het adres niet begint met een huisnummer. (Het gehele huisnummer hoeft niet numeriek te zijn, het moet enkel met een cijfer.)

ExcelTips is uw bron voor kosteneffectieve Microsoft Excel training. Deze tip (13.350) is van toepassing op Microsoft Excel 2007, 2010 en 2013.