Celinhoud splitsen over meerdere cellen (vba)

Status
Niet open voor verdere reacties.
R

Robert Smidt

Gebruiker
Lid geworden
26 mei 2009
Berichten
901
Beste Forumleden,

Ik ben bezig met het maken van een excelbestand waar ik persoonsgegevens, afkomstig vanuit een Pdf-bestand, handmatig naar toe kopieer.

Nu blijkt dat de naam en voornamen altijd naast elkaar staan én dat geldt ook voor het adres, postcode en plaats. Het zou mij helpen door de naam en voornamen in één keer te kopiëren en te plakken in de cel "naam" en idem voor de adresgegevens, door deze volledig naar het adres te kopiëren en dat het systeem deze automatisch splitst en op de juiste plek zet.

Kopiëren van de naam en het adres:
De naam wordt altijd gevolgd door een komma en de voornamen gesplitst door een spatie bijv. (van Tiel, Dirk Jan)
Het adres wordt ook altijd gevolgd door een komma, vervolgens een postcode, die altijd uit 6 variabelen bestaat, en tot slot de plaats bijv. (Wittedijk 22 I, 8882PH Harderwijk).

Tot slot zou het fijn zijn dat de postcode wordt voorzien van een spatie tussen de cijfers en de letters: dus 1234AB --> 1234 AB

Ik doe als bijlage een voorbeeld en hoop dat iemand mij kan helpen.

Alvast heel hartelijk dank.

Robert
 

Bijlagen

  • Adres.xlsm
    19,6 KB · Weergaven: 29
Laatst bewerkt:
Dit zal je wel een idee geven hoe er mee om te gaan:
Code: 
Function NaamAdres(Bron As Range, Deel As Integer) As String
    [COLOR="#008000"]'Bron is de cel waaruit het opgegeven deel moet worden opgehaald[/COLOR]
    Select Case Deel
        Case 1
            [COLOR="#008000"]'Voornamen[/COLOR]
            NaamAdres = Split(Bron, ", ")(1)
        Case 2
            [COLOR="#008000"]'Postcode[/COLOR]
            postcode = Split(Split(Bron, ", ")(1))(0)
            NaamAdres = Left(postcode, 4) & " " & Right(postcode, 2)
        Case 3
            [COLOR="#008000"]'Woonplaats[/COLOR]
            NaamAdres = Split(Split(Bron, ", ")(1))(1)
        Case 4
            [COLOR="#008000"]'Voorletters[/COLOR]
            If Bron.Value <> "" Then
                vrl = Split(Bron)
                For i = 0 To UBound(vrl)
                    NaamAdres = NaamAdres & Left(vrl(i), 1) & "."
                Next i
            End If
    End Select
End Function

Je document:
Bekijk bijlage Kopie van Adres.xlsm
 
Laatst bewerkt:
Dank je voor jouw snelle antwoord.

Ik heb de rode tekst verwijderd en zelf geprobeerd, echter gebeurt er niets. Deze manier van programmeren is mij onbekend. Wanneer wordt de code nu geactiveerd en kan het bezoekadres ook aangepast worden, nu staat er nog steeds de postcode en de plaats in het veld van het adres.
 
Het voorbeeld is een functie.
Die functie is in de betreffende cellen ingevoerd.
Nu je die rode cellen hebt verwijderd staat die functie er dus niet meer in.

Het hoeft uiteraard geen functie te zijn.
Ik wilde je alleen laten zie hoe je de bestaande gegevens kan splitsen.
 
Dat was idd niet zo slim van mij :eek:. Is er ook een mogelijkheid dat de cellen niet gevuld zijn (i.v.m. per ongelijk wissen) en dat het volledig via vba wordt uitgevoerd?
 
Het is volledig VBA.
Cellen kan je uiteraard beschermen.

Of je gebruikt die code op een manier zoals je zelf wenst.
 
Hoe kan ik de naam in cel I3 wijzigen? Hier staat nog steeds de voornaam bij en idem voor het adres F8 en F13, hier staat nog steeds de postcode en de plaats, of kan dat niet omdat dan de codes niet meer werken?
 
In het document dat ik plaatste staat in de rode cellen alles goed.
Dus ik weet niet wat je nu precies bedoelt.
De bron cellen worden niet gewijzigd.
 
In het document wil ik mensen met hun volledig personalia opvoeren. Onder "naam" staat nu: van Tiel, Dirk Jan. Als ik hem een brief stuur dan staat er in de aanhef: aan de heer van Tiel, Dirk Jan. Daarom was mijn bedoeling dat wanneer ik de volledige naam en voornamen in cel I3 plaats het systeem deze splitst over de overige cellen in voorletters, achternaam en voornamen. Vervolgens moet hij van Tiel, Dirk Jan aanpassen in: van Tiel en dit idem voor het adres.

Ik realiseer mij dat ik hier niet voldoende duidelijk over ben geweest en ging er teveel vanuit dat het voorbeeld voor zich spreekt, mijn verontschuldiging hiervoor.
 
Wat je vraagt is precies wat het voorbeeld doet.
Kijk in het document dat ik plaatste naar de functies in die cellen.
 
Klopt, daarom gaf ik ook aan dat ik niet volledig genoeg was en dat spijt me echt.

Ik heb op het forum een soortgelijke vraag gevonden, alleen is de uitkomst dermate technisch dat ik het niet in mijn situatie weet te gebruiken.

De code aldaar luidt:
Code: 
  With Columns(1)
   For j = 1 To 9
     .Replace " " & j, "_" & j
     For jj = 97 To 122
        .Replace j & Chr(jj), j & "_" & Chr(jj), , , True
        .Replace j & " " & Chr(jj), j & "_" & Chr(jj), , , True
        .Replace j & Chr(jj) & "_", j & "_" & Chr(jj) & "_"
     Next
   Next
   .TextToColumns , , , , 0, 0, 0, 0, -1, "_"
  End With

De code zoekt in kolom A op adres en postcode en splitst deze uit over de kolommen B, C, D en E. Stel in kolom A staat: Nieuwjaarstraat 4a 2017AA, dan splitst voornoemde code resp. over de kolommen A t/m E: Nieuwjaarstraat - 4 - a - 2017 - AA. Dit is zelfs nog specifieker dan mijn wens is. Maar dit is wel het idee achter mijn vraag.
 
Ik kan alleen nog maar weer zeggen, kijk naat de code die ik plaatste.
Daar staat precies in Case 1 t/m 4 wat je moet doen om de boel te splitsen.
 
Zal ik dan mijn vraag maar opnieuw stellen, maar dan compleet? Je hebt gelijk dat jouw antwoord juist is zoals ik deze in eerste instantie heb gesteld. Ik ging er onterecht van uit dat het duidelijk was. Daarom heb ik geprobeerd de vraag later wat duidelijker te stellen, maar je blijf volharden dat jouw antwoord, zoals aanvankelijk was gesteld, juist is en daar geef ik jou ook gelijk in.
 
Ik heb nu weinig tijd maar als er vanavond nog niemand anders heeft gereageerd zal ik er nog eens naar kijken.
Hij plaatst de correcte gegevens, maar de bron van die gegevens moet dus zodanig worden gewijzigd dat alleen het nodige overblijft.
 
Het zou echt super zijn als je me wil helpen. Ik heb nog iets gevonden wat er, t.a.v. het adres splitsen er ook op lijkt.

Code: 
Sub Splitsen()
  With Sheets("Blad1") 'Zet je eigen Bladnaam tussen de " "
    regel = [S1048576].End(xlUp).Row 'Laatste regel in de kolom
    For Each cl In Range("S2:S" & regel)
        A = cl.Value 'Inhoud van de cel
      For i = 1 To Len(cl)
        If IsNumeric(Mid(cl, i, 1)) Then Exit For
      Next
        Part = Left(cl, i - 2) 'Hier wordt het gesplits
        cl.Offset(0, 1).Value = Right(A, Len(A) - Len(Part))
        cl.Value = Part
    Next
  End With
End Sub
 
Het op die manier splitsen van de gegevens zoui ik niet doen maar op de manier die ik liet zien.
 
Ik hoop dat je antwoord hebt, op de wijze die jij voorstelt, zodat ik de naam (zonder voornaam) en het adres (zonder postcode en plaats) kan wijzigen.
 
het kan, behalve de straat, natuurlijk ook met formules, maar dit stond in het VBA-deel van de site
Zie bijlage, een oplossing met formules en 1 met VBA via change_event.
Dat is eigenlijk de 2 zwart-wit oplossingen, waar Edmoor's manier ergens halfweg tussenin zit.
 

Bijlagen

  • Adres.xlsm
    28,2 KB · Weergaven: 29
Bedankt voor jouw antwoord,

Ik waardeer het enorm dat je meedenkt en een oplossing heb bedacht. Toch denk ik, dat gelet op de andere antwoorden op dit forum bij gelijksoortige vragen, het ook anders moet kunnen. Volgens mij moet het zonder formules in de cellen en via uitsluitend een vba-code ook mogelijk zijn. Wanneer ik in een cel een adres, gevolgd door een komma, een postcode (bestaande uit 6 posities) en een plaatsnaam, vermeld dan is daar een code voor dat deze de gegevens op de juiste plaatsen zet en vervolgens de brongegevens aanpast door het adres te laten staan en alles voorbij de komma te verwijderen. Dit geldt ook voor de naam en de voornaam.

Ik hoop dat iemand hiervoor de oplossing heeft.
 
Laatst bewerkt:
ik denk dat je enkel naar de formule-oplossing gekeken hebt.
Daarnaast was er een oplossing op event_basis in de module van dat blad, die schreef haar resultaten weg in de O-kolom. (zie vorige bijlage)
Daarin moet je enkel nog de cellen aanpassen waar het resultaat moet weggeschreven worden, hierna in het rood vermeld.
Dat heb ik zelf niet gedaan omdat dan de formules overschreven werden.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
   Application.EnableEvents = False

   If Not Intersect(Target, Range("I3")) Is Nothing Then
      naam = Split(Range("I3").Value, ",")       'split naam op de komma
      If UBound(naam) = 1 Then                   'er zijn 2 delen
         Range("[COLOR="#FF0000"]O4[/COLOR]").Value = Trim(naam(0))       'gestripte 1e deel
         Range("[COLOR="#FF0000"]O5[/COLOR]").Value = Trim(naam(1))       'gestripte 2e deel
      Else
         Range("[COLOR="#FF0000"]O4[/COLOR]").Value = ""
         Range("[COLOR="#FF0000"]O5[/COLOR]").Value = ""
      End If
   End If

   'UITVOEREN ZODRA HET WOONADRES, POSCODE EN PLAATS IS GEVULD

   If Not Intersect(Target, Range("F8")) Is Nothing Then
      adres = Split(Range("f8").Value, ",")      'split naam op de komma
      If UBound(adres) = 1 Then                  'er zijn 2 delen
         Range("[COLOR="#FF0000"]N9[/COLOR]").Value = Trim(adres(0))      'wordt straks F8
         Range("[COLOR="#FF0000"]O9[/COLOR]").Value = Left(Trim(adres(1)), 4) & " " & Mid(Trim(adres(1)), 5, 2)   'gestripte 1e deel, wordt straks F9
         Range("[COLOR="#FF0000"]P9[/COLOR]").Value = Mid(Trim(adres(1)), 8)   'gestripte 2e deel, wordt straks G10
      Else
         Range("[COLOR="#FF0000"]N9[/COLOR]").Value = ""
         Range("[COLOR="#FF0000"]O9[/COLOR]").Value = ""
         Range("[COLOR="#FF0000"]P9[/COLOR]").Value = ""
      End If
   End If

   Application.EnableEvents = True
End Sub
 
Laatst bewerkt:
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan