Opgelost Tekst in cel opsplitsen

[Marthy Mc Fly]

Beste experts,

Ik zou graag via een cmd button bekomen dat de tekst die in kolom E geplakt wordt, beperkt wordt tot max 132 karakters per cel.
Het vervolg van de tekst gaat verder in de volgende cel in dezelfde kolom. Indien mogelijk de tekst splitsen na een punt en niet midden van een woord.
Als de tekst van cel E1 is opgesplitst moet de eerst volgende rij blanco zijn en doen we hetzelfde voor volgendeIn het voorbeeld bestand zie je wat de bedoeling is.

 

[snb]

met een programma dat zulke beperkingen aan invoer oplegt wil je toch niet werken ?

 

[Marthy Mc Fly]

Zou me veel tijd besparen bij overdracht van data, want migratieproces plaatst limiet op sommige velden.
Dat programma kost nogthans zeer veel geld.

 

[snb]

Dan hebben ze daar vast wel programmeurs die zulke beperkingen kunnen opheffen.

 

[JEC.]

Hier een recursieve oplossing voor office 365. Output staat in cel I1 met 1 dynamische formule. Simpel is het niet.

=DROP(REDUCE("";E1:E2;LAMBDA(x;y;VSTACK(x;
LET(R;LAMBDA(R;a;b;LET(c;LEN(a)<133;d;IF(c;a;TEXTBEFORE(LEFT(a;133);". ";-1;;1)&".");
IF(c;DROP(VSTACK(b;d);1);R(R;MID(a;LEN(d)+2;6^6);VSTACK(b;d)))));R(R;y;""));"")));1)

 

[JEC.]

Hier ook nog recursieve VBA oplossing. Haal eerst je voorbeelduitkomsten weg uit kolom E, zodat je alleen nog je werkelijke data overhoudt. Na het runnen van deze code zal je tekst in kolom E automatisch omgezet worden.

Dim ar(1000, 0), y As Long
Sub jec()
 Dim ary, j As Long
 ary = Range("E1", Range("E" & Rows.Count).End(xlUp))
 For j = 1 To UBound(ary)
   If j > 1 Then y = y + 2
   chunk ary(j, 1), t
 Next
 Range("E1").Resize(y + 1) = ar
 y = 0: Erase ar
End Sub

Sub chunk(ByVal xStr As String, ByVal t As String)
 Dim it, x
 For Each it In Split(xStr)
   t = Trim(Join(Array(t, it), " "))
   If Len(t) > 131 Then
      x = Trim(Left(t, InStrRev(t, ".")))
      ar(y, 0) = x: y = y + 1
      chunk Trim(Replace(xStr, x, "")), ""
      Exit Sub
   End If
 Next
 ar(y, 0) = t
End Sub

 

[snb]

Wat heeft een punt met de lengte van 132 tekens en 'niet midden in een woord' te maken ?

 

[OctaFish]

TS schrijft in zijn vraag over Max lengte van 132 tekens. Daarmee zou je midden in een woord kunnen afbreken als je altijd 132 tekens neemt. Of je kijkt naar het laatste volledige woord vóór de 132 tekens zijn bereikt. Dat zou mijn insteek ook zijn . Of je op hele zinnen wilt overgaan naar de volgende regel, lijkt mij wat dubieus omdat je dan wel hele korte regels kan krijgen.

 

[snb]

En 'hele zinnen' is een heel ander criterium dan 'max 132 tekens'.

 

[JEC.]

Gewenste uitkomst stond netjes in het vb bestand

 

[Marthy Mc Fly]

Ik krijg volgende foutmelding.

 

[JEC.]

Plaats dan eens het bestand waar het misgaat. Als het veel data betreft moet de array “ar” groter worden. Maak er eens van:

Dim ar(60000, 0)

 

[Marthy Mc Fly]

Als ik die waarde naar om het even welk getald verander dan geeft hij als fout onvoldoende stackruimte.

Dim ar(100, 0), y As Long
Sub jec()
 Dim ary, j As Long
 ary = Range("E1", Range("E" & Rows.Count).End(xlUp))
 For j = 1 To UBound(ary)
   If j > 1 Then y = y + 2
   chunk ary(j, 1), t
 Next
 Range("E1").Resize(y + 1) = ar
 y = 0: Erase ar
End Sub

Sub chunk(ByVal xStr As String, ByVal t As String)
 Dim it, x
 For Each it In Split(xStr)
   t = Trim(Join(Array(t, it), " "))
   If Len(t) > 131 Then
      x = Trim(Left(t, InStrRev(t, ".")))
      ar(y, 0) = x: y = y + 1
      chunk Trim(Replace(xStr, x, "")), ""
      Exit Sub
   End If
 Next
 ar(y, 0) = t
End Sub

Hij geeft een fout aan op ar(y, 0) = x

Het rare is dat het plots wel werkte en dat ook een tijdje is blijven doen. Na het opslaan van het bestand op sharepoint kreeg ik opnieuw de foutmelding.

 

[JEC.]

Had je de array dus groter gemaakt en daarna werkte het? Out of stack space error heb je vaker met recursieve oplossingen.

Gaat het om grote hoeveelheden data?
Wanneer het werkte, was dat op een kleinere dataset?

 

[Marthy Mc Fly]

Neen het werkte zelfs met dezelfde array.
Ik heb zowel grote als kleinere dataset gehad.
Na het opslaan van het bestand kreeg ik opnieuw de foutmelding.
Een collega van mij krijgt dezelfde foutmelding

 

[JEC.]

Bij grotere datasets zal het misgaan door te veel geneste functies als gevolg van recursie. Werk je niet toevallig met office 365?

 

[Marthy Mc Fly]

Ik werk met office 365, maar niet online.

 

[JEC.]

Heb je de formule optie ook geprobeerd?

 

[Marthy Mc Fly]

De formule optie werkt niet bij mij. Als er een rij is die over de 132karakters gaat dan doet hij rare dingen. Hij gaat de zin wel trimmen, maar het eerste gedeelte van de zin dat teveel is toont hij niet. Hij plaats 8 rijen er boven met telkens een punt in.

 

[JEC.]

In het voorbeeldbestand van jou werkt het prima. Heb je een nieuw bestand waar het niet werkt?
Ik neem aan dat er een aantal zinnen in het werkelijke bestand staan in kolom E. Deze lange zinnen dienen opgesplitst te worden naar zinnen onder de 133 karakters.