Rensa bort radbrytningar, dolda tecken och extra mellanslag i Excel

Du klistrar in en kundlista från en webbsida, öppnar en export från affärssystemet eller kopierar en tabell ur en PDF. Allt ser rätt ut. Ändå hittar LETARAD inte artikelnumret som uppenbart finns i listan, två namn som ser identiska ut räknas som olika och sorteringen lägger vissa rader på konstiga ställen.

Orsaken är nästan alltid tecken du inte ser: radbrytningar mitt i cellen, osynliga styrtecken, hårda mellanslag från webben och extra mellanslag före, efter eller mellan orden. För Excel är ”Andersson ” och ”Andersson” två helt olika värden.

I den här artikeln går vi igenom hur du hittar och tar bort de här tecknen med formlerna STÄDA (TRIM), RENSA (CLEAN) och BYT.UT (SUBSTITUTE). Du får också en färdig formel som rensar allt på en gång och ett snabbtips för att städa direkt med Sök och ersätt, helt utan formler. Vill du först ha en översikt över textfunktionerna kan du läsa om TRIM och andra texthanteringsformler.

Varför importerad text blir rörig

Text som klistras in från en webbsida, en PDF eller en export från ett affärssystem ser ofta helt normal ut i cellen. Problemet är det du inte ser. I texten finns tecken som Excel räknar som en del av innehållet, och därför blir "Skrivbord " något annat än "Skrivbord". Formler som jämför eller slår upp värden hittar då ingen träff.

De vanligaste bovarna

• Radmatning (tecken 10) är det tecken Excel använder för radbrytning inne i en cell, samma tecken som Alt+Enter skapar. Det följer ofta med när du kopierar adresser, beskrivningar eller fritextfält.
• Vagnretur (tecken 13) kommer från system och textfiler där en radbrytning skrivs som två tecken, 13 följt av 10. Ofta blir vagnreturen kvar och syns som ingenting alls.
• Hårt mellanslag (tecken 160) ser ut som ett vanligt mellanslag men är ett annat tecken. Det är mycket vanligt i text som kopieras från webbsidor, där det används för att hålla ihop ord på samma rad.
• Inledande, avslutande eller dubbla mellanslag är vanliga mellanslag (tecken 32) som hamnat på fel ställe, till exempel när ett system fyller ut fält till en fast bredd.

Så hittar du de dolda tecknen

Två funktioner räcker för att avslöja det mesta. LÄNGD (LEN) räknar alla tecken i cellen, även de osynliga. Är längden större än antalet tecken du ser finns det något extra i cellen.

KOD (CODE) visar teckenkoden för det första tecknet i en text. Kombinera den med VÄNSTER eller HÖGER för att undersöka början och slutet av cellen:

• =KOD(VÄNSTER(A2;1)) ger koden för det första tecknet.
• =KOD(HÖGER(A2;1)) ger koden för det sista tecknet.

I exemplet nedan ser alla celler i kolumn A ut att innehålla ordet Skrivbord, men bara den första är ren. Den har längden 9, medan de övriga har 10 eller 11 tecken. KOD avslöjar vad som ligger i ändarna: 32 är ett vanligt mellanslag, 160 ett hårt mellanslag, 10 en radmatning och 13 en vagnretur. Kolumn E visar följden: ingen av de smutsiga cellerna räknas som lika med A2.

Lägg märke till rad 4. Där ligger två mellanslag mitt i ordet, så KOD för första och sista tecknet ser normala ut. Bara LÄNGD visar att något är fel. Kontrollera därför alltid längden först och använd KOD för att ta reda på vilket tecken det handlar om.

Skillnaden mellan STÄDA (TRIM) och RENSA (CLEAN)

De två funktioner som oftast nämns när text ska städas är STÄDA (engelska TRIM) och RENSA (engelska CLEAN). Namnen låter som om de gör samma sak, men de tar hand om helt olika problem. Därför räcker sällan den ena ensam.

STÄDA (TRIM) tar hand om mellanslagen

=STÄDA(A2) gör tre saker med texten i A2:

• tar bort alla mellanslag i början av texten
• tar bort alla mellanslag i slutet av texten
• gör två eller fler mellanslag i följd inne i texten till ett enda

STÄDA känner bara igen det vanliga mellanslaget, tecken 32. Radbrytningar och andra styrtecken lämnas kvar. Det hårda mellanslaget (tecken 160) räknas inte heller som ett mellanslag och blir orört.

RENSA (CLEAN) tar bort osynliga styrtecken

=RENSA(A2) tar bort icke utskrivbara tecken, alltså styrtecknen med kod 0–31. Hit hör radmatning (10) och vagnretur (13), men också andra kontrolltecken som ibland följer med från äldre system. Vanliga mellanslag rör RENSA inte, och tecken 160 ligger utanför intervallet 0–31. Det tar alltså inte heller RENSA bort.

Observera att RENSA tar bort radbrytningen helt utan att sätta något i stället. Står det ”Kalle” på en rad och ”Anka” på nästa blir resultatet ”KalleAnka”. Vill du ha ett mellanslag mellan orden behöver du BYT.UT, som nästa avsnitt visar.

I exemplet nedan har samma namn importerats på tre olika sätt. Med =LÄNGD() före och efter syns vad varje funktion faktiskt gör. STÄDA kortar ner rad 2 från 15 till 10 tecken men gör inget åt radbrytningen på rad 3. RENSA tar bort radbrytningen på rad 3 men lämnar mellanslagen på rad 2 orörda. På rad 4 tar STÄDA bort det avslutande mellanslaget, men det hårda mellanslaget mellan orden finns kvar efter båda funktionerna.

När ska du använda vilken?

Problem i texten	STÄDA (TRIM)	RENSA (CLEAN)
Mellanslag i början eller slutet	Ja	Nej
Dubbla mellanslag inne i texten	Ja, blir enkla	Nej
Radbrytning (tecken 10 och 13)	Nej	Ja, tas bort helt
Andra styrtecken (kod 0–31)	Nej	Ja
Hårt mellanslag (tecken 160)	Nej	Nej

Kort sagt: använd STÄDA när problemet är mellanslag och RENSA när texten innehåller osynliga styrtecken. Är du osäker kan du kombinera dem som =STÄDA(RENSA(A2)). Det hårda mellanslaget kräver ändå ett extra steg, och det tar vi upp längre fram.

Ta bort radbrytningar med BYT.UT

Den vanligaste orsaken till rörig importerad text är radbrytningar mitt i cellerna. Det händer ofta med adresser, produktbeskrivningar och kommentarsfält. Funktionen BYT.UT (engelska SUBSTITUTE) ersätter ett tecken eller en textbit med något annat. Med TECKEN(10) (engelska CHAR(10)) pekar du ut radbrytningen, alltså samma tecken som Excel lägger in när du trycker Alt+Enter i en cell.

Steg för steg

1. Skapa en ny kolumn bredvid den importerade texten. Originalet får stå kvar tills du är säker på resultatet.
2. Skriv formeln =BYT.UT(A2;TECKEN(10);" ") i första raden i den nya kolumnen. Den byter varje radbrytning i A2 mot ett mellanslag.
3. Dra ned formeln, eller dubbelklicka på fyllnadshandtaget, så att den gäller hela kolumnen.
4. Kontrollera att orden inte har klistrats ihop och att inga radbrytningar finns kvar.

I exemplet nedan har adresserna i kolumn A kommit in med en eller flera radbrytningar. Formeln i kolumn B lägger varje adress på en rad, med mellanslag där radbrytningarna satt.

Mellanslag eller ingenting?

Vill du ta bort radbrytningarna helt i stället för att ersätta dem skriver du två citattecken utan något emellan: =BYT.UT(A2;TECKEN(10);""). Det passar när radbrytningen har hamnat mitt i ett ord eller ett nummer, till exempel ett artikelnummer som delats på två rader. Gäller det vanlig text blir det oftast fel, eftersom ordet före och ordet efter radbrytningen smälter ihop. Välj mellanslag om du är osäker. Eventuella dubbla mellanslag kan du rensa bort i efterhand.

Text från Windows-system: TECKEN(13)

Text som exporteras från vissa affärssystem, databaser eller textfiler skapade i Windows avslutar raderna med två tecken: vagnretur (TECKEN(13)) följt av radmatning (TECKEN(10)). Om du bara byter ut tecken 10 blir tecken 13 kvar. Det syns sällan, men det gör att jämförelser och uppslag misslyckas.

Lösningen är att nästla två BYT.UT i varandra:

=BYT.UT(BYT.UT(A2;TECKEN(13);"");TECKEN(10);" ")

Den inre BYT.UT tar bort alla vagnreturer helt. Den yttre byter sedan radmatningarna mot mellanslag. Ordningen har betydelse: om du ersätter båda tecknen med mellanslag får du två mellanslag där det tidigare fanns en radbrytning.

Tips: slå av Radbryt text

Om cellen har formatet Radbryt text kan resultatet se ut att fortfarande ha flera rader, eftersom Excel radbryter långa texter efter kolumnbredden. Markera kolumnen och klicka på Radbryt text under Start > Justering så att knappen inte längre är aktiv. Ligger texten nu på en enda rad är radbrytningarna borta på riktigt.

Hantera hårda mellanslag (tecken 160)

Hårda mellanslag, tecken 160, är den vanligaste orsaken till att en cell fortfarande ser smutsig ut efter en städning. De följer ofta med när du kopierar tabeller från webbsidor, eftersom HTML-koden   blir just tecken 160 när den hamnar i Excel. På skärmen går det inte att skilja ett hårt mellanslag från ett vanligt. För Excel är det två olika tecken.

Varför STÄDA och RENSA missar tecknet

Båda funktionerna gör exakt det de är byggda för, och tecken 160 faller utanför bådas uppdrag:

• STÄDA (TRIM) letar bara efter vanliga mellanslag, tecken 32. Ett hårt mellanslag räknas inte som mellanslag och lämnas kvar, även om det står först eller sist i cellen.
• RENSA (CLEAN) tar bara bort styrtecknen med kod 0–31. Tecken 160 är ett utskrivbart tecken och berörs inte.

Det märks ofta på tal som har kopierats från en webbsida. 12 500 med ett hårt mellanslag som tusentalsavgränsare blir text i stället för ett tal. Det går inte att summera, och en LETARAD mot en kolumn med riktiga tal ger #SAKNAS.

Lösningen: byt först, städa sedan

Gör om de hårda mellanslagen till vanliga mellanslag med BYT.UT och låt STÄDA ta hand om resten:

=STÄDA(BYT.UT(A2;TECKEN(160);" "))

BYT.UT körs först och ersätter varje tecken 160 i A2 med ett vanligt mellanslag. Därefter tar STÄDA bort mellanslag i början och slutet och slår ihop dubbla mellanslag till ett. Ordningen är viktig. Om du skriver STÄDA innerst har den inget att arbeta med, eftersom de hårda mellanslagen ännu inte har bytts ut.

Ska tecknet bort helt, till exempel i tal där det fungerar som tusentalsavgränsare, ersätter du med en tom sträng i stället: =BYT.UT(A2;TECKEN(160);""). Om resultatet fortfarande är text kan du omvandla det till ett tal med =VÄRDE(BYT.UT(A2;TECKEN(160);"")).

Kontrollera med LÄNGD före och efter

Eftersom du inte ser skillnaden får du räkna dig fram. Lägg den städade formeln i B2 och jämför antalet tecken:

• =LÄNGD(A2) visar längden på originalet.
• =LÄNGD(B2) visar längden efter städningen.

Har längden minskat har formeln tagit bort något. Vill du veta exakt hur många hårda mellanslag cellen innehöll kan du använda den här formeln:

=LÄNGD(A2)-LÄNGD(BYT.UT(A2;TECKEN(160);""))

Formeln tar bort alla tecken 160 och räknar hur många tecken som försvann. Om den ger 0 för originalet finns det inga hårda mellanslag, och felet ligger någon annanstans. Använd då KOD på första eller sista tecknet för att se vad som faktiskt står där.

Om formeln inte hittar några hårda mellanslag trots att du är säker på att de finns, till exempel i Excel för Mac, kan du byta TECKEN(160) mot UNICODETECKEN(160). Den funktionen utgår alltid från Unicode-numret.

En formel som rensar allt på en gång

När du vet vilka tecken som ställer till det kan du samla alla rättelser i en enda formel. Den här tar hand om hårda mellanslag, radbrytningar, övriga styrtecken och extra mellanslag på en gång:

=STÄDA(RENSA(BYT.UT(BYT.UT(A2;TECKEN(160);" ");TECKEN(10);" ")))

På engelska heter samma formel =TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2;CHAR(160);" ");CHAR(10);" "))). Skriv den i en hjälpkolumn bredvid den röriga texten och fyll nedåt.

Så fungerar formeln, inifrån och ut

1. BYT.UT(A2;TECKEN(160);" ") byter varje hårt mellanslag mot ett vanligt mellanslag. Annars skulle STÄDA inte känna igen det.
2. BYT.UT(…;TECKEN(10);" ") byter radbrytningar mot mellanslag. Det här steget gör du före RENSA. RENSA tar visserligen också bort tecken 10, men då klistras orden ihop, till exempel ”KaffebryggareSvart”.
3. RENSA(…) tar bort de styrtecken som finns kvar, bland annat vagnretur (tecken 13) från text som har radbrytningar i Windows-format. Därför behöver du ingen egen BYT.UT för TECKEN(13) här.
4. STÄDA(…) städar sist bort mellanslag i början och slutet och gör dubbla mellanslag till enkla. Det gäller även de mellanslag som de tidigare stegen har lagt in.

I exemplet nedan innehåller kolumn A en blandning av alla problemen. Kolumn C och D visar längden före och efter med LÄNGD. Den första cellen krymper från 25 till 19 tecken och blir ”Kaffebryggare Svart”.

Ersätt originalkolumnen med den städade texten

Formlerna i hjälpkolumnen hänger ihop med originaltexten. Om du raderar kolumn A blir resultatet #REFERENS!. Gör så här för att behålla den rena texten:

1. Markera de städade cellerna i kolumn B och kopiera med Ctrl+C.
2. Markera den första cellen i kolumn A, alltså A2.
3. Högerklicka och välj Klistra in special > Värden. Du kan också trycka Ctrl+Alt+V, välja Värden och trycka Enter.
4. Kontrollera några celler. I formelfältet ska det nu stå ren text, inte en formel.
5. Ta bort hjälpkolumnen B.

Vill du hellre behålla originalet för kontroll kan du klistra in värdena i en ny kolumn och dölja eller ta bort den gamla när du är säker på att allt stämmer.

Snabbtips: Sök och ersätt för engångsstädning

Ska du bara städa en kolumn en gång behöver du inga formler. Sök och ersätt gör jobbet direkt i cellerna, och originaltexten skrivs över utan att du behöver hjälpkolumner eller klistra in värden.

Ta bort radbrytningar

1. Markera de celler eller den kolumn du vill städa. Markerar du bara en cell söker Excel i hela bladet.
2. Tryck Ctrl+H för att öppna dialogrutan Sök och ersätt.
3. Klicka i fältet Sök efter och tryck Ctrl+J. Det motsvarar radmatningstecknet, alltså tecken 10.
4. Skriv ett mellanslag i fältet Ersätt med om orden på varsin rad ska hållas isär. Lämna fältet tomt om radbrytningarna ska bort helt.
5. Klicka på Ersätt alla.

Ta bort hårda mellanslag

Gör på samma sätt, men klicka i fältet Sök efter, håll ned Alt och skriv 0160 på det numeriska tangentbordet. Släpp Alt, så hamnar ett hårt mellanslag i fältet. Ersätt det med ett vanligt mellanslag. Siffrorna i raden ovanför bokstäverna fungerar inte för det här, så på en bärbar dator utan numeriskt tangentbord kan du i stället kopiera ett hårt mellanslag från en cell och klistra in det i sökfältet.

Se upp: du ser inte vad som står i sökfältet

Både radbrytningen och det hårda mellanslaget är osynliga i dialogrutan. Fältet ser tomt ut, eller visar som mest en liten blinkande punkt, även när det innehåller ett tecken. Det ger några vanliga fällor:

• Trycker du Ctrl+J två gånger söker du efter två radbrytningar i följd och missar alla enkla.
• Ett tecken från en tidigare sökning kan ligga kvar i fältet. Rensa fältet med Backsteg och Delete några gånger innan du skriver in tecknet.
• Ett mellanslag i Ersätt med syns inte heller. Kontrollera noga om fältet ska vara tomt eller inte.

Om Excel svarar att inget hittades, töm fälten och börja om. Det är också klokt att testa på några rader först eller ha en kopia av originalet, eftersom Sök och ersätt skriver över data direkt. Ångra med Ctrl+Z om resultatet inte blev som du tänkt. Mellanslag som blir dubbla efter ersättningen tar du bort med STÄDA, eller med en ny sökning på två mellanslag som ersätts med ett.

När den städade texten ska användas vidare

Ren text är ingen kosmetisk detalj. Ett enda osynligt tecken i slutet av ett artikelnummer räcker för att LETARAD ska returnera #SAKNAS, för att en jämförelse som =A2=B2 ska ge FALSKT och för att en pivottabell ska visa samma kund på två rader. När du har städat kolumnen och klistrat in resultatet som värden fungerar uppslag, jämförelser, sortering och filtrering som de ska.

Ofta är nästa steg att sätta ihop den städade texten med tal, till exempel =A2&" – "&B2 för att få en rad som ”Skrivbord – 3495”. Då försvinner talformatet, så att tusentalsavgränsare och decimaler inte följer med. Läs mer om hur du formatera siffror i textsträngar med formeln TEXT, så ser beloppen och datumen rätt ut även i sammanslagen text.

Vill du gå vidare med fler sätt att dela upp, slå ihop och plocka ut delar av text hittar du en genomgång av TRIM och andra texthanteringsformler i vår översiktsartikel om texthantering i Excel.

💡 Ytterligare läsning
Vill du veta mer om hur man byter ut specifika tecken baserat på position eller innehåll finns en djupare genomgång här.