Formule om alleen de waarde achter de dubbele punt weer te geven

[carlocarlo]

Ik heb een groot Excel bestand waarin in iedere cel tekst staat met daarachter een dubbele punt met een waarde. Bijvoorbeeld 'tekst : helpmij 123'. Door middel van 'tekst naar kolommen' zou ik het kunnen splitsen maar dat kost mij veel tijd. Het zijn namelijk 26 kolommen. Is er misschien een macro waarmee je alles tot en met de dubbele punt verwijderd en alleen nog de waarde na de dubbele punt zichtbaar is?

 

[Cobbe]

Voeg een werkblad toe en gebruik deze formule:

=ALS.FOUT(DEEL(Blad1!A1;VIND.SPEC(":";Blad1!A1)+1;LENGTE(Blad1!A1));"")

Dan copy/paste waarden terug naar blad1 et vwalla!!

Je kan ook deze macro draaien:

Sub cobbe()
Dim cl As Range
For Each cl In ActiveSheet.UsedRange
cl = Mid([a1], InStr(1, [a1], ":") + 1, Len([a1]))
Next
End Sub

 

[carlocarlo]

Beste Cobbe, het werkt. Dank je wel. Dat scheelt mij een hoop tijd.:d

 

[Warme bakkertje]

Of deze.

Sub splitsen()
    sn = Sheets(1).Cells(1).CurrentRegion
    For i = 1 To UBound(sn, 2)
        For ii = 1 To UBound(sn)
            sn(ii, i) = Trim(Split(sn(ii, i), ":")(1))
        Next
    Next
    Sheets(1).Cells(1).Resize(UBound(sn), UBound(sn, 2)) = sn
End Sub

 

[carlocarlo]

Hallo Rudi,

Ik krijg een foutmelding in de volgende regel:

sn(ii, i) = Trim(Split(sn(ii, i), ":")(1))

met vr. groet,
Carlo

 

[Cobbe]

Dat komt doordat uw gegevens niet beginnen in A1

 

[carlocarlo]

Beste Cobbe en Rudi

Als ik de eerste regel weghaal dan krijg ik een melding in de volgende regel:
For i = 1 To UBound(sn, 2)

Er is nog iets waar ik tegenaan loop. De gegevens in het te bewerken bestand staan, worden door middel van een macro in het betreffende tabblad gezet. We gegevens komen uit een txt bestand.
Als ik vervolgens onderstaande macro draai dan krijg ik dus de foutmelding.

Maar als ik dezelfde gegevens naar een nieuw bestand kopieer, en dan de macro draai, dan werkt alles perfect. Ik ben al 2 uur bezig maar kom er niet uit. Hebben jullie een idee wat er mis gaat?
Sub splitsen()
sn = Sheets(1).Cells(1).CurrentRegion
For i = 1 To UBound(sn, 2)
For ii = 1 To UBound(sn)
sn(ii, i) = Trim(Split(sn(ii, i), ":")(1))
Next
Next
Sheets(1).Cells(1).Resize(UBound(sn), UBound(sn, 2)) = sn
End Sub

 

[Warme bakkertje]

Als het werkt in een nieuw bestand dan zouden we indien mogelijk toch de originele data moeten kunnen bekijken om te zien waar het mis loopt, want dan werkt deze macro wel degelijk.

 

[Cobbe]

En wat scheelt er aan mijn macro?

 

[carlocarlo]

Sorry, dat was ik vergeten terug te koppelen. Het lijkt er op dat bij het gebruiken van jouw macro alleen het eerste veld word gepakt. De waarde achter de dubbele punt, uit de eerste cel, verschijnt vervolgens in alle gevulde cellen. Ik heb het bestand me beide macro's aan dit bericht toegevoegd.

 

[Cobbe]

Ja hij bleef telkens naar A1 kijken, sorry klein foutje:

Sub cobbe()
Dim cl As Range
For Each cl In ActiveSheet.UsedRange
cl = Mid(cl, InStr(1, cl, ":") + 1, Len(cl))
Next
End Sub

 

[carlocarlo]

Het werkt, super bedankt. Een prettige dag verder.

Met vriendelijke groet,
Carlo