alternatief voor tekst naar kolommen

[Marc666]

ik heb een cel (komende uit een csv) waarin het volgende staat:

1 omschrijving 10 A

1 is het aantal
omschrijving kan van alles zijn (ook 2 of meerdere woorden)
10 is altijd een bedrag
B is een verwijzing naar een BTW tarief

de bedoeling is dat dit in verschillende kolommen komt te staan.

het aantal en het btw tarief heb ik er al uit kunnen filteren met een links en een rechts formule

ik loop echter vast op het "filteren" van de andere gegevens. tekst naar kolommen kan ik in deze niet echt gebruiken omdat de omschrijving dus uit meerdere woorden kan bestaan.

in bijlage een voorbeeld bestand.

in de kolom item kunnen ook andere gegevens staan in bepaalde rijen die ik niet nodig heb. deze moeten niet in de uiteindelijke tabel worden opgenomen. vandaar dat ik in mijn formule ook "niet nodig" heb opgenomen zodat ik daar op kan gaan filteren

wie kan mij hier bij helpen?

 

[edmoor]

Hoe staat dat in het CSV bestand?
Plaats daar ook een voorbeeldje van.

 

[Marc666]

zie bijlage voor het csv bestand

 

[edmoor]

Als je dat zo laat aanleveren heb je er in Excel verder geen omkijken naar:

DocType;Type;ClosureNum;TicketNum;ItemNum;Date;Time;Item;Bedrag;BTW Code
1;0;3;1;3;20160106;165134;1 BIER/FRISDR;1.00;A
1;0;3;1;4;20160106;165134;CORRECTIE;
1;0;3;1;5;20160106;165134;1 BIER/FRISDR;-1.00;A

 

[Marc666]

zou heel mooi zijn moest dit kunnen maar jammer genoeg kan dit niet. deze bestanden worden zo aangeleverd en komen uit een kassa systeem.

moet dus echt n manier vinden mo de gegevens er uit te filteren....

 

[peter59]

Hallo Marc

Ik heb de 2 bijlagen (csv) en je eerste voorbeeld eens naast elkaar gezet.
Ik kan er helaas geen touwtje aan vast knopen.
Kan je eens in het csv bestand laten zien hoe je aan de desbetreffende gegevens komt v.w.b. bestandje 1?
Ik ben zelf aan het denken om hier power query voor in te zetten.

 

[Marc666]

peter,

het enige wat ik nodig heb uit het csv bestand is het "Item"

in het item staan een aantal, gevolgd door een omschrijving daarna een bedrag gevolgd door de btw code
in mijn voorbeeldbestand heb ik het aantal er al uit gehaald en in de eerste kolom (aantal) gezet, het btw percentage heb ik er ook al uitgehaald. resten mij het bedrag en de omschrijving er nog uit te halen.

hopelijk is het nu iets duidelijker. ik denk dat ik met tussenkolommen ga moeten werken en de reeds "overgehevelde" "zaken" uit de tussenkolom ga moeten wissen zodat ik enkel nog de omschrijving en het bedrag over houd, ben nog een het grasduidnn hoe, alle hulp en tips zijen welkom!

als ik er uit ben zal ik de oplossing hier zeker posten.

 

[Haije]

Open het bestand eens via deze route:
Gegevens > Van tekst

 

[AlexCEL]

Als je testbestandje een beetje representatief is en je kan er zelf mee uit de voeten: zie bijlage voor verdere opsplitsing.

 

[Marc666]

Als je testbestandje een beetje representatief is en je kan er zelf mee uit de voeten: zie bijlage voor verdere opsplitsing.

AlexCEL, mijn dank is groot! hier kan ik mee weg! dat is de oplossing die ik zocht ( en nog begrijp ook ;-) )

 

[AlexCEL]

Hier nog versie 1.1 met paar kleine verbeteringen (getal van tekst gemaakt bij bedrag, en formule voor BTW wat korter). Als je Excel 365 gebruikt kan e.e.a. nog eenvoudiger.
Ook nog een korter alternatief voor de formule in kolom D is:

=ALS.FOUT(XML.FILTEREN("<t><s>"&SUBSTITUEREN(SUBSTITUEREN(A2;".";",");" ";"</s><s>")&"</s></t>";"//s[translate(.,'1234567890','')!=. and .*0!=0]");"")

 

[Marc666]

Hier nog versie 1.1 met paar kleine verbeteringen (getal van tekst gemaakt bij bedrag, en formule voor BTW wat korter). Als je Excel 365 gebruikt kan e.e.a. nog eenvoudiger.
Ook nog een korter alternatief voor de formule in kolom D is:

=ALS.FOUT(XML.FILTEREN("<t><s>"&SUBSTITUEREN(SUBSTITUEREN(A2;".";",");" ";"</s><s>")&"</s></t>";"//s[translate(.,'1234567890','')!=. and .*0!=0]");"")

AlexCEL,

danku! zou je mij het weergeven van het bedrag kunnen uitleggen? ik begrijp het niet helemaal en zou het wel willen begrijpen zodat ik het in de toekomst elders ook zelf kan gaan gebruiken en toepassen.
het gedeelte van de omschrijving en de formule voor de btw begrijp ik.

alvast bedankt!

 

[AlexCEL]

Die laatste was meer voor de fun, zou ik me niet in vastbijten eerlijk gezegd.

Het komt erop neer dat je de tekst eerst omzet in XML-format en dan een XPath zoekopdracht erop los laat.

Gebruik je Excel 365? Dan zijn er misschien nog wel wat opties....

 

[snb]

Of een UDF

Function F_snb(c00)
   st = Split(Application.Trim(Replace(c00, ".", ",")))
   F_snb = st
   If UBound(st) = 4 Then F_snb = Array(Val(st(0)), st(1) & " " & st(2), --st(3), st(4))
End Function

Selecteer B2:E2 en voer als Matrixformule in =F_snb(A2)