numerieke waarden uit tekststring dmv formule

[spoel003]

Hallo ,

Ik zou graag een formule willen ontvangen die alle numeriek waarden uit een tekststring vist.

Daarbij zou het resultaat moeten zijn dat alle waarden gescheiden door een komma ( in de cel geplaatst moeten worden.

Als het niet via een formule kan dan mag een stukje vba natuurlijk ook.

Heb een voorbeeld bijgevoegd


Alvast dank,

Jaap

 

[AlexCEL]

Zit er nog enige structuur in de numerieke waarden die je zoekt? Lengte, scheidingsteken?
Datums zijn ook numerieke waarden, maar die wil je niet hebben... dat maakt een formule-optie complexer.
Hoeveel kenmerken kunnen er per regel zijn?
Welke Excel versie gebruik je?

In bijlage een optie voor 2 numerieke waarden, met 2 hulpkolommen met daarin draken van formules... hoop dat VBA een elegantere oplossing kan bieden.

 

[spoel003]

Hallo AlexCel, dank voor je reactie,

De numerieke waarden hebben geen structuur. Om je een beeld te geven betreft het factuurnummer(s) die meekomen uit de omschrijving van een betaling aan een relatie.

Je kan 1 factuur betalen dan betreft het 1 factuur , maar ook kunnen het er drie factuurnummers kunnen gezamenlijk betaald worden. Ik gebruik Excel 2020 engelse versie.

Dus liefst de formules in het engels.


Het probleem waar ik mee worstel is dat dat ik 2 bronnen heb die ik moet vergelijken. Bron die leidend is zijn de betalingen. Wat er tegenover staat zijn opgehaalde facturen maar die zijn niet compleet. Nu moet ik aangeven op basis van betalingen welke facturen ik mis. Dat is geen probleem als opgehaalde facturen 1 op 1 zijn betaald. Echter men betaald soms 2 facturen tegelijk en heb ik op basis van betaald bedrag geen houvast meer met bedrag van de factuur. Facturen worden altijd individueel opgehaald waarbij ik een bedrag en factuurnummer meekrijg. Via Vlookup zoek ik eerste bedrag betaling en kijk of deze factuur op basis van bedrag is binnengekomen. Daarna wil ik verder zoeken of facturen die zijn samengevoegd en in 1 bedrag zijn betaald ook via het factuurnummer in omschrijving kan herkennen en daarmee een kenmerk kan geven dat deze factuur is ontvangen.



Hopeeijk wordt de vraag zo een beetje duidelijk waar ik heen wil.

Groet,

Jaap

 

[AlexCEL]

Facturen worden altijd individueel opgehaald waarbij ik een bedrag en factuurnummer meekrijg

Hoe ziet dit eruit? Een factuurnummer kan ik wel zoeken in jouw lijst!

Oftewel: laat eens zien wat je met elkaar wilt vergelijken. Misschien is er een gemakkelijkere manier.

 

[spoel003]

Hierbij een voorbeeldje met de formules zoals ik nu zoek.

Misschien kun je extra voorwaarde toevoegen zodat formule ook een herkenning afgeeft als hij een factuur nummer vind uit de omschrijving en bedrag betaling niet gelijk is aan factuurbedrag.

Groet en alvast dank voor je hulp.

Jaap

 

[VenA]

Hoe komen de gegevens in het bestand tot stand? Met een macro kan veel maar dit zal een beetje maatwerk worden en dan zal je toch wat duidelijker moeten zijn. Je hebt het over factuurnummers maar deze zie ik niet. Er komt een factuur binnen op relatienummer waarbij de periode van belang is. Er komt een factuur binnen opv het contactnr waarbij het termijn van belang is. En je krijg 2 gemeentelijke aanslagen waarvan de nummers uniek zullen zijn.

Dan heb je nog een tab met opgehaalde facturen. Hoe heb je dit in elkaar geknutseld?

 

[sylvester-ponte]

als er ergens namen of relatienummers in beide sheets voorkomen , maakt dat het matchen en stuk eenvoudiger.

 

[JEC.]

Misschien dat deze macro voldoet.
Wanneer hij een foutmelding geeft, moet je de library even activeren in de VBA Editor. Tools > References > Microsoft VBScript Regular Expressions.

Sub jvr()
 ar = Sheets(1).Range("A2:C4")
 ar2 = Sheets(2).Range("B2:C5")
 
 With CreateObject("VBscript.RegExp")
  For i = 1 To UBound(ar)
     .Global = True
     .Pattern = "\ \d*\ "
     Set a = .Execute(ar(i, 1))
     
     For Each it In a
       x = Application.Match("*" & Application.Trim(it) & "*", Application.Index(ar2, 0, 1), 0)
       If IsNumeric(x) Then ar(i, 3) = Application.Index(ar2, x, 2)
     Next
       x = Application.Match(ar(i, 2), Application.Index(ar2, 0, 2), 0)
       If IsNumeric(x) Then ar(i, 3) = Application.Index(ar2, x, 2)
  Next
 End With
 
 Sheets(1).Range("A2:C4") = ar
End Sub

 

[AlexCEL]

Hier nog mogelijke optie met formules. Voor Excel 365.

 

[mumzel]

Binnen gekomen bij beheer:

Verzonden: zondag 26 september 2021 19:11
Aan: Helpmij.nl Forum <webmaster@helpmij.nl>
Onderwerp: Re: Reactie op bericht 'numerieke waarden uit tekststring dmv formule'

Beste VenA, de betalingen konen van de bank en de facturen konen uit een andere bron. Staan dus los van elkaar. Facturen die we ophalen zijn niet compleet. Om te kijken welke we missen gaan we de bron betalingen ermee vergelijken. Die liegen niet. Alles wat betaald is daar moet een factuur van zijn . Hopelijk zo duidelijk waar ik heen wil.

 

[sylvester-ponte]

misschien heb je hier iets aan:

in de formule heb ik de minimum lengte van de eventuele Relatie nummers op 5 gezet.( misschien moet je dat voor de veiligheid groter maken.)
als je hem kleiner maakt gooien de datums roet in het eten.

( het kan vast sneller en mooier ) maar het gaat om het principe .

 

[sylvester-ponte]

misschien heb je hier iets aan: zie grijze cellen

in de formule heb ik de minimum lengte van de eventuele Relatie nummers op 5 gezet.( misschien moet je dat voor de veiligheid groter maken.)
als je hem kleiner maakt gooien de datums roet in het eten.

( het kan vast sneller en mooier ) maar het gaat om het principe .

ps die E kolom in opgehaalde facturen kan weg , hij is alleen om de hulp function "mijngetal" te testen.

 

[sylvester-ponte]

kleine verbetering:

 

[JEC.]

Voor de gein een aanpassing van de macro, met dezelfde output als de functies van sylvester:d

Sub jvr()
 Sheets(1).Cells(1, 1).CurrentRegion.Offset(1).Columns(3).ClearContents
 ar = Sheets(1).Cells(1, 1).CurrentRegion
 ar2 = Sheets(2).Cells(1, 1).CurrentRegion
 
 With CreateObject("VBscript.RegExp")
  For I = 2 To UBound(ar)
     .Global = True
     .Pattern = "\ \d*\ "
     Set a = .Execute(ar(I, 1))
     
     For Each it In a
       x = Application.Match("*" & Application.Trim(it) & "*", Application.Index(ar2, 0, 2), 0)
       If IsNumeric(x) Then
          y = Application.Index(ar2, x, 3)
          xVal = xVal + y
          xTxt = xTxt & " / " & y
       End If
        ar(I, 3) = Mid(xTxt, 4) & " totaal: " & xVal
     Next
     xVal = Empty
     xTxt = Empty
  Next
 End With
 
 Sheets(1).Cells(1, 1).CurrentRegion = ar
End Sub