Excel har noen fantastiske tekstfunksjoner som kan hjelpe deg når du arbeider med tekstdata.
I noen tilfeller må du kanskje beregne det totale antallet tegn i en celle/område eller antall ganger et bestemt tegn forekommer i en celle.
Selv om det er LEN -funksjonen som kan telle antall tegn i en celle, kan du også gjøre resten med en kombinasjon av formler (som vi vil se senere i eksemplene).
I denne opplæringen vil jeg dekke forskjellige eksempler hvor du kan telle totalt eller spesifikke tegn i en celle/område i Excel.
Tell alle tegnene i en celle
Hvis du bare vil få en total telling av alle tegnene i en celle, kan du bruke LEN -funksjonen.
LEN-funksjonen tar ett argument, som kan være teksten i doble anførselstegn eller cellehenvisningen til en celle som har teksten.
Anta for eksempel at du har datasettet som vist nedenfor, og du vil telle totalt antall tegn i hver celle:
Nedenfor er formelen som gjør dette:
= LENG (A2)
I seg selv ser LEN -funksjonen kanskje ikke ut som mye, men når du kombinerer den med andre formler, kan den gjøre noen fantastiske ting (for eksempel å få ordtellingen i en celle eller dele for- og etternavn).
Merk: LEN -funksjonen teller alle tegnene i en celle, det være seg et spesialtegn, tall, skilletegn og mellomromstegn (ledende, etterfølgende og doble mellomrom mellom ord).
Siden LEN -funksjonen teller hvert tegn i en celle, kan du noen ganger få feil resultat hvis du har ekstra mellomrom i cellen.
For eksempel, i tilfellet nedenfor, returnerer LEN -funksjonen 25 for teksten i celle A1, mens den burde ha vært 22. Men siden den også teller ekstra mellomrom, får du feil resultat.
For å unngå at ekstra mellomrom telles, kan du først bruke TRIM -funksjonen til å fjerne mellomrom, etterfølgende og doble mellomrom og deretter bruke LEN -funksjonen på den for å få det virkelige ordtallet.
Nedenstående formel vil gjøre dette:
= LENNE (TRIM (A2))
Tell alle tegn i en rekke celler
Du kan også bruke LEN -funksjonen til å telle totalt antall tegn i et helt område.
Anta for eksempel at vi har samme datasett, og denne gangen, i stedet for å få antall tegn i hver celle, vil jeg vite hvor mange det er i hele området.
Du kan gjøre det ved å bruke formelen nedenfor:
= SUMPRODUCT (LENNE (A2: A7)))
La meg forklare hvordan denne formelen fungerer.
I formelen ovenfor tar LEN -delen av funksjonen et helt celleområde og teller tegnene i hver celle.
Resultatet av LEN -funksjonen vil være:
{22;21;23;23;23;31}
Hvert av disse tallene representerer antall tegn i cellen.
Og når du bruker SUMPRODUCT -funksjonen med den, vil den ganske enkelt legge til alle disse tallene.
Hvis du lurer på hvorfor du ikke kan bruke SUM i stedet for SUMPRODUCT, er årsaken at dette er en matrise, og SUMPRODUCT kan håndtere matrisen, men SUM -funksjonen kan ikke.
Men hvis du fortsatt vil bruke SUM, kan du bruke formelen nedenfor (men husk at du må bruke Ctrl + Shift + Enter for å få resultatet i stedet for en vanlig Enter)
= SUM (LENG (A2: A7))
Tell spesifikke tegn i en celle
Som jeg nevnte at den virkelige nytten av LEN -funksjonen er når den brukes i kombinasjon med andre formler.
Og hvis du vil telle bestemte tegn i en celle (det kan være en bokstav, tall, spesialtegn eller mellomromstegn), kan du gjøre det med en formelkombinasjon.
Anta for eksempel at du har datasettet som vist nedenfor, og du vil telle totalt antall ord i hver celle.
Selv om det ikke er noen innebygd formel for å få ordtellingen, kan du telle mellomromstegnene og deretter bruke den til å kjenne det totale antallet ord i cellen.
Nedenfor er formelen som gir deg det totale antallet mellomromstegn i en celle:
= LENG (A2) -LEN (SKIFTER (A2, "", ""))+1
Formelen ovenfor teller det totale antallet mellomromstegn og legger deretter 1 til det tallet for å få ordtellingen.
Slik fungerer denne formelen:
- SUBSTITUTE -funksjonen brukes til å erstatte alle mellomromstegnene med et tomt. LEN -funksjonen brukes deretter til å telle totalt antall tegn når det ikke er mellomrom.
- Resultatet av LEN (SUBSTITUTE (A2, ”“, ””)) blir deretter trukket fra LEN (A2). Dette gir oss det totale antallet mellomromstegn som er der i cellen.
- 1 legges til i formelen, og det totale antallet ord vil være ett mer enn det totale antallet mellomromstegn (ettersom to ord er atskilt med ett tegn).
Vær oppmerksom på at hvis det er noen mellomrom, bakre eller doble mellomrom, får du feil resultat. I et slikt tilfelle er det best å bruke TRIM -funksjonen sammen med LEN -funksjonen.
Du kan også bruke den samme logikken til å finne et bestemt tegn eller ord eller uttrykk i en celle.
Anta for eksempel at jeg har et datasett som vist nedenfor der jeg har forskjellige grupper, der hver batch har et alfabet og et tall som skal representere det (for eksempel A1, J2, etc.)
Nedenfor er formelen som gir deg det totale antallet ganger en batch med alfabetet A har blitt opprettet hver måned:
= LENNE (B2) -LEN (ERSTATNING (B2, "A", ""))
Formelen ovenfor bruker igjen den samme logikken - finn lengden på teksten i en celle med og uten tegnet du vil telle, og ta deretter forskjellen på disse to.
I formelen ovenfor har jeg hardkodet tegnet jeg vil telle, men du kan også sette det i en celle og deretter bruke cellereferansen. Dette gjør det mer praktisk ettersom formelen oppdateres neste gang du endrer teksten i cellen.
Tell spesifikke tegn ved bruk av store og små bokstaver
Det er ett problem med formelen som brukes til å telle bestemte tegn i en celle.
SUBSTITUTE -funksjonen er store og små bokstaver. Dette betyr at du "A" ikke er lik "a". Det er derfor du får feil resultat i celle C5 (resultatet burde vært 3).
Så hvordan kan du få tegnetallet til et bestemt tegn når det i alle fall kunne vært det (nedre eller øvre).
Du gjør det ved å gjøre formelen mellom store og små bokstaver ufølsom. Selv om du kan gå for en kompleks formel, la jeg ganske enkelt til antall tegn i begge tilfellene (små og store A).
= LEN (B2) -LEN (SKIFT (B2, "A", ""))+LEN (B2) -LEN (SKIFT (B2, "a", "")))
Tell tegn/sifre før og etter desimal
Jeg vet ikke hvorfor, men dette er en vanlig spørring jeg får fra leserne mine og har sett i mange fora - som denne
Anta at du har et datasett som vist nedenfor, og du vil telle tegnene før desimalen og etter desimalen.
Nedenfor er formlene som vil gjøre dette.
Tell tegn/tall før desimalen:
= LENN (INT (A2))
Tell tegn/tall etter desimalen:
= LENG (A2) -FIND (".", A2)
Vær oppmerksom på at disse formlene bare er ment for betydelige sifre i cellen. Hvis du har ledende eller etterfølgende nuller eller du har brukt tilpasset tallformatering for å vise flere/færre tall, vil formlene ovenfor fortsatt gi deg betydelige sifre før og etter desimalen.
Så dette er noen av scenariene der du kan bruke formler til å telle tegn i en celle eller et celleområde i Excel.
Jeg håper du synes opplæringen var nyttig!
