Giver SUM 0 eller et for lavt resultat, er beløbene sandsynligvis gemt som tekst. SUM springer tekst over uden at give en fejl. Til danske beløb, der er kommet ind som tekst, er denne formel et godt udgangspunkt:
TALVÆRDI (engelsk: NUMBERVALUE) laver tekst om til tal med de separatorer, du angiver: komma som decimaltegn og punktum som tusindtalsseparator. UDSKIFT (SUBSTITUTE) fjerner hårde mellemrum først, fordi dem kan TALVÆRDI ikke klare. Men pas på amerikanske tal som “1234.50”. Dem laver formlen om til 123450 uden at give en fejl. Det gennemgår vi nedenfor.
Sådan ser du, at tallene er tekst
| Række | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Beløb (tekst) | ER.TAL | ER.TEKST | Test | Resultat | Beløb (tal) | ER.TAL | ||
| 2 | 1250 | FALSK | SAND | SUM(A2:A5) | 0 | 1.250,00 | SAND | ||
| 3 | 99,95 | FALSK | SAND | TÆL(A2:A5) | 0 | 99,95 | SAND | ||
| 4 | 1.234,50 | FALSK | SAND | TÆLV(A2:A5) | 4 | 1.234,50 | SAND | ||
| 5 | 310 | FALSK | SAND | A2+A3 | 1349,95 | 310,00 | SAND |
Tre hurtige test:
=ER.TAL(A2)(engelsk: ISNUMBER) giver FALSK, hvis cellen er tekst, også selvom den ligner et tal.TÆLogTÆLVtæller forskelligt.=TÆL(A2:A5)(COUNT) tæller kun tal og gav 0.=TÆLV(A2:A5)(COUNTA) tæller alle udfyldte celler og gav 4. Er de to tal forskellige i en kolonne med beløb, er noget af det tekst.=SUM(A2:A5)gav 0, mens=SUM(H2:H5)med de samme beløb som tal gav 2.894,45.
Det forvirrende er, at nogle beregninger alligevel virker. =A2+A3 gav 1349,95, fordi plus laver teksten om til tal. =SUM(A2;A3) gav stadig 0, fordi SUM springer tekst over i cellehenvisninger. Og =SUM("5";2) gav 7: Tekst, der er skrevet direkte som argument, blev regnet med. Et regneark kan altså have nogle totaler, der passer, og andre, der ikke gør.
Ifølge Microsoft står tal gemt som tekst venstrejusteret i cellen, og Excel kan markere dem med en grøn trekant i hjørnet.
Hvordan tallene bliver til tekst
Tekst-tal kan komme med, når du kopierer fra en hjemmeside eller en PDF eller importerer en CSV-fil. Men det kan også ske, når du skriver selv. Vi skrev disse værdier i celler med formatet Standard i Excel på dansk:
| Skrevet i cellen | Excel gemte | Vist som |
|---|---|---|
1.234,50 |
tal | 1.234,50 |
-1.234,50 |
tal | -1.234,50 |
(1.234,50) |
tal | -1.234,50 |
1.234,50 kr. |
tal (med valutaformat) | 1.234,50 kr. |
12,5 % |
tal (med procentformat) | 12,50% |
1.234,50 kr (uden punktum) |
tekst | 1.234,50 kr |
kr. 1.234,50 |
tekst | kr. 1.234,50 |
1 234,50 |
tekst | 1 234,50 |
1234.50 |
tekst | 1234.50 |
1,234.50 |
tekst | 1,234.50 |
1.234,50- |
tekst | 1.234,50- |
Excel genkender altså danske beløb og “kr.” bagerst, men ikke “kr” uden punktum, “kr.” forrest, mellemrum som tusindtalsseparator, amerikanske tal eller minus bagerst. Kommer dine tal fra en bank eller et andet system, så se også CSV-filer fra banken i Excel, hvor problemet kan løses allerede ved importen.
Ud over skrivemåden kan cellens format gøre det. Er en celle formateret som Tekst, før du skriver i den, bliver en formel heller ikke regnet ud: celler formateret som Tekst viser også formler som tekst.
VÆRDI, gange med 1 eller TALVÆRDI
Der er tre almindelige måder at lave tekst om til tal med en formel. Her er de prøvet på de samme tekster:
| Tekst i A2 | =VÆRDI(A2) |
=A2*1 |
=TALVÆRDI(A2) |
=TALVÆRDI(A2;",";".") |
=TALVÆRDI(A2;".";",") |
|---|---|---|---|---|---|
| 1.234,50 kr. | 1234,5 | 1234,5 | 1234,5 | 1234,5 | #VÆRDI! |
| 1234,50 | 1234,5 | 1234,5 | 1234,5 | 1234,5 | 123450 |
| -1.234,50 | -1234,5 | -1234,5 | -1234,5 | -1234,5 | #VÆRDI! |
| (1.234,50) | -1234,5 | -1234,5 | -1234,5 | -1234,5 | #VÆRDI! |
| 12,5 % | 0,125 | 0,125 | 0,125 | 0,125 | 1,25 |
| “ 250 “ | 250 | 250 | 250 | 250 | 250 |
| 1 234,50 | #VÆRDI! | #VÆRDI! | 1234,5 | 1234,5 | 123450 |
| 1 234,50 (hårdt mellemrum) | #VÆRDI! | #VÆRDI! | #VÆRDI! | #VÆRDI! | #VÆRDI! |
| kr. 1.234,50 | #VÆRDI! | #VÆRDI! | #VÆRDI! | #VÆRDI! | #VÆRDI! |
| 1.234,50- | #VÆRDI! | #VÆRDI! | #VÆRDI! | #VÆRDI! | #VÆRDI! |
| 1234.50 | #VÆRDI! | #VÆRDI! | 123450 | 123450 | 1234,5 |
| 1,234.50 | #VÆRDI! | #VÆRDI! | #VÆRDI! | #VÆRDI! | 1234,5 |
=--A2 gav det samme som =A2*1 i alle rækker.
Det vigtigste at lægge mærke til:
VÆRDIog*1bruger dine danske indstillinger. De klarer danske beløb, “kr.” bagerst, minus foran og parenteser, men ikke mellemrum mellem cifrene.TALVÆRDIignorerer almindelige mellemrum, også midt i tallet, som Microsoft skriver. Derfor virker den på “1 234,50”. Hårde mellemrum er ikke almindelige mellemrum, og dem ignorerer den ikke.- Forkerte separatorer giver ikke altid en fejl. “1234.50” blev til 123450 med danske separatorer, og “1234,50” blev til 123450 med amerikanske. Punktum og komma bliver behandlet som tusindtalsseparator og bare sprunget over. Tjek derfor en total mod kilden, når du har konverteret, og vælg separatorer efter, hvor tallene kommer fra, ikke efter hvad din Excel bruger.
Hårde mellemrum: TEGN(160)
Tal, der er kopieret fra en hjemmeside, kan have et hårdt mellemrum (non-breaking space) som tusindtalsseparator. Det ser ud som et almindeligt mellemrum, men er et andet tegn. LÆNGDE kan ikke afsløre det: Både “1 234,50” med almindeligt og med hårdt mellemrum gav 8. Brug KODE (engelsk: CODE) på det tegn, der ser ud som et mellemrum:
Fjern tegnet med UDSKIFT og TEGN(160) (engelsk: CHAR), før du konverterer:
“kr.” forrest og minus bagerst
“kr.” bagerst klarede både VÆRDI og TALVÆRDI selv. Står “kr.” forrest, skal det fjernes først. Formlen her fjerner både “kr.” og hårde mellemrum:
Nogle systemer skriver negative beløb med minus bagerst, fx “1.234,50-”. Det klarer ingen af funktionerne, så du må flytte minusset selv:
Hvis teksten ender med minus, fjerner VENSTRE (LEFT) det sidste tegn, og resultatet gøres negativt. Ellers konverteres teksten som normalt.
Når resultatet skal erstatte teksten
Formlerne giver tallene i en ny kolonne. Vil du have tallene i stedet for teksten, beskriver Microsoft to veje:
- Kopiér den nye kolonne, og indsæt den som værdier oven i den gamle med Indsæt speciel.
- Markér cellerne med den grønne trekant, klik på fejlindikatoren, og vælg Konvertér til tal. Ifølge Microsoft åbner Alt+Skift+F10 den samme menu.
Ingen af de to veje er afprøvet i brugerfladen. Tjek derfor en total bagefter.
Numre, der skal blive ved med at være tekst
Ikke alle tal i tekstform er en fejl. Varenumre, medarbejdernumre og lignende med foranstillede nuller skal være tekst, fordi nullerne ellers forsvinder. Skrev vi 00417 i en celle med formatet Standard, gemte Excel tallet 417. Med en apostrof foran, '00417, blev det gemt som teksten 00417.
| Række | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Varenr. (tekst) | Gjort til tal | TEKST(tal;"00000") | Tal med format 00000 | LÆNGDE af D | ER.TAL(D) |
| 2 | 00417 | 417 | 00417 | 00417 | 3 | SAND |
| 3 | 00089 | 89 | 00089 | 00089 | 2 | SAND |
| 4 | 01200 | 1200 | 01200 | 01200 | 4 | SAND |
Kolonne D ser rigtig ud, men værdien er stadig 417. Formatet viser kun nullerne. Skal du bruge nummeret i et opslag mod en liste, hvor numrene er tekst, er det ikke nok:
| Formel | Excel viste |
|---|---|
=XSAMMENLIGN(417;A2:A4) |
#I/T |
=XSAMMENLIGN("00417";A2:A4) |
1 |
=TÆL.HVIS(A2:A4;417) |
1 |
=TÆL.HVIS(A2:A4;"417") |
1 |
XSAMMENLIGN skelner mellem tallet 417 og teksten “00417”. TÆL.HVIS gør ikke: Den fandt “00417”, både når vi søgte efter tallet 417 og teksten “417”. Det kan give forkerte tællinger, hvis både “417” og “00417” findes i listen. Samme forskel mellem tal og tekst kan også være årsagen, når LOPSLAG giver #I/T.
Skal nummeret bruges som tekst med nuller, så lav det med TEKST, som i kolonne C. Meget lange numre, fx kontonumre med mere end 15 cifre, skal altid gemmes som tekst. Se kommatal og præcision.