Excel meerdere regels splitsen naar kolommen

[Jimster]

Mijn vraag
In Excel heb ik verschillende producten in een cel staan. De producten zijn gescheiden door middel van een scheidingsteken /. Het aantal regels kan verschillen.
Hoe kan ik de producten scheiden zodat een deel in een kolom komt en het deel achter de / in een andere kolom?
Zie voorbeeld:


Relevante software en hardware die ik gebruik
Excel 2016 / 2019

Wat ik al gevonden of geprobeerd heb
Tekst naar kolommen werkt niet, omdat deze alleen de eerste regel pakt.
Verschillende zoekwoorden gebruikt in Google, maar die geven niet het gewenste resultaat.
Bijv: "meerdere regels splitsen naar 2 kolommen", "meerdere regels in 1 cel splitsen"

Enig idee of dit mogelijk is?

[itons]

https://www.ablebits.com/.../split-text-string-excel/

[g0tanks]

Wil je de verschillende producten per se in één cel houden? Het lijkt me een stuk fijner om mee te werken als je eerst elk product een eigen regel geeft.

[Jimster]

g0tanks schreef op dinsdag 18 mei 2021 @ 21:17:
Wil je de verschillende producten per se in één cel houden? Het lijkt me een stuk fijner om mee te werken als je eerst elk product een eigen regel geeft.

Ja ze moeten in 1 cel blijven.
Het zijn bestellingen, elke regel is 1 bestelling. Als alles gesplitst wordt in een aparte regel is dat niet meer overzichtelijk.

[g0tanks]

Jimster schreef op dinsdag 18 mei 2021 @ 21:21:
[...]

Ja ze moeten in 1 cel blijven.
Het zijn bestellingen, elke regel is 1 bestelling. Als alles gesplitst wordt in een aparte regel is dat niet meer overzichtelijk.

Hoeveel producten zitten er maximaal in één bestelling? Een generieke oplossing bedenken voor 3 is mogelijk een stuk makkelijker dan voor 100.

[Jimster]

g0tanks schreef op dinsdag 18 mei 2021 @ 21:26:
[...]


Hoeveel producten zitten er maximaal in één bestelling? Een generieke oplossing bedenken voor 3 is mogelijk een stuk makkelijker dan voor 100.

Ik denk max 25.

[g0tanks]

Het lijkt me nu eerlijk gezegd vrij lastig om het te doen zonder VBA.

itons schreef op dinsdag 18 mei 2021 @ 21:09:
https://www.ablebits.com/.../split-text-string-excel/

Voor dit probleem zie ik nog niet hoe die voorbeelden toereikend zijn.

[Aragnut]

Jimster schreef op dinsdag 18 mei 2021 @ 21:06:
.....
Tekst naar kolommen werkt niet, omdat deze alleen de eerste regel pakt.
.....

Probeer eerst alle rijen te selecteren in de kolom en dan te splitsen (of de kolom zelf selecteren). Ik gebruik dit veelvuldig en nog nooit gehad dat er maar 1 rij gesplitst werd.

[g0tanks]

Aragnut schreef op dinsdag 18 mei 2021 @ 21:43:
[...]

Probeer eerst alle rijen te selecteren in de kolom en dan te splitsen (of de kolom zelf selecteren). Ik gebruik dit veelvuldig en nog nooit gehad dat er maar 1 rij gesplitst werd.

Met de eerste regel bedoelt @Jimster volgens mij in dit geval de eerste regel van elke cel. Niet de eerste regel van de dataset.

Als je cel A2 in zijn voorbeeld splitst op "/" krijg je inderdaad alleen de bananen terug.

[ Voor 9% gewijzigd door g0tanks op 18-05-2021 21:48 ]

[Jimster]

g0tanks schreef op dinsdag 18 mei 2021 @ 21:46:
[...]


Met de eerste regel bedoelt @Jimster volgens mij in dit geval de eerste regel van elke cel. Niet de eerste regel van de dataset.

Als je cel A2 in zijn voorbeeld splitst op "/" krijg je inderdaad alleen de bananen terug.

Klopt inderdaad, alleen de eerste regel van de cel blijft dan over.

[Aragnut]

Jimster schreef op dinsdag 18 mei 2021 @ 22:36:
[...]

Klopt inderdaad, alleen de eerste regel van de cel blijft dan over.

ah nu zie ik het: alle data staat in 1 cel. Daar moet dan eerst wat aan gedaan worden, anders kun je er inderdaad erg weinig mee.

edit: je zal dan eerst op newline character moeten splitten, vervolgens alle (nieuwe) kolommen moeten selecteren, kopieren en plakken speciaal naar kolom. Vervolgens kun je de split op / doen.

[ Voor 23% gewijzigd door Aragnut op 18-05-2021 22:44 ]

[GRDavies75]

Je moet het allemaal zelf weten natuurlijk, maar je maakt het je zelf wel heel moeilijk door 1 bestelling in 1 cell te stoppen. Als ik jou was, dan zou ik werken met een extra kolom in de vorm van Bestellingnummer o.i.d.
Dan kan je daarna ook makkelijker werken met Tekst naar Kolommen.

[Pandorian]

Met tekst naar kollommen, on veld "anders" CTRL-J gebruiken dan splits hij op de regel

A/B
C/D
E/F
is dan A/B I C/D I E/F
die dan ook weer splitsen en samenvoegen. etc etc

[Jimster]

GRDavies75 schreef op woensdag 19 mei 2021 @ 08:44:
Je moet het allemaal zelf weten natuurlijk, maar je maakt het je zelf wel heel moeilijk door 1 bestelling in 1 cell te stoppen. Als ik jou was, dan zou ik werken met een extra kolom in de vorm van Bestellingnummer o.i.d.
Dan kan je daarna ook makkelijker werken met Tekst naar Kolommen.

Thanks, het zit wat complexer in elkaar. Origineel bestand is een CSV bestand wat weer uit een bepaald programma komt. Bestelnummer is ook beschikbaar. Meerdere cellen is geen optie, ik snap dat splitsen makkelijker is met verschillende rijen, maar het werken met het bestand wordt dan niet makkelijk. Lastig uit te leggen

Pandorian schreef op woensdag 19 mei 2021 @ 21:22:
Met tekst naar kollommen, on veld "anders" CTRL-J gebruiken dan splits hij op de regel

A/B
C/D
E/F
is dan A/B I C/D I E/F
die dan ook weer splitsen en samenvoegen. etc etc

Thanks. Ik zie dan alleen niet hoe je A/B | C/D | E/F dan weer kan splitsen naar A | B | C | D | E | F. Als dat zou kunnen, kan ik A, C, E en B,D,F weer eenvoudig samenvoegen in 1 cel.

[Aragnut]

Ik gok dat je ook niet altijd even veel regels/items hebt staan per cel. Zitten er ook newline characters in de .cvs zoals die aangeleverd wordt? Ik zou denk hiervoor toch uitwijken naar een perl of python scriptje die regel voor regel inleest, met split op newline/spatie en vervolgens per item split op / alles naar 2 arrays weg schrijven om die weer in een andere csv aan excel aan te bieden....Mogelijk kan dat ook met VBA.

[Jimster]

Aragnut schreef op woensdag 19 mei 2021 @ 23:13:
Ik gok dat je ook niet altijd even veel regels/items hebt staan per cel. Zitten er ook newline characters in de .cvs zoals die aangeleverd wordt? Ik zou denk hiervoor toch uitwijken naar een perl of python scriptje die regel voor regel inleest, met split op newline/spatie en vervolgens per item split op / alles naar 2 arrays weg schrijven om die weer in een andere csv aan excel aan te bieden....Mogelijk kan dat ook met VBA.

Klopt, per regel verschilt het aantal producten.
In het CSV bestand zitten geen newline characters. De producten staan daar achter elkaar met een paar spaties ertussen. Die spaties verander ik met een macro naar een nieuwe regel.
Perl, Python of VBA zijn helaas 6 stappen te ver voor mij. Het lijkt er op dat er geen handige oplossing is helaas, dan moet het maar handmatig. Thanks voor je input.

[Archie_T]

Split text into different columns with functions - Office Support
Ik heb niet het hele document doorgelezen maar volgens mij helpt dit je wel wat op weg?

[dix-neuf]

Jimster schreef op zaterdag 22 mei 2021 @ 08:43:
In het CSV bestand zitten geen newline characters. De producten staan daar achter elkaar met een paar spaties ertussen.

Zijn die 'paar' spaties er altijd twee? Indien ja, dan kun je m.i. voor een oplossing beter dáár van uitgaan (van het csv-bestand) dan van jouw voorbeeld waar wel 'newlines' in zitten. Principe: je doorloopt in elke cel in kolom A de tekst tot je 2 spaties tegenkomt en plaatst het gedeelte er vóór (en na het eerste woord het gedeelte tussen twee spaties) in kolom B of C. Maar als je liever de woorden splitst op " / " (spatie/spatie), wat je nu al hebt, dan kan dat ook. In beide gevallen kan dat (in vba bv.) met de MID-functie.

[ Voor 9% gewijzigd door dix-neuf op 24-05-2021 09:37 ]

[dix-neuf]

@Jimster,
Is het aantal woorden (zoals in je voorbeeld) altijd een even aantal?

[HvanL]

Eerst inlezen in google spreadsheet, die heeft de functie split vb: =split(A2;" / ")

[g0tanks]

HvanL schreef op dinsdag 25 mei 2021 @ 10:26:
Eerst inlezen in google spreadsheet, die heeft de functie split vb: =split(A2;" / ")

In Google Sheets is splitten inderdaad iets makkelijker dan in Excel, maar ik zie nog niet hoe het een oplossing voor dit probleem is. De complexiteit hier is dat je links en rechts wil scheiden, maar dat beide delen bestaan uit meerdere regels in één cel.

Archie_T schreef op zaterdag 22 mei 2021 @ 09:04:
Split text into different columns with functions - Office Support
Ik heb niet het hele document doorgelezen maar volgens mij helpt dit je wel wat op weg?

Zelfde opmerking geldt hiervoor. Er is net iets meer nodig dan de basis als het aankomt op splitten.

[ Voor 36% gewijzigd door g0tanks op 25-05-2021 10:54 ]

[HvanL]

Hoeveel regels? Bij 1 regel is het nog te doen: =split(SUBSTITUTE(A2;CHAR(10);" / ";1);" / ")

Moet zijn: =split(SUBSTITUTE(A2;CHAR(10);" / ");" / ") dan maakt aantal regels niet meer uit

[ Voor 34% gewijzigd door HvanL op 25-05-2021 11:23 ]

[g0tanks]

HvanL schreef op dinsdag 25 mei 2021 @ 11:18:
Hoeveel regels? Bij 1 regel is het nog te doen: =split(SUBSTITUTE(A2;CHAR(10);" / ";1);" / ")

Jouw voorbeeld snap ik niet helemaal. Waarom is CHAR(10) (een nieuwe regel) relevant als het maar om één regel gaat?

In ieder geval gaat het niet voldoende zijn: het voorbeeld in de openingspost heeft al 3 regels en een paar posts verder wordt genoemd dat het er maximaal 25 kunnen zijn.

[dix-neuf]

Ik heb een macro geprogrammeerd (vandaar mijn voorgaande vraag), zo goed als klaar, maar de vragensteller lijkt in wat voor oplossing dan ook, niet meer geïnteresseerd.

[ Voor 10% gewijzigd door dix-neuf op 25-05-2021 11:25 ]

[HvanL]

g0tanks schreef op dinsdag 25 mei 2021 @ 11:23:
[...]


Jouw voorbeeld snap ik niet helemaal. Waarom is CHAR(10) (een nieuwe regel) relevant als het maar om één regel gaat?

In ieder geval gaat het niet voldoende zijn: het voorbeeld in de openingspost heeft al 3 regels en een paar posts verder wordt genoemd dat het er maximaal 25 kunnen zijn.

Hiermee worde de regels omgezet naat 1 cel met alle tekst achter elkaar gescheiden door " / ", en dan werkt split goed

[g0tanks]

HvanL schreef op dinsdag 25 mei 2021 @ 11:25:
[...]


Hiermee worde de regels omgezet naat 1 cel met alle tekst achter elkaar gescheiden door " / ", en dan werkt split goed

Lees even het voorbeeld in de openingspost nog een keer, want dat is niet de oplossing waar @Jimster naar opzoek is.

[HvanL]

Dan snap ik het probleem niet, dit ziet er hetzelfde uit toch: https://docs.google.com/s...nHpsur5s/edit?usp=sharing

[The Eagle]

Wat ik in zo'n geval meestal doe is de boel even naar notepad++ kopieren en dan de spatie/spatie search en replace all door het standaard gebruikte scheidingsteken. Vervolgens import in excel vanuit csv en dan heb je mooi twee kolommen ipv 1.

[g0tanks]

HvanL schreef op dinsdag 25 mei 2021 @ 11:27:
Dan snap ik het probleem niet, dit ziet er hetzelfde uit toch: https://docs.google.com/s...nHpsur5s/edit?usp=sharing

Ik zie dat je ook CONCATENATE() hebt toegevoegd, dan ben ik het met je eens dat je er bent. Wel nog even uitbreiden voor de bestelling met 25 items natuurlijk.

The Eagle schreef op dinsdag 25 mei 2021 @ 12:27:
Wat ik in zo'n geval meestal doe is de boel even naar notepad++ kopieren en dan de spatie/spatie search en replace all door het standaard gebruikte scheidingsteken. Vervolgens import in excel vanuit csv en dan heb je mooi twee kolommen ipv 1.

Volgens mij gaat dat hier niet werken. Regel 2 in het orginele voorbeeld wordt dan opgesplitst in de volgende kolommen:

Bananen

Bananen Oranges

Sinaasappels Apples

Appels

[HvanL]

g0tanks schreef op dinsdag 25 mei 2021 @ 12:41:
[...]


Ik zie dat je ook CONCATENATE() hebt toegevoegd, dan ben ik het met je eens dat je er bent. Wel nog even uitbreiden voor de bestelling met 25 items natuurlijk.

Topicstarter kan zelf 25 kopieren , heb hem nog even aangepast met textjoin, alleen gevulde cellen opnemen

[Jimster]

dix-neuf schreef op dinsdag 25 mei 2021 @ 11:24:
Ik heb een macro geprogrammeerd (vandaar mijn voorgaande vraag), zo goed als klaar, maar de vragensteller lijkt in wat voor oplossing dan ook, niet meer geïnteresseerd.

Thanks, heb even geen tijd gehad om te reageren.
Aantallen kunnen verschillen, het kunnen er tot 25 zijn.

HvanL schreef op dinsdag 25 mei 2021 @ 11:27:
Dan snap ik het probleem niet, dit ziet er hetzelfde uit toch: https://docs.google.com/s...nHpsur5s/edit?usp=sharing

Thanks, ik ga er morgen even naar kijken.

[The Eagle]

g0tanks schreef op dinsdag 25 mei 2021 @ 12:41:
[...]


Ik zie dat je ook CONCATENATE() hebt toegevoegd, dan ben ik het met je eens dat je er bent. Wel nog even uitbreiden voor de bestelling met 25 items natuurlijk.


[...]


Volgens mij gaat dat hier niet werken. Regel 2 in het orginele voorbeeld wordt dan opgesplitst in de volgende kolommen:

Bananen

Bananen Oranges

Sinaasappels Apples

Appels

Da's een kwestie van de juiste delimiter(s) gebruiken. Notepad++ kan ook gewoon carriage returns vervangen. Je moet er wellicht wat mee spelen, en het zal misschien niet in 1 keer kunnen. Maar het moet kunnen

[HvanL]

Voorruit dan spreadsheet aangepast naar max 25 producten, ff testregel toegevoegd

[dix-neuf]

Als:
- Het blad waarin de gegevens staan "Blad1" heet;
- De gegevens in dat blad staan in kolom A, beginnend in A2;
- De gegevens er uitzien zoals in het eerste bericht van dit topic;
kun je onderstaande macro gebruiken.

code:

Sub macro1()
Dim a As Integer, k As Integer, x As Integer, y As Integer, L As Integer
With Sheets("Blad1")
.Columns("B:C").ClearContents
For y = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
a = 1: k = 1: L = Len(.Cells(y, 1))
If L > 0 Then
For x = 1 To L
If Mid(.Cells(y, 1), x, 3) = " / " Then
k = k + 1
.Cells(y, k) = .Cells(y, k) & Mid(.Cells(y, 1), a, x - a) & Chr(10)
x = x + 3: a = x
Do Until Mid(.Cells(y, 1), x, 1) = Chr(10) Or x = L + 1
x = x + 1
Loop
k = k + 1
.Cells(y, k) = .Cells(y, k) & Mid(.Cells(y, 1), a, x - a) & Chr(10)
If k = 3 Then k = 1
x = x + 1: a = x
End If
Next x
End If
If x - a > 1 Then
.Cells(y, 2) = .Cells(y, 2) & Mid(.Cells(y, 1), a, L + 1)
End If
Next y
End With
End Sub

[ Voor 6% gewijzigd door dix-neuf op 25-05-2021 17:27 ]

[Jimster]

dix-neuf schreef op dinsdag 25 mei 2021 @ 17:22:
Als:
- Het blad waarin de gegevens staan "Blad1" heet;
- De gegevens in dat blad staan in kolom A, beginnend in A2;
- De gegevens er uitzien zoals in het eerste bericht van dit topic;
kun je onderstaande macro gebruiken.

code:

Sub macro1()
Dim a As Integer, k As Integer, x As Integer, y As Integer, L As Integer
With Sheets("Blad1")
.Columns("B:C").ClearContents
For y = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
a = 1: k = 1: L = Len(.Cells(y, 1))
If L > 0 Then
For x = 1 To L
If Mid(.Cells(y, 1), x, 3) = " / " Then
k = k + 1
.Cells(y, k) = .Cells(y, k) & Mid(.Cells(y, 1), a, x - a) & Chr(10)
x = x + 3: a = x
Do Until Mid(.Cells(y, 1), x, 1) = Chr(10) Or x = L + 1
x = x + 1
Loop
k = k + 1
.Cells(y, k) = .Cells(y, k) & Mid(.Cells(y, 1), a, x - a) & Chr(10)
If k = 3 Then k = 1
x = x + 1: a = x
End If
Next x
End If
If x - a > 1 Then
.Cells(y, 2) = .Cells(y, 2) & Mid(.Cells(y, 1), a, L + 1)
End If
Next y
End With
End Sub

Thanks! Lijkt te werken. Nog even finetunen. Mijn vrouw is je dankbaar

[dix-neuf]

Wát moet er nog gefinetuned worden?

[Jimster]

dix-neuf schreef op woensdag 26 mei 2021 @ 18:33:
Wát moet er nog gefinetuned worden?

Kleine aanpassingen zoals Blad1 en beginnen bij A2. Maar dat is niet meer nodig, ik pas de werkinstructie wel aan
Bedankt