Excel, splitsen string!
Tip
Je kan veel dingen doen met behulp van de standaardset aan formules die Excel aan jou beschikbaar stelt. Toch is het soms overzichtelijker om - als je kennis hebt van VBA - een eigen gebruikergedefinieerde Excel-formule te maken. Zo ook in het geval van het opdelen van een string op basis van scheidingstekens in een aantal substrings. We bespreken hier enkele oplossingen.
Situatie: Cel A1 bevat de waarde "01.01.2006"
Deze string willen we opdelen in DAG, MAAND en JAAR.
Oplossing 1. Met behulp van Excel.
Ad 1. Vaste stringindeling, vaste stringlengte, vaste positie en lengte scheidingsteken.
Voor een vaste string is het nog mogelijk om gebruik te maken van de formule DEEL(). Bij een vaste lengte van de datumstring kunnen we gewoon gebruik maken van de volgende formules:
| DAG |
=DEEL(A1;1;2) |
| MAAND |
=DEEL(A1;4;2) |
| JAAR |
=DEEL(A1;7;4) |
Ad 2. Variabele stringindeling, stringlengte, vast aantal scheidingstekens.
We kijken in deze oplossing niet naar de vaste posities van vaste substrings met een vaste lengte, maar gaan uit van de string voor het eerste scheidingsteken ("." in dit geval), de substring tussen de eerste en het tweede scheidingsteken en de substring na het laatste scheidingsteken. Dit betekent dat zowel "01.01.2006", "1.01.2006", "1.1.2006" en "1.1.06" goed wordt afgehandeld. De formules die hierbij horen zijn wel iets ingewikkelder:
| DAG |
=DEEL(A1;1;VIND.ALLES(".";A1;1)-1) |
| MAAND |
=DEEL(RECHTS(A1;LENGTE(A1)-VIND.ALLES(".";A1;1));1; _
VIND.ALLES(".";RECHTS( A1 ; LENGTE(A1)-VIND.ALLES(".";A1;1));1)-1) |
| JAAR |
=RECHTS(RECHTS(A1;LENGTE(A1)- _ VIND.ALLES(".";A1;1));LENGTE(RECHTS(A1;LENGTE(A1)-VIND.ALLES(".";A1;1)))- _ VIND.ALLES(".";RECHTS(A1;LENGTE(A1)-VIND.ALLES(".";A1;1));1)) |
Met deze oplossing zijn we echter nog wel steeds beperkt tot een vast aantal scheidingstekens. Daarbij is de formule wel erg onoverzichtelijk.
Oplossing 2. VBA, gebruikersgedefinieerde functie
Maak in VBA een module aan en plak hierin de volgende VBA-code
Option Explicit
Public Function splitswaarde(TeSplitsenWaarde As String, Scheidingsteken As String, _
ItemIndex As Integer) As String
Dim t() As String
t = Split(TeSplitsenWaarde, Scheidingsteken)
If UBound(t) < (ItemIndex-1) Then
splitswaarde = ""
Else
splitswaarde = t(ItemIndex-1)
End If
End Function
Deze gebruikersgedefinieerde formule kun je nu gebruiken om de waarden uit de string te splitsen. Je bent daarbij niet eens gebonden aan het aantal scheidingstekens in de string. In deze situtatie zien de formules er als volgt uit:
| DAG |
=splitswaarde(A1;".";1) |
| MAAND |
=splitswaarde(A1;".";2) |
| JAAR |
=splitswaarde(A1;".";3) |
Kijk er eens naar en probeer het uit . . . Deel je ervaringen met andere Excel-gebruikers via deze site.
Laat eens iets van je horen op ons gastenboek of forum..
|