Generisk formel
=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"."," ")))
Sammendrag
For å fjerne noe av den naturlige kompleksiteten i tekst (stripe tegnsetting, normalisere store bokstaver, fjerne ekstra mellomrom) kan du bruke en formel basert på SUBSTITUTE-funksjonen, med hjelp fra TRIM og LOWER-funksjonene.
Kontekst
Det kan være tider når du må fjerne noe av variabiliteten i tekst før annen behandling. Et eksempel er når du vil telle bestemte ord i større tekststrenger. Fordi Excel ikke gir støtte for vanlige uttrykk, kan du ikke lage presise treff. Hvis du for eksempel vil telle hvor mange ganger ordet "rev" vises i en celle, vil du ende opp med å telle "rev". Du kan se etter "rev" (med mellomrom), men det vil mislykkes med "rev" eller "rev". En løsning er å forenkle teksten først med en formel i en hjelpekolonne, og deretter kjøre teller på den forenklede versjonen. Eksemplet på denne siden viser en måte å gjøre dette på.
Forklaring
Formelen vist i dette eksemplet bruker en serie nestede SUBSTITUTE-funksjoner for å fjerne parenteser, bindestreker, kolon, semikolon, utropstegn, komma og punktum. Prosessen løper fra innsiden og ut, med at hver SUBSTITUT erstatter ett tegn med et enkelt mellomrom, og deretter avleveres til neste SUBSTITUT. Det indre SUBSTITUTE fjerner venstre parentes, og resultatet blir overlevert til neste SUBSTITUTE, som fjerner høyre parentes, og så videre.
I versjonen nedenfor er linjeskift lagt til for lesbarhet, og for å gjøre det lettere å redigere erstatninger. Excel bryr seg ikke om linjeskift i formler, så du kan bruke formelen som den er.
= LOWER( TRIM( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "("," "), ")"," "), "-"," "), ":"," "), ";"," "), "!"," "), ","," "), "."," ")))
Etter at alle erstatninger er fullført, kjøres resultatet gjennom TRIM for å normalisere mellomrom, deretter LOWER-funksjonen for å tvinge all tekst til små bokstaver.
Merk: Du må justere de faktiske erstatningene etter dine data.
Legge til en ledende og etterfølgende plass
I noen tilfeller vil du kanskje legge til et mellomromstegn i begynnelsen og slutten av den rensede teksten. Hvis du for eksempel vil telle ord nøyaktig, kan det være lurt å lete etter ordet omgitt av mellomrom (dvs. søke etter "rev", "kart") for å unngå falske treff. For å legge til et ledende og etterfølgende område, bare sammenkoble et mellomrom ("") til start og slutt:
=" "&formula&" "
Hvor "formel" er den lengre formelen ovenfor.
