abonnement Unibet Coolblue Bitvavo
pi_197631064
quote:
0s.gif Op maandag 25 januari 2021 13:47 schreef Twentsche_Ros het volgende:

[..]

In Nederland kennen we straatnamen die "Plein 1945" heten, dan heb je al een probleem. Maar volgens mij moet het kunnen: Een soort "vind de derde spatie" Of vind de 20e "a". Het gaat me nu ook een beetje om het principe.
Dan zoiets:
=VIND.ALLES(TEKEN(1);SUBSTITUEREN(B5;" ";TEKEN(1);3))

Die 3 geeft aan dat het de derde spatie moet zijn :)

Geen garanties, ik google het ook maar bij elkaar :P
pi_197631168
quote:
0s.gif Op maandag 25 januari 2021 13:52 schreef Piles het volgende:

[..]

Dan zoiets:
=VIND.ALLES(TEKEN(1);SUBSTITUEREN(B5;" ";TEKEN(1);3))

Die 3 geeft aan dat het de derde spatie moet zijn :)

Geen garanties, ik google het ook maar bij elkaar :P

Ik doe het iets anders, maar dankzij jou ben ik er uit.
Zie dit commando:
=SUBSTITUTE(G15;" ";"#";L15)

Er van uitgaande dat geen enkele straatnaam en geen enkel huisnummer een "#" (hekje) in zich herbergt.

L15 staat voor het aantal spaties dat ik in de cel heb gevonden.
G15 staat voor de tekst van de oorspronkelijke cel inclusief alle spaties.

En rechts naast heb substitute commando ga ik via "find.spec" op zoek naar de plek waar "#" staat.
Dank je wel!
Je kunt beter één kaars opsteken dan duizend maal de duisternis vervloeken.
pi_197631179
Je kan ook nog met als() controleren of het laatste stukje een getal is. Als dat niet zo is moet je dus de twee na laatste spatie laten staan en de rest verwijderen.
pi_197631192
quote:
1s.gif Op maandag 25 januari 2021 13:52 schreef Tegan het volgende:
Misschien is dit een idee. Met de 1 in substitueren(a2;" ";"";1) kun je meegeven dat alleen de eerste spatie vervangen wordt, met substitueren(a2;" ";"";2) alleen de tweede, etc. Als je weet dat er drie spaties zijn kun je 1 en 2 vervangen (met een als() kun je het alle spaties behalve de laatste vervangen) om vervolgens een tekst over te houden met maar één spatie op de juiste plek. En dan kun je de rest gebruiken wat je al had.
Ik had jouw bijdrage over het hoofd gezien. Maar het komt volgens mij op hetzelfde neer.
Dank je.
Je kunt beter één kaars opsteken dan duizend maal de duisternis vervloeken.
pi_197631217
quote:
1s.gif Op maandag 25 januari 2021 14:00 schreef Tegan het volgende:
Je kan ook nog met als() controleren of het laatste stukje een getal is. Als dat niet zo is moet je dus de twee na laatste spatie laten staan en de rest verwijderen.
Ja, maar je hebt ook 22a en 23b als huisnummer.
Ik ben al geholpen met de bijdrages van jou en Piles. De uitzonderingen (mochten die er zijn) filter ik er wel uit.
Je kunt beter één kaars opsteken dan duizend maal de duisternis vervloeken.
pi_197631706
Hoe pas je een ctrl end aan.
Als je bijvoorbeeld een grote tabel hebt en je haalt een deel van de regels weg. Dan blijft ctrl end naar de laatste regel van de originele tabel gaan. Terwijl je dan minder regels hebt en wil ik dat ctrl end naar de laatste regel van de nieuwe tabel gaat.
pi_197632121
Rijen verwijderen en bestand opslaan?
Aldus.
pi_197632460
quote:
0s.gif Op maandag 25 januari 2021 14:35 schreef Stansfield het volgende:
Hoe pas je een ctrl end aan.
Als je bijvoorbeeld een grote tabel hebt en je haalt een deel van de regels weg. Dan blijft ctrl end naar de laatste regel van de originele tabel gaan. Terwijl je dan minder regels hebt en wil ik dat ctrl end naar de laatste regel van de nieuwe tabel gaat.
Ctrl S tussen door (opslaan). Dan is het weer gerefreshed.
Typisch: als je insert doet dan past ie het wel aan bij Ctrl-End.
Ik doe trouwens altijd End-Down
Je kunt beter één kaars opsteken dan duizend maal de duisternis vervloeken.
pi_197632570
Ik heb dit trouwens als goed voornemen voor 2021. Maar moet nog wel een beetje de balans vinden van wanneer wel Excel en wanneer overstappen naar Python.
Aldus.
pi_197644752
Dit is natuurlijk een Excel-topic, maar het probleem van @Twentsche_Ros laat zich volgens mij goed oplossen met OpenRefine (GREL met regex).
"Met charme kun je je een kwartiertje redden, daarna moet je iets te zeggen hebben."
pi_197646157
quote:
0s.gif Op dinsdag 26 januari 2021 06:22 schreef baskick het volgende:
Dit is natuurlijk een Excel-topic, maar het probleem van @:Twentsche_Ros laat zich volgens mij goed oplossen met OpenRefine (GREL met regex).
Zo, leg uit! Wat is "OpenRefine (GREL met regex)"? Waar kan ik dat vinden?
Je kunt beter één kaars opsteken dan duizend maal de duisternis vervloeken.
pi_197646463
quote:
0s.gif Op dinsdag 26 januari 2021 09:36 schreef Twentsche_Ros het volgende:

[..]

Zo, leg uit! Wat is "OpenRefine (GREL met regex)"? Waar kan ik dat vinden?
OpenRefine (voorheen Google Refine) is een stand-alone applicatie die je lokaal in de browser laadt. Je kunt er data mee opschonen en transformeren.

GREL staat voor Google Refine Expression Language en is de taal waarmee je commando's kunt invoeren. Voor veel standaard-acties is een visuele mogelijkheid, maar je kunt GREL-commando's (net als formules in Excel) nesten en ook met het handje invoeren. Regex zijn regular expressions, waarmee je patronen kunt herkennen - even simpel gezegd. Je kunt zo kolommen splitten op numerieke waardes, op de laatste numerieke waarde, enz.

Ik gebruik het vaak om data op te schonen voordat ik het in Excel verder bewerk of in een database importeer.

Zie https://openrefine.org.

[ Bericht 2% gewijzigd door baskick op 26-01-2021 10:06:18 ]
"Met charme kun je je een kwartiertje redden, daarna moet je iets te zeggen hebben."
pi_197646802
quote:
0s.gif Op dinsdag 26 januari 2021 10:00 schreef baskick het volgende:

[..]

OpenRefine (voorheen Google Refine) is een stand-alone applicatie die je lokaal in de browser laadt. Je kunt er data mee opschonen en transformeren.

GREL staat voor Google Refine Expression Language en is de taal waarmee je commando's kunt invoeren. Voor veel standaard-acties is een visuele mogelijkheid, maar je kunt GREL-commando's (net als formules in Excel) nesten en ook met het handje invoeren. Regex zijn regular expressions, waarmee je patronen kunt herkennen - even simpel gezegd. Je kunt zo kolommen splitten op numerieke waardes, op de laatste numerieke waarde, enz.

Ik gebruik het vaak om data op te schonen voordat ik het in Excel verder bewerk of in een database importeer.

Zie https://openrefine.org.
Top!
Al deze informatie.
Het splitten van de "ruwe" adres info in tekstueel en numeriek ging in dit geval niet op.
Zoals ik al zei gooit "Plein 1945" al roet in het eten. Verder heb je bij huisnummers legio voorbeelden van 22a en 3-III etc. Maar ik heb dus een prima oplossing gevonden voor "traceer de laatste spatie" en dat was de bedoeling.
In 2 stappen:
Vervang alle spaties door "blanks", maw "delete alle spaties" en vergelijk de lengte van de nieuwe cel met de oorspronkelijke. Hierdoor heb je het aantal spaties.
En daarna:
Vervang de laatste spatie (die weet je nu "in rang" omdat je het aantal spaties weet) in een teken (bv hekje, #) waarvan je zeker weet dat ie never nooit in een adres voorkomt.
Want het commando "substitute" kende dus een "rang-optie" in de geest van "vervang de zoveelste spaties/e/af/qq..whatever in het opgegeven teken".
En daarna de plek van het nieuwe teken traceren via FIND en voila.... via MID (of DEEL in het Nederlands) kun je de tekst op de juiste plek "splitten".

[ Bericht 1% gewijzigd door Twentsche_Ros op 26-01-2021 11:04:16 ]
Je kunt beter één kaars opsteken dan duizend maal de duisternis vervloeken.
pi_197647006
Dat is dus zo krachtig aan regex, je kunt uit een string halen wat je wilt. Bijvoorbeeld de laatste numerieke waarde in een string: (\d+)(?!.*\d)

Die haalt uit "Plein 1945 3a" en "5 Meiweg 3b" dus in beide gevallen 'gewoon' 3. En uit "Straat 2 rd" (een toevoeging zoals in Haarlem voorkomt) dus ook 3.

Die regex zou je dus kunnen tweaken als je in de kolom huisnummer ook de toevoeging wilt hebben. En je zou 'm kunnen tweaken dat 'ie het getal en de toevoeging (letter/kleur/etage/whatever) in aparte kolommen zet.
"Met charme kun je je een kwartiertje redden, daarna moet je iets te zeggen hebben."
pi_197647304
quote:
0s.gif Op dinsdag 26 januari 2021 10:36 schreef baskick het volgende:
Dat is dus zo krachtig aan regex, je kunt uit een string halen wat je wilt. Bijvoorbeeld de laatste numerieke waarde in een string: (\d+)(?!.*\d)

Die haalt uit "Plein 1945 3a" en "5 Meiweg 3b" dus in beide gevallen 'gewoon' 3. En uit "Straat 2 rd" (een toevoeging zoals in Haarlem voorkomt) dus ook 3.

Die regex zou je dus kunnen tweaken als je in de kolom huisnummer ook de toevoeging wilt hebben. En je zou 'm kunnen tweaken dat 'ie het getal en de toevoeging (letter/kleur/etage/whatever) in aparte kolommen zet.
Ha ha.
Je praat echt als een enthousiaste IT-er. Programmeur? Ik ben helemaal vergroeid met Excel en bovenstaand is voor mij abracadabra. Ik zou al op moeten zoeken wat "tweaken" is. Voorlopig ben ik met de oplossing die ik dankzij FOK heb gevonden tevreden. Maar ik zal bovenstaande tips zeker "in mind" houden. ;)
Je kunt beter één kaars opsteken dan duizend maal de duisternis vervloeken.
pi_197647486
Regular expressions kan je ook in VBA gebruiken. Ik raak er altijd helemaal van in de war. Het is wel echt een kunst.
Aldus.
pi_197647499
Hah, hobbymatig, ja. Misschien heb jij of een andere meelezer er in de toekomst wat aan :)

quote:
2s.gif Op dinsdag 26 januari 2021 11:13 schreef Z het volgende:
Regular expressions kan je ook in VBA gebruiken. Ik raak er altijd helemaal van in de war. Het is wel echt een kunst.
Klopt. En in PHP, Java, JavaScript, C#, enz :-) Het is zeker complex, helemaal als je ingewikkelde patronen gaat zoeken.
"Met charme kun je je een kwartiertje redden, daarna moet je iets te zeggen hebben."
pi_197647805
quote:
2s.gif Op dinsdag 26 januari 2021 11:13 schreef Z het volgende:
Regular expressions kan je ook in VBA gebruiken. Ik raak er altijd helemaal van in de war. Het is wel echt een kunst.
VBA gebruik ik soms ook. Maar je moet wel goed weten wat je doet. In mijn professie in finance is een gedegen kennis van alle standaard commando's (oa sumif, pivottables) al heel wat om een gedegen informatieverstrekking te krijgen. Op eigen houtje gaan programmeren in VBA is niet raadzaam.
De ERP programma's moeten deugdzaam zijn en dat is het metier van de programmeur.
Je kunt beter één kaars opsteken dan duizend maal de duisternis vervloeken.
pi_197647833

:P .
pi_197647979
quote:
0s.gif Op dinsdag 26 januari 2021 11:34 schreef Twentsche_Ros het volgende:

[..]

VBA gebruik ik soms ook. Maar je moet wel goed weten wat je doet. In mijn professie in finance is een gedegen kennis van alle standaard commando's (oa sumif, pivottables) al heel wat om een gedegen informatieverstrekking te krijgen. Op eigen houtje gaan programmeren in VBA is niet raadzaam.
De ERP programma's moeten deugdzaam zijn en dat is het metier van de programmeur.
VBA is ook absoluut geen deugdzame taal dus ik raad het je dan ook niet aan.
Aldus.
pi_197650802
quote:
0s.gif Op maandag 25 januari 2021 15:19 schreef Twentsche_Ros het volgende:

[..]

Ctrl S tussen door (opslaan). Dan is het weer gerefreshed.
Typisch: als je insert doet dan past ie het wel aan bij Ctrl-End.
Ik doe trouwens altijd End-Down
End-Down werkt ook top. En de rest ook.
Bedankt voor de info!
pi_197668936
Ik heb "Sheet 1":
1
2
3
4
5
Kolom A    | Kolom B
-----------|-----------
Duitsland  | Mercedes
Duitsland  | Volkswagen
Japan      | Toyota

Ik heb "Sheet 2":
1
2
3
4
5
6
7
8
Kolom A    | Kolom B
-----------|-----------
Mercedes   | ..
Volkswagen | ..
Toyota     | ..
Volkswagen | ..
Volkswagen | ..
Toyota     | ..

Ik wil kolom B in "Sheet 2" automatisch laten vullen waar nu ".." staat.

Ik dacht dat kan op deze manier:
1=VERT.ZOEKEN(Sheet 2!A2;Sheet 1!A:B;Sheet 1!1;0)

Maar dat werkt niet. De formule klopt niet.
pi_197669486
Verticaal zoeken kan niet op deze manier.
Hij zoekt in de meest linkerkolom van een matrix (dus A in jouw voorbeeld) en geeft de xe kolom van de matrix terug. Je kan niet naar links tellen, alleen naar rechts. Je kan dus niet verwijzen naar een kolom, alleen een getal invullen als 3e parameter...

Zet in sheet 1 je merken in kolom A en de landen in kolom B.
Je formule wordt dan:
1    =VERT.ZOEKEN(Sheet 2!A2;Sheet 1!A:B;2;0)

Als je de indeling niet kan/wil veranderen, is er ook een andere oplossing.
pi_197669589
@xaban06 de langzamere/complexere optie is:
1=INDEX(Sheet 1!A:A;VERGELIJKEN(Sheet 1!A2;Sheet 1!B:B;0);1)
Waarbij A:A de op te halen waarde is en B:B de te zoeken waarde. Dan hoef je de indeling niet te veranderen.

Eventueel nog te beperken tot A1:A99 en B1:B99 (afhankelijk van de lengte van je matrix)
pi_197681919
quote:
0s.gif Op dinsdag 26 januari 2021 11:00 schreef Twentsche_Ros het volgende:

[..]

Ha ha.
Je praat echt als een enthousiaste IT-er. Programmeur? Ik ben helemaal vergroeid met Excel en bovenstaand is voor mij abracadabra. Ik zou al op moeten zoeken wat "tweaken" is. Voorlopig ben ik met de oplossing die ik dankzij FOK heb gevonden tevreden. Maar ik zal bovenstaande tips zeker "in mind" houden. ;)
Je kan ook Power Query gebruiken. Zit ingebouwd in Excel en met een paar klikken kun je de mooiste dingen doen zonder dat je ingewikkelde formules hoeft te bedenken.

abonnement Unibet Coolblue Bitvavo
Forum Opties
Forumhop:
Hop naar:
(afkorting, bv 'KLB')