Excelerera ditt kunnande

– Bloggen som ger dig kraftfulla tips och trix till Excel

Så gör du ensiffriga punktuppräkningar tvåsiffriga

Hela lösningen görs som en matrisberäkning i en cell och behöver därför matas in med control och shift nedtryckta samtidigt som man matar in formeln med enter. Matrisberäkning måste aktiveras för att få med alla siffergrupperna.

  • ”2” konverteras till ”02” för att inte sorteras efter ”10”
  • Tom position ”..” visas som ”.00.”
  • Hanterar även tal högre än 99.
  • Alfasträngar som inte kan visas som tal kan presenteras som 00.

=TEXTJOIN(".";0;TEXT(EXTEXT("."&$A1&".";HITTA("<data>";BYT.UT("."&$A1&".";".";"<data>";KOLUMN(FÖRSKJUTNING($A1;0;0;1;LÄNGD($A1)-LÄNGD(BYT.UT($A1;".";""))+1))))+1;HITTA("<data>";BYT.UT("."&$A1&".";".";"<data>";KOLUMN(FÖRSKJUTNING($A1;0;0;1;LÄNGD($A1)-LÄNGD(BYT.UT($A1;".";""))+1))+1)) - HITTA("<data>";BYT.UT("."&$A1&".";".";"<data>";KOLUMN(FÖRSKJUTNING($A1;0;0;1;LÄNGD($A1)-LÄNGD(BYT.UT($A1;".";""))+1)))) -1);"00;00;00;""00""")))

Formeln är rätt lång och beskrivs här inifrån och ut.
En väsentlig del är att plocka ut textsträngen mellan punkterna. Ett exempel på den delen finns i ett annat inlägg om att dela-upp-semikolonseparerad-data.

Längd (LEN) av hela A1 minus längenden av A1 där alla punkter ersatts med ingenting -> antalet punkter. Kallas nedan för {n}.

LEN($A1)-LEN(SUBSTITUTE($A1,".","")

Förskjutning (OFFSET) ger ett område som är {n} brett, dvs en kolumn för varje punkt. Matrisformeln (matas in med Control-Shift-Enter, CSE) ska loopar igenom alla kolumner i området. Med funktionen kolumn får vi en räknare som räknar 1 tom {n}. Räknaren kallas nedan för {k}.

COLUMN(OFFSET($A1,0,0,1, {n} )+1

Punkt nummer {k} ersätts med en sträng som annars inte finns i hela texten i A1. Strängen som används är <data>…

SUBSTITUTE("."&$A1&".",".","<data>", {k})

…som vi sedan kan Hitta (FIND) så vi vet att vi är på rätt punkt. Utan att först ersätta punkten med en unik sträng skulle Hitta returnerat positionen av den första punkten.

FIND("<data>",SUBSTITUTE("."&$A1&".",".","<data>",{k}))

Nu vet vi var i A1 som punkt nummer {k} finns. Genom att extrahera den text (Extext, MID) som finns emellan den punkten och nästa {k1} kan vi få rätt på texten mellan de två angivna punkterna.
A1 konkateneras dessutom med punkt både före och efter så att både första och sista numren kan hittas.

Formlen är nu principiellt lika som kommandot ”Text till kolumner…” men strängarna fiins i en matris.

MID("."&$A1&".",FIND("<data>",SUBST...

Den resulterande strängen konverteras från numerisk till text och använder då formatet två nollor vilket alltid visar minst två siffror.

TEXT(MID("."&$A1&"." <snip> ,"00")

Slutligen läggs alla enskilda strängar ihop igen. Funktionen Textjoin ordnar det och lägger en ny punkt emellan. Nollan anger att den inte ska hoppa över tomma strängar.

TEXTJOIN(".",0,TEXT(MID("."&$A1&"." <snip> ,"00"))

Genom att hålla ned control och shift medan du trycker på enter (CSE) matas formeln in som en matrisberäkning.
Excel visar att formeln är inmatad som matrisberäkning genom att innesluta formeln i krullparenteser.

{TEXTJOIN(".",0,TEXT(MID("."&$A1&"." <snip> ,"00"))}

Extra;
Om du vill ha olika numeriska format i de olika grupperna så kan det lösas genom att komplettera med Växla (CHOOSE) istället för att ha ’endast’ ”00”;

=TEXTJOIN(".";0;TEXT(EXTEXT("."&$A1&".";HITTA("";BYT.UT("."&$A1&".";".";"";KOLUMN(FÖRSKJUTNING($A1;0;0;1;LÄNGD($A1)-LÄNGD(BYT.UT($A1;".";""))+1))))+1;HITTA("";BYT.UT("."&$A1&".";".";"";KOLUMN(FÖRSKJUTNING($A1;0;0;1;LÄNGD($A1)-LÄNGD(BYT.UT($A1;".";""))+1))+1)) - HITTA("";BYT.UT("."&$A1&".";".";"";KOLUMN(FÖRSKJUTNING($A1;0;0;1;LÄNGD($A1)-LÄNGD(BYT.UT($A1;".";""))+1)))) -1);VÄXLA(KOLUMN(FÖRSKJUTNING($A1;0;0;1;LÄNGD($A1)-LÄNGD(BYT.UT($A1;".";""))+1));1;"00";3;"000";"00;00;00;@")))

Som understruket i formeln så ligger nu en parameter och växlar till önskat format för respektive sträng. Den sista parametern, "00;00;00;@", används om det inte fanns någon träff. Se även hur talformat kan användas på olika sätt. Uppbyggnaden är möjlighet att på olika sätt formatera ”plus;minus;noll;text”.

Formeln är en av flera möjliga lösningar.  Förfrågan finns på linkedin.
https://www.linkedin.com/groups/44008/44008-6435853364600406016

BTW:
Genom att flytta in matrisformler i definierade namn blir de automatiskt matrisberäknade.

TEXTJOIN_as_Parse_and_Format.xlsx

Senaste artiklarna från Excelerera

I bloggen Excelerera listar vi tips & trix som hjälper dig få ut mer av ditt Excel-användande.

2020-01-02
Har du någon gång haft en lista som du vill jämföra med en annan och hitta de som skiljer - eller är lika? Här beskrivs sex olika sätt att räkna fram de rader du är intresserad av.
2019-09-21
LETARAD är funktionell men begränsad genom att den alltid söker till vänster i listan. Genom att kombinera INDEX och PASSA ges full frihet men är en längre formel. Här ges tips om hur du kan använda rättstavningsfunktionen för att skriva komplexa formler.

Vill du veta mer?

Fyll i kontaktformuläret nedan så hjälper vi dig komma igång med PowerPage. Du kan även boka in en kostnadsfri konsultation för att få en bättre inblick i hur PowerPage kan hjälpa dig nå dina mål.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.