tekst splitsen, komma gescheiden verschillend aantal woorden

[2WW]

Voor een sportschool houden wij bij wie aanwezig zijn bij de trainingen. De app geeft in Excel - in 1 cel - de namen kommagescheiden weer. De ene keer zijn er 6 mensen, de andere keer 10, 12 etc.

1) Is het mogelijk om middels een formule de aanwezigen over meerdere kolommen te scheiden?
2) Kan de formule het ook aan dat deze het resultaat verticaal weergeeft?

Ik kan bovenstaand oplossen in Google Sheet, de formule is dan: TRANSPOSE(split('Form responses 1'!$C2,",")), maar ben benieuwd of dat ook in Excel kan middels een formule (feitelijk optie 'tekst naar kolommen' in een formule).

Wouter

 

[Rubenio]

Hallo Wouter,

Om e.e.a. even makkelijker te maken heb ik zelf een voorbeeld bestand gemaakt met 4 namen.

Je ziet in cel B2 4 namen gescheiden door een komma.Ik ga ervan uit dat dit gelijk is aan hoe jij het aangeleverd krijgt.

Om nu te komen tot het resultaat dat jij wilt doe ik het volgende:

1. selecteer cel B2 en ga naar tabblad gegevens en klik op tekst naar kolommen
2. Kies voor "gescheiden" i.v.m. komma's en klik op volgende
3. Vink komma aan en klik op volgende en dan op voltooien
4. je ziet nu dat in cel B2 tot E4 de 4 namen zijn opgedeeld over de kolommen.

5. Om dit vervolgens verticaal weer te geven kijk ik eerst om hoeveel kolommen het gaat, je kan dit doen door ze te selecteren en linksboven te kijken naar omvang bereik. In dit geval zijn het er dus 4.
6. Ik selecteer het verticale bereik waar ik de namen wil hebben, ik kies voor B5 tot B8, en selecteer deze.
7. Ik type in =transponeren(B2:E2) en druk op Ctrl + Shift + Enter

Mocht je dit nu sneller willen of automatiseren dan is het denk ik het beste om even een realistisch voorbeeldbestand te uploaden zodat iedereen weet weer alles staat en waar je het wilt hebben.

Succes.

Groet,
Ruben

 

[Rubenio]

Bedenk mezelf net dat het misschien nog wel gemakkelijker kan met formule LINKS + VIND.SPEC en dan telkens tot de volgende komma.
Weet alleen zelf niet precies hoe dit te doen.

 

[2WW]

Hoi Ruben,

Dank voor jouw snelle antwoord? 'Tekst naar kolommen' ken ik en betekent dus klikken wat ik eigenlijk niet wil. Zonder in VBA te moeten duiken (als dat al lukt), zoek ik daarvoor naar een formule. In Google Sheets is dat de genoemde 'split' functie.

Het tweede deel van je antwoord (transponeren in formule), die kende ik nog niet, maar zo'n tussenstap is prima.

Het voorbeeldbestand is zo eenvoudig als je het stelt:
(cel A1) naam a, naam c, naam f
(cel A2) naam a, naam b, naam d, naam f, naam h
(cel A3) naam b, naam d, naam e, naam k, naam l, naam m, naam m, naam w
(cel A4) naam d, naam k, naam m

Wouter

 

[VenA]

Volgens mij kan het niet met een formule. Wel met een aantal hulpkolommen. Het splitsen kan je dan doen met een combinatie van SUBSTITUTE() en MID()

De app geeft in Excel - in 1 cel - de namen kommagescheiden weer

Dit blijkt niet uit je tweede reactie

Dus een voorbeeldbestand is welkom

Met VBA obv #1

Sub VenA()
  [c1].Resize(UBound(Split([c1], ",")) + 1) = Application.Transpose(Split([c1], ","))
End Sub

 

[2WW]

Hoi VenA,

Wil niet bij de hand zijn, maar volgens mij staat in het onderwerp 'komma gescheiden'.

Daar in niet in VBA wil duiken, voor mij te onbekende terrein, zal ik de oplossing dus zoeken in Google Sheets door met - TRANSPOSE(split('Form responses 1'!$C2,",")) - het exportbestand naar Excel voor te bereiden, waardoor ik de vraagstelling in Excel dus vermijd.

Ik houd er alleen niet van om met verschillende documenten te werken, vandaar mijn vraag.

Anyway, dank voor jouw antwoord en tijd. Ik laat de vraag nog even open staat. Wie weet brengt iemand ons nog op een idee.

Wouter

 

[2WW]

Ik Google Sheets los ik het nu als volgt op, waarna ik wel makkelijk de export/import kan doen:

=transpose(split(SUBSTITUTE(C2;", ";",");","))

Vervangt de 'komma_spatie' door 'komma' (zonder een spatie daarna)
Splitst de namen in de cel op de 'komma'
Transponeert de gecreëerde rij in een kolom

Dank voor het meedenken, laat de vraag nog even open staan.

Wouter

 

[keesbl]

Begin je steeds met een nieuw werkblad?
Dan zou je een macro kunnen opnemen met de macrorecorder en de stappen die je doet doorlopen, je hoeft dan niet steeds te klikken.
Als je een bestaand werkblad steeds onderaan aanvult met nieuwe gegevens, is eventueel wel wat te maken met VBA. Dan heb je niet met verschillende bestanden te maken

Kees

 

[2WW]

Hoi Kees,

Macrorecorder is inderdaad ook een oplossing. Heb inmiddels het Google Sheet aangepast (data wordt automatisch gevuld vanuit Google Form), eerder genoemde formule in een tweede sheet waardoor nu alles in kolommen staat. Kop is datum met daaronder aanwezigen. Zodra ik de administratie doe, export ik Google Sheet als Excel, open dit, kopieer data in een keer in het juiste werkblad. Dit voedt weer de andere bladen waar nodig om aanwezigheid in grafiekjes vast te leggen en eventuele 'rittenkaarten' af te boeken. Het loopt nu als een trein. Zojuist wat tests uitgevoerd.

Wouter