Opgelost Tekstbestanden splitten plaatsbepaling

[Georgyboy]

Hallo ieder,

Heb een goed werkende VBA code om tekst naar een tabel om te zetten.
Doch wil me daar verder in verdiepen in het onderdeel om tekst naar kolommen te splitten.
Heb daar een handige formule voor gekregen (waarvoor dank!), Graag had ik geweten of dit ook kan met VBA?


Hier loopt het bijgevoegde Tekst bestand wel vast voor invoeging in het tabblad "resultaat"

Alvast bedankt!

 

[emields]

bedoel je zoiets?

 

[Georgyboy]

Dankjewel emields, handig

Kunnen we ook de plaatsbepaling zien waar we kunnen splitten?

Voorbeeld om hier de plaatsen te kunnen bepalen?

ReDim resultaat(1 To UBound(tekst) + 1, 1 To 6)
For i = 1 To UBound(tekst)
resultaat(i, 1) = Trim(Left(tekst(i), 32))
resultaat(i, 2) = Mid(tekst(i), 33, 9)
resultaat(i, 3) = --Trim(Mid(tekst(i), 43, 8))
totaal_gew = totaal_gew + resultaat(i, 3)
resultaat(i, 5) = --Trim(Mid(tekst(i), 71, 11))
resultaat(i, 6) = --Trim(Mid(tekst(i), 83, 15)) / 100
totaal_kost = totaal_kost + resultaat(i, 6)

 

[cow18]

je kan het eventueel ook nog met 365-formules doen, ik geef de aanzet, vermoedelijk zal het iemand nog beter afmaken zodat je getallen en percenten vanaf de 3e kolom ook nog netjes afgewerkt worden.
En vermoedelijk bestaat er nog een PQ-versie (waar ik geen kaas van gegeten heb).

 

[JEC.]

In ene

=LET(t;TRIM(A7:A19);s;TEXTAFTER(t;" ";-8);HSTACK(SUBSTITUTE(t;s;"");DROP(REDUCE("";s;LAMBDA(a;b;VSTACK(a;TEXTSPLIT(b;" "))));1)))

 

[emields]

We zijn weer aan het gokken hoe TS het wil, Als je het doet zoals hij het vraagt(voorbeeld) is het blijkbaar niet goed. Maar erbij zeggen waar hij het wil splitsen doet hij niet. Ofwel weet hij zelf niet wat hij zoekt.

 

[Georgyboy]

Hallo dank voor de vele mogelijkheden,

Sorry indien mijn vraagstelling misschien niet duidelijk was!

Mijn vraag kwam eigenlijk omdat ik dankzij jullie een zéér goed werkende code heb "Sub kostprijs"
me daar verder wou in verdiepen om te weten waar gesplitst moet worden zoals hier.
Tabblad "Tekst" naar tabblad "resultaat"

Dit staat in de code en is interessant te weten hoe dit te bepalen.

resultaat(i, 1) = Trim(Left(tekst(i), 32))
resultaat(i, 2) = Mid(tekst(i), 33, 9)
resultaat(i, 3) = --Trim(Mid(tekst(i), 43, 8))
totaal_gew = totaal_gew + resultaat(i, 3)
resultaat(i, 5) = --Trim(Mid(tekst(i), 71, 11))
resultaat(i, 6) = --Trim(Mid(tekst(i), 83, 15)) / 100
totaal_kost = totaal_kost + resultaat(i, 6)

Daarmee de vraag hoe dit ipv van manueel in het tekstbestand de positie te bepalen of via een formule te beter te kunnen doen.
De macro van emields helpt mee daarbij alsook de vele formules van jullie allen.
Zie "test emiel" naar "emiel_resultaat"

De code van @jec

=LET(t;TRIM(A7:A19);s;TEXTAFTER(t;" ";-8);HSTACK(SUBSTITUTE(t;s;"");DROP(REDUCE("";s;LAMBDA(a;b;VSTACK(a;TEXTSPLIT(b;" "))));1)))

geeft fout #NAAM? omwille van mijn excel versie

ook de vertaalde? versie geeft de fout #NAAM?

=LET(t;SPATIES.WISSEN(A7:A19);s;TEKST.NA(t;" ";-8);HORIZONTAAL(SUBSTITUEREN(t;s;"");WEGLATEN(SAMENVATTEN("";s;LAMBDA(a;b;VERT.SPLITSEN(a;TEKST.SPLITSEN(b;" "))));1)))

Waarschijnlijk deze niet beschikbaar:

horizontaal
samenvatten
vert.splitsen

@cow18 wederom top om van te leren

Nogmaals dank!

 

[JEC.]

Reduce = Reduce
Vstack = vert.stapelen
Hstack = hor.stapelen

 

[Georgyboy]

@ Jec,

Jou formule gelukt met wat aanpassing

=LET(t;SPATIES.WISSEN(A7:A18);s;TEKST.NA(t;" ";-8);HOR.STAPELEN(SUBSTITUEREN(t;s;"");WEGLATEN(REDUCE("";s;LAMBDA(a;b;VERT.STAPELEN(a;TEKST.SPLITSEN(b;" "))));1)))

 

[cow18]

deze werkt op die "tekst naar kolommen.txt" en hoef je enkel te weten hoeveel kolommen je wil uitkomen.
Het is wel zonder die afwerking, dus enkel inlezen, macro "kostprijs"
Edit : gebruik bijvoorbeeld "Courier"-lettertype op de kolom A om je tekstbestand beter te kunnen lezen (true-lettertype)

 

[JEC.]

Zo via Power Query, alleen even het pad aanpassen in de source regel. Wijst zich vanzelf.
Open een lege query en plak dit in de advanced editor

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\xxx\Downloads\Tekst voor kolommen.txt"))}),
    skip = Table.Skip(Source,4),
    cols = {"Omschrijving", "ZrtNr","NattR","ArutR","ArutRUnit","PV%","X","PrYjs","AAdrZg"},
    convert = Table.AddColumn(skip, "Custom", each
        [
              spList = List.Select(Text.Split([Column1]," "), (x)=> x <> ""),
              vData = List.LastN(spList,8),
              out = {Text.Combine(List.RemoveItems(spList,vData)," ")} & vData
        ][out]
    ),
    toTable = Table.FromRows(convert[Custom],cols),
    types = Table.TransformColumnTypes(toTable,List.Zip({{"ArutR", "NattR", "PrYjs","AAdrZg"},List.Repeat({type number},4)}))
in
    types

 

[Georgyboy]

Dankjewel voor deze leerrijke uitwerking @cow18 !

Mijn vragen zijn misschien niet altijd duidelijk voor ieder, maar de antwoorden zijn steeds TOP!

Mag ik mijn vraag nog even anders formuleren?
Via de formules kunnen we een plaatsbepaling voor het splitsen van tekst bepalen met het aantal karakters (Lengte tekst per kolom).

Dit kunnen we ook in het tekstbestand via de cursor.
Hoe kunnen we dan zonder formule (reeds goede tips met formules ontvangen) de plaatsbepaling en de tekstlengte bepalen om in de VBA code te gebruiken?

Alvast bedankt!

 

[Georgyboy]

@ JEC.

Ook gesplit met deze Power Query code, TOP!
Heel wat tabellen bevatten de "error" tekst
dit moet waarschijnlijk ook makkelijk te verwijderen zijn naar een leeg veld.

Vermoed dat we met Power Query direct de oplossing hebben naar gesplitte kolommen?
IPV de Bepaling van de Begin en tekst lengte per kolom?

esultaat(i, 1) = Trim(Left(tekst(i), 32))
resultaat(i, 2) = Mid(tekst(i), 33, 9)
resultaat(i, 3) = --Trim(Mid(tekst(i), 43, 8))
totaal_gew = totaal_gew + resultaat(i, 3)
resultaat(i, 5) = --Trim(Mid(tekst(i), 71, 11))
resultaat(i, 6) = --Trim(Mid(tekst(i), 83, 15)) / 100
totaal_kost = totaal_kost + resultaat(i, 6)

Knap wat ik van jullie allen mag leren!

Iedereen hartelijk bedankt daarvoor!

 

[JEC.]

Ja je hebt inderdaad de tekstlengtes niet nodig, net als bij de formule. Als je voorbeelden kan plaatsen waar hij vastloopt kan ik weer even kijken morgen

 

[peter59]

Ik heb het een klein beetje aangepast en nu doet hij het.
Maar alle credits gaan naar @jec

let
    Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    skip = Table.Skip(Source,1),
    #"Kolomkoppen met niveau verlaagd1" = Table.DemoteHeaders(skip),
    #"Bovenste rijen verwijderd" = Table.Skip(#"Kolomkoppen met niveau verlaagd1",1),
    cols = {"Omschrijving", "ZrtNr","NattR","ArutR","ArutRUnit","PV%","X","PrYjs","AAdrZg"},
    convert = Table.AddColumn(#"Bovenste rijen verwijderd", "Custom", each [
              spList = List.Select(Text.Split([Column1]," "), (x)=> x <> ""),
              vData = List.LastN(spList,8),
              out = {Text.Combine(List.RemoveItems(spList,vData)," ")} & vData
        ][out]),
    toTable = Table.FromRows(convert[Custom],cols),
    types = Table.TransformColumnTypes(toTable,List.Zip({{"ArutR", "NattR", "PrYjs","AAdrZg"},List.Repeat({type number},4)}))
in
    types

 

[Georgyboy]

Dankjewel @ JEC

Dankjewel @ peter59

Dank voor de vele mogelijkheden ook met power query

 

[snb]

Of zó:

Sub M_snb()
  With CreateObject("scripting.filesystemobject")
     sn = Filter(Split(Application.Trim(.opentextfile("G:\OF\tekst voor kolommen.txt").readall), vbCrLf), " ")
     y = UBound(Split(sn(0))) + 2

     For j = 1 To UBound(sn)
       sn(j) = Replace(Replace(Replace(sn(j), " kg", "kg"), " x", "x"), " ", Chr(160), , UBound(Split(sn(j))) - y)
     Next
     .createtextfile("G:\OF\V_001.txt").write Join(sn, vbCrLf)
  End With

  Workbooks.OpenText "G:\OF\V_001.txt", , , , , , 0, 0, 0, 1, 0
End Sub

 

[Georgyboy]

Dankjewel snb,
Maak hier ergens een fout in het pad?
"\\xxxx-fp01\userdesk$\Ge\Desktop\Ge\Computer tips\Excel VBA testen"
ook hier via de C: Schijf
sn = Filter(Split(Application.Trim(.opentextfile("C:\georges").readall), vbCrLf), " ")

Wat zou ik fout doen?

 

[snb]

Het moeten natuurlijk wel txt-bestanden zijn. Die extensie zie ik bij jou niet.

 

[Georgyboy]

Duidelijk, dankjewel!