Via een formule tekens uit een lijst met waarden filteren

[gridishere]

Hallo allemaal,
Ik ben bezig met een lijst aan waarden te bewerken. Voor mij is het van bellang dat ik alleen de waarden aan de rechterkant die het procent weergeven er uitgefiltert krijg. Dit lukt me tot nu toe alleen om bepaalde karaters er uit te filteren. Sommige veranderingen kan ik oplossen met =SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(A2;"""N/A - ";"");"N/A - ";"");"""";"");"+";"")+0

Nu zijn deze lijst aan waarden afkomstig van een website dus aan veranderingen onderhevig. Graag wil ik met 1 formule mijn uitkomst kunnen doorvoeren. Wie zou me hier verder mee kunnen helpen? Misschien met een andere oplossing?

Bekijk bijlage Voorbeeld grid.zip

Als het niet duidelijk genoeg is leg ik het graag nog wat uit.
Bij voorbaat dank,
grid

 

[wampier]

moet het met formules of mag het ook met een macro? Ik heb even een macrootje gemaakt gebaseerd op 'regular expressions' en die haalt alle waarden er perfect uit.

Ik verbaas me nog steeds dat RegEX niet onderdeel is van het standaard takenpakket van VBA, maar dat is een ander verhaal.

Indien het met een macro mag, vertel me dan even hoe bedreven/ervaren je bent met VBA dan kan ik daar rekening mee houden voor eventuele verdere uitleg.

Voor de geïnteresseerden alvast:

Sub test()

    Dim colmatches As MatchCollection
    Dim myMatch As Match
    Dim r As New VBScript_RegExp_55.RegExp
    
    r.Pattern = "[-0-9]+,+[0-9]+%"
    
For Each myCell In [a2:a27]

    If r.test(myCell.Value) Then
        Set colmatches = r.Execute(myCell.Value)
        For Each myMatch In colmatches
            myCell.Offset(0, 5).Value = myMatch.Value
        Next
    End If
Next
    
End Sub

deze macro maakt gebruik van de module "Microsoft VBScript Regular Expressions 5.5"

 

[gridishere]

Beste wampier, allereerst mijn complimenten. Na een test te hebben uitgevoerd werkt dit idee inderdaad goed. Die vraag of het met een formule moet of niet kan ik als volgend beantwoorden. De waarden veranderen automatisch mee en dan wordt de subroutine test() keer op keer niet meegevoerd. Dus eigenlijk moet het wel met formules denk ik. Dus als je dit nou ook nog met een formule kunt bereiken dan zou het helemaal af zijn.

Wederom mijn dank,
grid

 

[wampier]

je kunt de macro natuurlijk ook afroepen indien een cel van waarde veranderd (in principe doet excel zelf dit ook).

Ik verwacht dat het met puur formule's een erg moelijke opgave gaat worden.

je kunt ook een beetje cheaten:

Function PakPercentage(InCell As Range) As String
    Dim colmatches As MatchCollection
    Dim myMatch As Match
    Dim r As New VBScript_RegExp_55.RegExp
    Dim tempstring As String
        
    r.Pattern = "[-0-9]+,+[0-9]+%"
    
For Each myCell In InCell

    If r.test(myCell.Value) Then
        Set colmatches = r.Execute(myCell.Value)
        For Each myMatch In colmatches
            tempstring = myMatch.Value
        Next
    End If
Next
PakPercentage = tempstring
End Function

en dan =pakpercentage(A2)

echter moet je die formule dan weer met de hand doortrekken, terwijl afroepen op verandering automatisch kan op het hele bereik zonder handwerk

 

[gridishere]

Hallo
Ik merk dat het allemaal al beter overkomt. Vanaf de formule berekend worden er geen waarden meegevoerd. Wanneer het document zal worden opgeslagen wel.

Als dit nog omgezet kan worden ben ik erg blij. Misschien kan iemand het ook zonder omweg werkend krijgen?

Zie voorbeeld.
Bij voorbaat dank!
grid

 

[wampier]

Ik begrijp je vraag niet helemaal goed? bedoel je dat de uitkomst er nu uitziet als tekst ipv een getal?

 

[gridishere]

wampier, waarschijnlijk is dit toch een onduidelijke uitleg van mij geweest! Hoe dan ook, als je A2 een beetje veranderd met andere waarden, en je hebt nu in D2 bijvoorbeeld staan =PakPercentage(A2) dan worden de waarden niet automatisch doorgevoerd.

Dank,
grid

 

[wampier]

Dat is vreemd, want bij mij werkt dat. Als ik in je voorbeeldfile een waarde aanpas veranderd kolom 'G' direct.

Weet je zeker dat calculation automatic aanstaat en dat je niet in design mode bent?

 

[gridishere]

wampier,
Ik merk dat de berekeningen worden doorgevoerd Dit doet de instelling Werkmap berekenen: automatisch
Inderdaad, weer heb je gelijk. Kort gezegd: nu werkt het wel. Dus hartelijk dank. Nu ben ik nog zeer benieuwd of iemand dit ook via een reguliere formule lukt

Groet,
grid

 

[wampier]

ik zou persoonlijk dit gebruiken op blad1 (niet in module1):

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, [a:a]) Is Nothing Then
    
        Dim colmatches As MatchCollection
        Dim myMatch As Match
        Dim r As New VBScript_RegExp_55.RegExp
    
        r.Pattern = "[-0-9]+,+[0-9]+%"
    
        For Each myCell In Target

            If r.test(myCell.Value) Then
                Set colmatches = r.Execute(myCell.Value)
                For Each myMatch In colmatches
                    myCell.Offset(0, 5).Value = myMatch.Value
                Next
            End If
        Next
    End If

End Sub

hoef je ook nooit meer de formule door te trekken

 

[gridishere]

wampier, toch heb ik nog een aanvulling. Van oorsprong krijg ik mijn gegevens met een punt in plaats van een komma er tussen. Nu viel dat kwartje nu pas bij mij. Van te voren had ik mijn gegevens al wat aangepast. Als de gegevens zijn doorgevoerd moet het wel een comma worden uiteindelijk. Zou je ook nog de macro kunnen aanpassen zodat hij inspeelt op waarden als deze:


"N/A - -0.60%"
N/A - -0.36%
N/A - -0.36%
N/A - 0.00%
N/A - 0.00%
N/A - 0.00%
N/A - 0.00%
"N/A - 0.00%"
"N/A - -0.30%"
"N/A - -0.35%"
N/A - -0.33%
N/A - -0.81%
N/A - +0.00%
N/A - -0.43%
N/A - -0.79%
N/A - -0.85%
"-455.84 - -1.95%"
"-6.99 - -0.07%"
"-20.39 - -0.71%"
N/A - 0.00%
N/A - 0.00%
"0.00 - 0.00%"
N/A - 0.00%
"-45.84 - -71.95%"

"-455.84 - -100.00%"

Groetjes,
grid

 

[wampier]

Function PakPercentage(InCell As Range) As Variant
    Dim colmatches As MatchCollection
    Dim myMatch As Match
    Dim r As New VBScript_RegExp_55.RegExp
    Dim tempstring As Variant
        
    r.Pattern = "[-0-9]+\.+[0-9]+%"
    
For Each myCell In InCell

    If r.test(myCell.Value) Then
        Set colmatches = r.Execute(myCell.Value)
        For Each myMatch In colmatches
            tempstring = myMatch.Value
            tempstring = Replace(tempstring, ".", ",")
        Next
    End If
Next
PakPercentage = tempstring
End Function

de functie zet nu ook meteen een komma in de plaats van de punt. Indien dit niet de bedoeling is, moet je even de "replace" regel weghalen

 

[gridishere]

wampier, nou, weer bedankt voor je vlugge antwoord! ik zie dat het nu werkt als de bedoeling is. Als de veranderingen zijn doorgevoerd som ik op welke macro het beste past.

Groet,
grid

 

[wampier]

indien de macro moet werken met punten en komma's kun je deze gebruiken:

r.Pattern = "-*[0-9]+(,|\.)+[0-9]+%"

afgezien van de replace hoef je normaal gesproken dan niets aan de andere formules te veranderen.

 

[gridishere]

wampier, heel netjes dat je gelijk meerdere oplossingen geeft. Veel dank.

Groet,
grid