På senare tid diskuterade vi användningen av funktionen FILTER.XML för att importera XML-data från Internet – huvuduppgiften som denna funktion i själva verket är avsedd för. Längs vägen har dock en annan oväntad och vacker användning av denna funktion dykt upp – för att snabbt dela upp klibbig text i celler.
Låt oss säga att vi har en datakolumn så här:
Naturligtvis, för enkelhetens skull, skulle jag vilja dela upp det i separata kolumner: företagsnamn, stad, gata, hus. Du kan göra detta på en massa olika sätt:
- Använda Text efter kolumner från fliken Data (Data — text till kolumner) och gå tre steg Texttolkare. Men om uppgifterna ändras i morgon måste du upprepa hela processen igen.
- Ladda in denna data i Power Query och dela den där och ladda sedan upp den tillbaka till arket och uppdatera sedan frågan när data ändras (vilket redan är lättare).
- Om du behöver uppdatera i farten kan du skriva några mycket komplexa formler för att hitta kommatecken och extrahera texten mellan dem.
Och du kan göra det mer elegant och använda funktionen FILTER.XML, men vad har det med det att göra?
FILTER.XML-funktionen tar emot som sitt initiala argument en XML-kod — text som är markerad med speciella taggar och attribut, och analyserar den sedan till dess komponenter och extraherar de datafragment vi behöver. XML-koden ser vanligtvis ut ungefär så här:
I XML måste varje dataelement vara inneslutet i taggar. En tagg är viss text (i exemplet ovan är det manager, namn, vinst) omgiven av vinkelparenteser. Taggar kommer alltid i par – öppning och stängning (med ett snedstreck lagt till i början).
FILTER.XML-funktionen kan enkelt extrahera innehållet i alla taggar vi behöver, till exempel namnen på alla chefer, och (viktigast) visa dem alla på en gång i en lista. Så vår uppgift är att lägga till taggar till källtexten och omvandla den till XML-kod som lämpar sig för efterföljande analys med funktionen FILTER.XML.
Om vi tar den första adressen från vår lista som ett exempel, måste vi förvandla den till denna konstruktion:
Jag ringde den globala öppnings- och stängningstaggen för all text t, och taggarna som ramar in varje element är s., men du kan använda vilka andra beteckningar som helst – det spelar ingen roll.
Om vi tar bort indrag och radbrytningar från den här koden – helt, förresten, valfritt och endast tillagt för tydlighetens skull, kommer allt detta att förvandlas till en rad:
Och det kan redan relativt enkelt erhållas från källadressen genom att ersätta kommatecken i den med ett par taggar med funktionen ERSÄTTNING (ERSÄTTNING) och limma med symbolen & i början och slutet av inledande och avslutande taggar:
För att utöka det resulterande området horisontellt använder vi standardfunktionen ÖVERFÖRING (ÖVERFÖRA), med vår formel:
En viktig egenskap i hela den här designen är att i den nya versionen av Office 2021 och Office 365 med stöd för dynamiska arrayer krävs inga speciella gester för inmatning – bara in och klicka på ange – själva formeln upptar det antal celler den behöver och allt fungerar med en smäll. I tidigare versioner, där det inte fanns några dynamiska arrayer ännu, måste du först välja ett tillräckligt antal tomma celler innan du anger formeln (du kan med en marginal), och efter att ha skapat formeln, tryck på kortkommandot ctrl+shift+angeför att ange den som en matrisformel.
Ett liknande knep kan användas när man separerar text som sitter ihop i en cell genom en radbrytning:
Den enda skillnaden mot föregående exempel är att istället för ett kommatecken, ersätter vi här det osynliga Alt + Enter radbrytningstecknet, som kan specificeras i formeln med hjälp av CHAR-funktionen med kod 10.
- Finheterna med att arbeta med radbrytningar (Alt + Enter) i Excel
- Dela text efter kolumner i Excel
- Ersätter text med SUBSTITUTE