Dela upp Text och Tal från celler i Excel och Power Query

Det finns många olika situationer där du kan behöva separera eller bryta ut olika delar från celler till en eller flera andra celler. I Excel finns många olika funktioner att ta hjälp av för att extrahera ut delar från en cell, t.ex. HÖGER, VÄNSTER, EXTEXT, LÄNGD, SÖK, HITTA, BYT.UT, ERSÄTT, TEXT, TEXTNUM m.fl.

Ibland är kan det vara enkla – eller relativt enkla – lösningar som t.ex. att hämta årtalet från cell A1 som har cellvärdet ”18/10/2019”, genom att ta =HÖGER(A1;4) eller att hämta det första ordet i en cell med =VÄNSTER(B1;HITTA(” ”;B1;1)-1).

Logiken kan dock ganska snabbt blir komplicerad och väldigt långa formler kan behövas för att lösa relativt enkla problemställningar. Ibland kan Power Query vara en snabb och effektiv väg runt hardcore-formelknackande. Nedan visar vi två Excelösningar och en Power Query-lösning på ett problem…vilken väljer du? 😉

Power Query är numera inbyggt i Excel och du hittar verktyget i menyfliken Data [Data] och gruppen Hämta och transformera data [Get and Transform Data]. På www.officekurs.se hittar du en pedagogisk och omfattande e-kurs i Power Query. På www.infocell.se hittar du vår lärarledda tvådagarskurs i de olika Power-programmen som gör dig redo för ett nytt (mer effektivt) sätt att använda Excel 😊.

I de blå tabellerna nedan har vi celler som innehåller både en ”textdel” och en ”taldel”. Nu skulle vi vilja separera texten i en cell/kolumn och talet i en annan cell/kolumn.

Häng med genom att ladda ner filen (inklusive facit) här.

Lösning med formler i Excel – den hårda långa vägen

I Excel blir detta mycket komplicerat. För att extrahera ut textdelen från den översta blå tabellen behöver vi använda flera olika funktioner tillsammans och dessutom använda oss av en talmatris med möjliga numeriska värden. Formeln blir enligt följande:

=VÄNSTER([@[Text&Tal]];MIN(OMFEL(HITTA({0;1;2;3;4;5;6;7;8;9};[@[Text&Tal]]);””))-1)

=LEFT([@[Text&Tal]];MIN(IFERROR(FIND({0;1;2;3;4;5;6;7;8;9};[@[Text&Tal]]);””))-1)

(Notera att vi använder Exceltabeller i exemplet, vilket ger oss ”strukturerade cellreferenser” som [@[Text&Tal]] istället för B3).

Därefter kan vi använda funktionen BYT.UT [SUBSTITUE] för att klara av ”taldelen” med hjälp av följande funktion:

=BYT.UT([@[Text&Tal]];[@Text];””)

=SUBSTITUTE([@[Text&Tal]];[@Text];””)

Nästa tabell – där cellerna inleds med tal och avslutas med en textdel – blir nog ännu jobbigare formelmässigt 😣.

För att lösa ut ”taldelen” använder vi följande formel:

=VÄNSTER([@[Tal&Text]];PASSA(”*~”;OMFEL(TEXTNUM(EXTEXT([@[Tal&Text]];
RAD(INDIREKT(”1:”&LÄNGD([@[Tal&Text]])));1));”*~”);)-1)

=LEFT([@[Tal&Text]];MATCH(”*~”;IFERROR(VALUE(MID([@[Tal&Text]];
ROW(INDIRECT(”1:”&LEN([@[Tal&Text]])));1));”*~”);)-1)

Ovanstående formel är nog endast de mest hängivna formelnördarna i Excel som fixar skulle jag gissa 😉.

När man har löst ”taldelen”, kan man i och för sig snabbt lösa ut textdelen med:

=BYT.UT([@[Tal&Text]];[@Tal];””)

=SUBSTITUTE([@[Tal&Text]];[@Tal];””)

Vill du ha en enklare lösning utan att behöva använda formlerna ovan?

Ja, säg den som inte vill det. Läs vidare, så ser du en snabb och busenkel lösning.

Lösning med Snabbfyllning i Excel – den snabba (men inte alltid gångbara) vägen

Ovanstående problem går faktiskt utmärkt att lösa med hjälp av Snabbfyllning [Flash Fill] i Excel.

Lägg till kolumnen Text och skriv in den första texten manuellt (”AXTB”). Tryck sedan Enter och börja skriv ”G”. Excel skickar då fram ett förslag på inmatning i övriga celler i kolumnen. Ser allt rätt ut, trycker du bara Enter, och programmet autofyller texten i kolumnen.

OBS! Notera dock att denna lösning inte är bra i de fall då data kan förändras. Snabbfyllning sker momentant – en gång – och fungerar inte som formler. Ändrar du data i kolumnen Text&Tal/Tal&Text kommer förändringen INTE att slå igenom i kolumnerna Text/Tal när du använder Snabbfyllning. Samma problem får du om du lägger till nya rader i tabellen – dessa hänger inte med automatiskt om du använder Snabbfyllning. Vid ad-hoc-arbeten och vid tillfällen när data inte ändras eller läggs till är Snabbfyllning det smartaste och snabbaste sättet att lösa problemet.

Lösning med Power Query – den snabba (och alltid gångbara) vägen

Du löser det med Power Query på följande sätt:

1. Ställ dig i den övre blå tabellen.
2. Klicka på menyfliken Data [Data] och knappen Från Tabell/intervall [From Table/Range].
3. När tilläggsprogrammet öppnas, klickar du på menyfliken Lägg till kolumn [Add Column] och väljer Duplicera kolumn [Duplicate Column].
4. Se till att den sista kolumnen är markerad (grön). Gå till menyfliken Transformera [Transform] och välj Dela upp kolumn [Split Column] och därefter Efter icke-siffra till siffra [By non-Digit to Digit].
   
5. Därefter har du jobbet klart 😊. För att skjuta ut informationen i Excel väljer du Start [Home] och Stäng och läs in [Close and Load]. Vill du placera tabellen bredvid den andra tabellen väljer du Stäng och läs in till… [Close and Load to…]
   

Snyggt och enkelt. Power Query rockar fett i många, många lägen.

Dessutom räcker det att högerklicka (i den gröna tabellen) och välja Uppdatera [Refresh] om du skulle få förändrad eller nya data i tabellen till vänster 😊.

Lösningen för den nedre blå tabellen är lika enkel som för tabellen ovan. Du följer samma steg, men väljer Efter siffra till icke-siffra [By Digit to non-Digit] under knappen Dela upp kolumn [Split Column] istället.

Associerad e-kurs: Power Query via officekurs.se

Associerad lärarledd kurs: Power Pivot & Power BI

Kontakta oss på 0522-506007 eller utbildning@infocell.se om ni vill ha en lärarledd anpassad kurs. Till alla lärarledda kurser ingår e-kurser.