Konvertera siffror i textformat till tal
Ofta hämtar man in data ifrån affärssystem eller olika databaser. Då får man ibland med en massa skräp i sin data som gör att man inte kan utföra beräkningar. Ett vanligt problem är att vi importerat siffror som vi sedan inte kan räkna på. Vi kan ändra formatet från Allmänt till Tal osv men det går fortfarande inte att räkna på. Vi skall nu titta på ett sådant exempel där vår data ser ut som i bilden nedan. Du kan även ladda ner datan under inläggets rubrik.
Vi har data med försäljningssiffror uppdelade på kvartal. I cell B6 har vi skapat en summafunktion som returnerar talet noll. Excel kan alltså inte utföra någon beräkning. Vi kan även notera att försäljningssiffrorna är vänsterjusterade vilket indikerar att de är formaterade som text. Det finns olika sätt att ändra siffror som är i textformat till talformat.
Vi kan testa att ställa om formatet till Tal men det fungerar inte.
Vi kan även testa att använda
oss av funktionen STÄDA och RENSA som tar bort all icke-utskrivningsbara tecken
och onödiga mellanslag men det fungerar inte.
Vi kan även testa att använda oss av funktionen TEXTNUM som konverterar en textsträng som representerar ett tal till ett tal, men det fungerar inte.
Vi kan ställa os i kolumnen bredvid och göra en formel som adderar en 0: dvs = B2 + 0, men det fungerar inte.
I vårat fall så fungerar ingen av teknikerna ovan.
Vi kan däremot manuellt ta bort mellanslagen i talen och då accepterar Excel att de är tal och nu går det att räkna med. Dessvärre är den här lösningen manuell vilket gör att det blir omständigt med stora mängder data.
Men Excel skall kunna hantera vanliga mellanslag i siffror och kunna behandla det som tal. Då ställer vi oss frågan: Är det verkligen ett ”vanligt mellanslag”? Låt oss identifiera vilket tecken mellanslaget egenltigen är?
I Cell C2 matar vi följande formeln som kollar upp vilken ASCII kod det andra tecknet i cell B2 representerar:
=KOD(EXTEXT(B2;2;1))
Läs mer om hur man bryter utdelar av en textsträng med t.ex. funktionen EXTEXT här.
Vi får svaret 160. Vi kan kolla upp vilken tecken det är här: https://www.asciitabell.se/
Det är alltså ett ”Icke-brytande blanksteg”. Inte ett vanligt blanksteg eller mellanslag som har ASCII-värdet 32. Nu vet vi varför de andra sätten att få siffrorna att bete sig som tal inte fungerade. Vi kan nu lösa problemet med antingen formel eller funktionen Sök och Ersätt
Formel
Vi kan nu skapa en ny kolumn med värden som vi konverterar med följande formel där vi byter ut det Icke-bytande blanksteget mot ett nullvärde ””, eller ett mellanslag ” ”.
=TEXTNUM(BYT.UT(B2;TECKENKOD(160);""))
Klicka på länkarna för att läsa mer om formlerna BYT.UT och TECKENKOD.
I bilden nedan kan vi se resultatet av formeln ovan och våra värden har nu blivit konverterade till tal som är högerjusterade. Den här lösnignen skapar en ny kolumn och vi dubblerar därmed datan. Vi kan även byta ut det direkt med funktionen Sök och Ersätt.
Sök och Ersätt
Vi skall anävnda oss av funktionen Sök och Ersätt för att hantera mellanslagen. I den funktionen anger vi en textsträng som skall sökas upp och sedan anger vi en ny som den skall ersättas med. Vi kan antingen markera enskilda celler där ersättningarna skall ske eller så kan man köra på hela dokumentet samtidigt. Då skapas inte några extra kolumner som i lösningen ovan.
Vi börjar med att kopiera ett ”Icke-brytande blanksteg” från någon av våra försäljningssiffror. Markera själva ”mellanslaget” det första tomrummet efter 2:an i cell B2. Se bilden nedan.
Markera sedan datan i kolumn B från rad 2 till 5, eller hela bladet. Under fliken Start -> klicka på knappen Sök och markera -> Ersätt som i bilden nedan.
Du får nu upp dialogrutan Sök och Ersätt som i bilden nedan.
I fältet Sök efter: Klistra in det Icke-brytande blanksteget i fältet
I fältet Ersätt med: Lämnas tomt eller med ett mellanslag
Klicka sedan Ersätt alla
Nu är blankstegen antingen borttagna eller ersatta med ett vanligt mellanslag och det fungerar som i bilden nedan. Klicka OK för att gå tillbaka till Excel.
