For nylig diskuterede vi brugen af funktionen FILTER.XML til at importere XML-data fra internettet – hovedopgaven, som denne funktion i virkeligheden er beregnet til. Undervejs er der dog dukket en anden uventet og smuk brug af denne funktion op – til hurtigt at opdele klæbrig tekst i celler.
Lad os sige, at vi har en datakolonne som denne:
For nemheds skyld vil jeg selvfølgelig gerne opdele det i separate kolonner: firmanavn, by, gade, hus. Du kan gøre dette på en række forskellige måder:
- Brug Tekst efter kolonner fra fanen data (Data — Tekst til kolonner) og gå tre trin Tekstfortolker. Men hvis dataene ændres i morgen, bliver du nødt til at gentage hele processen igen.
- Indlæs disse data i Power Query og del dem der, og upload dem derefter tilbage til arket, og opdater derefter forespørgslen, når dataene ændres (hvilket allerede er nemmere).
- Hvis du har brug for at opdatere i farten, så kan du skrive nogle meget komplekse formler for at finde kommaer og udtrække teksten imellem dem.
Og du kan gøre det mere elegant og bruge funktionen FILTER.XML, men hvad har det med det at gøre?
FILTER.XML-funktionen modtager som sit indledende argument en XML-kode - tekst markeret med specielle tags og attributter og parser den derefter ind i dens komponenter og udtrækker de datafragmenter, vi har brug for. XML-koden ser normalt sådan ud:
I XML skal hvert dataelement være indesluttet i tags. Et tag er noget tekst (i eksemplet ovenfor er det manager, navn, profit) omgivet af vinkelparenteser. Tags kommer altid i par – åbner og lukker (med en skråstreg tilføjet i begyndelsen).
FILTER.XML-funktionen kan nemt udtrække indholdet af alle de tags, vi har brug for, for eksempel navnene på alle ledere, og (vigtigst) vise dem alle på én gang på én liste. Så vores opgave er at tilføje tags til kildeteksten, forvandle den til XML-kode, der er egnet til efterfølgende analyse med funktionen FILTER.XML.
Hvis vi tager den første adresse fra vores liste som et eksempel, bliver vi nødt til at omdanne den til denne konstruktion:
Jeg kaldte den globale åbning og lukning af al tekst-tag t, og de tags, der indrammer hvert element er s., men du kan bruge alle andre betegnelser – det er lige meget.
Hvis vi fjerner indrykning og linjeskift fra denne kode - helt, forresten, valgfri og kun tilføjet for klarhedens skyld, så bliver alt dette til en linje:
Og det kan allerede relativt nemt fås fra kildeadressen ved at erstatte kommaer i den med et par tags ved hjælp af funktionen ERSTATNING (ERSTATNING) og limning med symbolet & i begyndelsen og slutningen af de indledende og afsluttende tags:
For at udvide det resulterende område horisontalt bruger vi standardfunktionen TRANSP (TRANSPOSERER)pakker vores formel ind:
Et vigtigt træk ved hele dette design er, at i den nye version af Office 2021 og Office 365 med understøttelse af dynamiske arrays, kræves der ingen specielle bevægelser til input - bare indtast og klik på Indtast – selve formlen fylder det antal celler, den har brug for, og alt fungerer med et brag. I tidligere versioner, hvor der endnu ikke var nogen dynamiske arrays, skal du først vælge et tilstrækkeligt antal tomme celler, før du indtaster formlen (du kan med en margen), og efter at have oprettet formlen skal du trykke på tastaturgenvejen Ctrl+Flytte+Indtastfor at indtaste det som en matrixformel.
Et lignende trick kan bruges, når du adskiller tekst, der er sat sammen i én celle gennem et linjeskift:
Den eneste forskel med det foregående eksempel er, at i stedet for et komma erstatter vi her det usynlige Alt + Enter linjeskift, som kan specificeres i formlen ved hjælp af CHAR-funktionen med kode 10.
- Finesserne ved at arbejde med linjeskift (Alt + Enter) i Excel
- Opdel tekst efter kolonner i Excel
- Erstatter tekst med SUBSTITUTE