Re: het splitsen van adres, postcode, plaats in meerdere cellen

[Niek Otten]

Staan er echt alleen spaties tussen? Omdat er ook veel spaties in de rubrieken zelf staan (Prof J.H. Bavicklaan heeft er twee) zal
dit niet automatisch gaan. Als er andere tekens staan, bijvoorbeeld komma's, dan kun je Data>Tekst naar kolommen gebruiken
Als er maar een enkele is met meer dan een spatie, gebruik dan ook Tekst naar kolommen (met een spaties als scheidingsteken) en
coorigeer de enkele handmatig

Je zou nog VBA code kunnen schrijven die een cijfer (huisnummer) ziet als afsluiting van de straatnaam, maar zelfs dat is tricky.
--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"manfredvanroekel" <manfredv...@discussions.microsoft.com> wrote in message
news:871C68E3-9107-4998...@microsoft.com...
| In 1 cel staant de straatnaam, huisnummer, postcode en woonplaats vermeld.
| Hoe krijg ik dit in aparte cellen. Een voorbeeld is:
| Prof. J.H. Bavincklaan 5 1183 AT Amstelveen
| Dit moet in verschillende cellen terecht komen.

[Wim Hendriksen]

Hallo Manfred,

Ingrid Baplue heeft eens een tweetal formules gemaakt waarmee je een
straatnaam met huisnummer splitst.
Die formules zou je kunnen gebruiken, hoewel het verhaal van Niek wel
blijft opgaan.
De formules werken volgens het principe dat de splitsing plaatsvindt bij
de laatste spatie in het veld.
Je zou dan, werkend met een paar hulpkolommen misschien het volgende
kunnen doen:
de eerste formule geeft je het veld minus de plaatsnaam, de tweede
formule de plaatsnaam.
vervolgens splits je het veld zodanig dat de laatste 7 posities eraf
gehaald worden (= postcode) om dan vervolgens nogmaals beide formules te
gebruiken om straatnaam en huisnummer te splitsen.

Nu dan beide formules:
de eerste:
=LINKS($A1;MAX(ALS(DEEL($A1;RIJ($1:$100);1)=" ";RIJ($1:$100)))-1)

de tweede:
=RECHTS($A1;LENGTE($A1)-MAX(ALS(DEEL($A1;RIJ($1:$100);1)=" ";RIJ($1:$100))))

Wel beide formules ingeven als matrixformules (dus met shift-ctrl-enter).

Ik hoop dat je hier mee een stukje op weg geholpen bent.

Groeten
Wim Hendriksen

Niek Otten schreef:

[Niek Otten]

<De formules werken volgens het principe dat de splitsing plaatsvindt bij de laatste spatie in het veld.>

Dat is wel slim. De meeste spaties zullen waarscijnlijk in de straatnaam zitten.

Hoewel, Alphen aan den Rijn, Krimpen, Capelle, .....

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Wim Hendriksen" <hendri...@hotmail.com> wrote in message news:3c576$45dc2d1b$3ec2d909$61...@news.chello.nl...

[Hans]

"manfredvanroekel" <manfredv...@discussions.microsoft.com> schreef in
bericht news:871C68E3-9107-4998...@microsoft.com...

> In 1 cel staant de straatnaam, huisnummer, postcode en woonplaats vermeld.
> Hoe krijg ik dit in aparte cellen. Een voorbeeld is:
> Prof. J.H. Bavincklaan 5 1183 AT Amstelveen
> Dit moet in verschillende cellen terecht komen.

Ik zou het denk ik in vba oplossen.
Daarbij zou ik gebruik maken van de postcode. Dus in de complete string zou
ik gaan zoeken naar de volgorde

spatie
numeric
numeric
numeric
numeric
spatie
alfabetic
alfabetic
spatie

Dat patroon lijkt me zo uniek dat je met grote zekerheid kunt zeggen dat dat
de postcode moet zijn.
Wanneer dat patroon wordt gevonden weet je wat de startpositie en
eindpositie van de postcode is.
Daarmee kan je plaats, postcode en straat huisnummer van elkaar scheiden.

Hans

[manfredvanroekel]

Dank je voor je inbreng, helaas lukt het niet met tekst naar kolommen omdat
dit aantal nogal varieert.

Wat ik wel net bedacht is om alle spaties net voor een cijfer te vervangen
door een <Spatie><komma>, dan ben je even 9 maal aan het vervangen, maar het
kan wel werken(denk ik)

Manfred

"Niek Otten" schreef:

[manfredvanroekel]

Hoi Hans,

Bedankt voor je inbreng, ik weet niet of het werkt omdat mijn kennis van VBA
ongeveer nihil is.

Maar ik waardeer je antwoord wel.

Wellicht toch een handig om me in te gaan verdiepen.

"Hans" schreef:

[edjez]

Kweetniet hoeveel tijd je hebt, maar het volgende werkt voor schat ik 95% van
de adressen. Verdient niet de schoonheidsprijs, maar werkt zo te zien wèl:

Sub AdresOntleden()
Dim Totaal As String
Dim straat As String
Dim straat_nummer As String
Dim nummer As String
Dim pcode As String
Dim plaats As String
Dim teller As Integer
Dim Positie As Integer
Totaal = Range("A1")

'Zoek laatste cijfer in de string
For teller = Len(Totaal) To 1 Step -1
Select Case Mid$(Totaal, teller, 1)
Case "1" To "9"
Positie = teller: Exit For
End Select
Next
teller = 0

'1e deel postcode
Postcode = Mid$(Totaal, Positie - 3, 4)

'De eerste string tot die postcode = straatnaam + huisnummer
straat_nummer = RTrim(Left$(Totaal, Positie - 4))

'Zoek de tweede letter daarna en vul tevens de postcode aan
Do While teller < 2
Positie = Positie + 1
Select Case Mid$(Totaal, Positie, 1)
Case "a" To "z", "A" To "Z"
Postcode = Postcode + Mid$(Totaal, Positie, 1)
teller = teller + 1
End Select
Loop

'Alles rechts van die positie is plaatsnaam
plaats = LTrim(Right$(Totaal, Len(Totaal) - Positie))

'Zoek de eerste spatie na straatnaam, beginnend vanaf rechts.
For teller = Len(straat_nummer) To 1 Step -1
Select Case Mid$(straat_nummer, teller, 1)
Case " "
Positie = teller: Exit For
End Select
Next teller

'Alles links daarvan is straatnaam, de rest is nummer + toevoeging
straat = Left$(straat_nummer, Positie)
nummer = Right$(straat_nummer, Len(straat_nummer) - Len(straat))

Range("B1") = plaats
Range("C1") = Postcode
Range("D1") = straat
Range("E1") = nummer

End Sub

Gr. Ed

[manfredvanroekel]

Dabnk je Wim,

Hiermee lukt het inderdaad.

Manfred

"Wim Hendriksen" schreef:

[manfredvanroekel]

Dank je voor je inbreng, helaas is mijn kennis van VB(A) nihil en moet ik het
op een andere manier aanpakken, ik zie wel dat je met VBA een boel meer kan
bereiken.

"edjez" schreef:

[eddydendaddy]

Manfred,
Eigenlijk is jouw probleem vrij simpel op te lossen:
Sla je document op als txt (tab is scheidingsteken)
Open vervolgens een nieuw exeldocument en haal het tekstbestand op.
Nu krijg je de mogelijkheid om de scheidingstekens die je wenst te
selecteren. Selecteer ze allemaal en druk ok. Alles staat nu netjes in
verschillende cellen naast elkaar.
Vg
Eddy

"manfredvanroekel" schreef:

[Niek Otten]

Hi Eddy,

Aan welk scheidingsteken dacht je? Er staat een onbekend aantal spaties in 1 rubriek, dat is al verscheidene keren genoemd; zie
ook het voorbeeld.
De omweg via een txt file is niet nodig; dat doet Data>Tekst naar kolommen direct.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"eddydendaddy" <eddyde...@discussions.microsoft.com> wrote in message
news:F45108C4-9073-42A1...@microsoft.com...

| Manfred,
| Eigenlijk is jouw probleem vrij simpel op te lossen:
| Sla je document op als txt (tab is scheidingsteken)
| Open vervolgens een nieuw exeldocument en haal het tekstbestand op.
| Nu krijg je de mogelijkheid om de scheidingstekens die je wenst te
| selecteren. Selecteer ze allemaal en druk ok. Alles staat nu netjes in
| verschillende cellen naast elkaar.
| Vg
| Eddy
|
|
|
|
| "manfredvanroekel" schreef:
|
| > Dank je voor je inbreng, helaas is mijn kennis van VB(A) nihil en moet ik het
| > op een andere manier aanpakken, ik zie wel dat je met VBA een boel meer kan
| > bereiken.
| >
| > "edjez" schreef:
| >
| > > Kweetniet hoeveel tijd je hebt, maar het volgende werkt voor schat ik 95% van

| > > de adressen. Verdient niet de schoonheidsprijs, maar werkt zo te zien wčl:

[Joyce]

Ik heb een dergelijk probleem, maar dan alleen bij straatnaam. Ik wil de
huisnummers van de straten splitsen.

Hoe splits ik in vba op het nummerieke van alphanummeriek? zoals jij
hieronder aangeeft?? (ik ken vba ook niet goed...)

groetjes joyce

"Niek Otten" schreef:

[jan]

Joyce,

Je zou onderstaande formules kunnen gebruiken:

voor de straatnaam:
=LINKS(A1;1000-(MAX(ISGETAL(1*DEEL(A1;1000-RIJ($A$1:$A$1000);1))*RIJ($A$1:$A$1000)))-1)

voor het nummer:
=DEEL(A1;1000-(MAX(ISGETAL(1*DEEL(A1;1000-RIJ($A$1:$A$1000);1))*RIJ($A$1:$A$1000)));1000)

als de straatnaam met huisnummer in A1 staat en de lengte daarvan niet
groter is dan 1000 tekens.

De (matrix)formules moeten beide, na invoer, worden afgesloten met
Ctrl+Shift+Enter.

Er wordt gesplitst bij het eerste gevonden numerieke teken.
Dat betekent dat straatnamen waar numerieke tekens in voorkomen verkeerd
gesplitst zullen worden.

Jan

[Joyce]

Bedankt Jan,

Het is gelukt.

Ik heb nu een kolom met straatnaam en een andere kolom met het huisnummer.
Echter wilde ik de huisnummers met de toevoeging splitsen nu dacht ik als ik
jouw formule pak en ik verander isgetal in istekst, maar dat werkt niet.
ik heb beide geprobeerd.

Vb. huisnummer met toevoeging = 39a

=LINKS(A1;1000-(MAX(ISTEKST(1*DEEL(A1;1000-RIJ($A$1:$A$1000);1))*RIJ($A$1:$A$1000)))-1)
en
=DEEL(A1;1000-(MAX(ISTEKST(1*DEEL(A1;1000-RIJ($A$1:$A$1000);1))*RIJ($A$1:$A$1000)));1000)

Zou je me hiermee kunnen helpen? en waarom lukt het niet?
(ik heb de kolom huisnummer omgezet van formule naar waarde, je krijgt dan
zo'n geel uitroepteken en kun je kiezen cel omzetten naar tekst of
converteren naar getal, heeft dat er mee te maken??)

groetjes Joyce

"jan" schreef:

> .
>

[jan]

Joyce,

Wat lastiger, maar dat zou zo kunnen:

voor het nummer:
=--LINKS(A1;MIN(ALS((20-(NIET(ISGETAL(--(DEEL(A1;20-RIJ($A$1:$A$20);1))))*RIJ($A$1:$A$20)))=0;1000;20-(NIET(ISGETAL(--(DEEL(A1;20-RIJ($A$1:$A$20);1))))*RIJ($A$1:$A$20))))-1)

voor de toevoeging:
=DEEL(A1;MIN(ALS((20-(NIET(ISGETAL(--(DEEL(A1;20-RIJ($A$1:$A$20);1))))*RIJ($A$1:$A$20)))=0;1000;20-(NIET(ISGETAL(--(DEEL(A1;20-RIJ($A$1:$A$20);1))))*RIJ($A$1:$A$20))));100)

en beide weer ingevoerd als matrixformules.

Jan