Et enkelt, ved første øyekast, problem med en ikke-åpenbar løsning: trekke ut det siste ordet fra en tekstlinje. Vel, eller, i det generelle tilfellet, det siste fragmentet, atskilt med et gitt skilletegn (mellomrom, komma, etc.) Med andre ord er det nødvendig å implementere et omvendt søk (fra ende til begynnelse) i strengen til en gitt tegn og trekk ut alle tegnene til høyre for det.
La oss se på de tradisjonelt flere måtene å velge mellom: formler, makroer og gjennom Power Query.
Metode 1. Formler
For å gjøre det lettere å forstå essensen og mekanikken til formelen, la oss starte litt langveisfra. La oss først øke antall mellomrom mellom ord i kildeteksten vår til for eksempel 20 stykker. Du kan gjøre dette med erstatningsfunksjonen. ERSTATNING (ERSTATNING) og funksjonen til å gjenta et gitt tegn N ganger – GJENTA (REPT):
Nå klipper vi av 20 tegn fra slutten av den resulterende teksten ved å bruke funksjonen HØYRE (IKKE SANT):
Det blir varmere, ikke sant? Det gjenstår å fjerne ekstra mellomrom ved å bruke funksjonen TRIM (LISTVERK) og problemet vil bli løst:
I den engelske versjonen vil formelen vår se slik ut:
=TRIMME(HØYRE(ERSTATT(A1;» «;REPT(» «;20));20))
Jeg håper det er klart at det i prinsippet ikke er nødvendig å sette inn nøyaktig 20 mellomrom – et hvilket som helst tall holder, så lenge det er mer enn lengden på det lengste ordet i kildeteksten.
Og hvis kildeteksten ikke må deles med et mellomrom, men med et annet skilletegn (for eksempel med komma), må formelen vår korrigeres litt:
Metode 2. Makrofunksjon
Oppgaven med å trekke ut det siste ordet eller fragmentet fra teksten kan også løses ved hjelp av makroer, nemlig å skrive en omvendt søkefunksjon i Visual Basic som skal gjøre det vi trenger – søke etter en gitt delstreng i en streng i motsatt retning – fra slutten til begynnelsen.
Trykk på hurtigtasten andre+F11 eller knappen Visual Basic tab utvikler (Utvikler)for å åpne makroredigering. Legg deretter til en ny modul via menyen Sett inn – modul og kopier følgende kode dit:
Funksjon LastWord(txt As String, Optional delim As String = " ", Optional n As Integer = 1) As String arFragments = Split(txt, delim) LastWord = arFragments(UBound(arFragments) - n + 1) End Function
Nå kan du lagre arbeidsboken (i et makroaktivert format!) og bruke den opprettede funksjonen i følgende syntaks:
=Sisteord(txt ; delim ; n)
hvor
- txt – celle med kildetekst
- avgrense — skilletegn (standard — mellomrom)
- n – hvilket ord skal trekkes ut fra slutten (som standard – det første fra slutten)
Med eventuelle endringer i kildeteksten i fremtiden, vil makrofunksjonen vår bli beregnet på nytt umiddelbart, som enhver standard Excel-arkfunksjon.
Metode 3. Power Query
Strømforespørsel er et gratis tillegg fra Microsoft for å importere data til Excel fra nesten alle kilder og deretter transformere de nedlastede dataene til en hvilken som helst form. Kraften og kjøligheten til dette tillegget er så stor at Microsoft har bygget inn alle funksjonene i Excel 2016 som standard. For Excel 2010-2013 kan Power Query lastes ned gratis herfra.
Vår oppgave med å skille det siste ordet eller fragmentet gjennom en gitt separator ved hjelp av Power Query løses veldig enkelt.
Først, la oss gjøre datatabellen vår til en smart tabell ved hjelp av hurtigtaster. Ctrl+T eller kommandoer Hjem – Formater som en tabell (Hjem — Formater som tabell):
Deretter laster vi den opprettede "smarttabellen" inn i Power Query ved å bruke kommandoen Fra bord/sortiment (Fra bord/serie) tab Data (hvis du har Excel 2016) eller på fanen Strømforespørsel (hvis du har Excel 2010-2013):
I spørringsredigeringsvinduet som åpnes, på fanen Transformation (Forvandle) velge et lag Del kolonne – etter skilletegn (Delt kolonne – etter skilletegn) og så gjenstår det å angi skilletegnet og velge alternativet Avgrensningstegn lengst til høyreå kutte ikke alle ordene, men bare det siste:
Etter å ha klikket på OK det siste ordet vil bli delt inn i en ny kolonne. Den unødvendige første kolonnen kan fjernes ved å høyreklikke på overskriften og velge fjerne (Slett). Du kan også gi nytt navn til den gjenværende kolonnen i tabelloverskriften.
Resultatene kan lastes opp tilbake til arket ved hjelp av kommandoen Hjem — Lukk og last — Lukk og last til … (Hjem — Lukk og last — Lukk og last til...):
Og som et resultat får vi:
Som dette – billig og muntert, uten formler og makroer, nesten uten å røre tastaturet 🙂
Hvis den opprinnelige listen endres i fremtiden, vil det være nok å høyreklikke eller bruke en hurtigtast Ctrl+andre+F5 oppdater forespørselen vår.
- Deler opp klebrig tekst i kolonner
- Parsing og analysering av tekst med regulære uttrykk
- Trekker ut de første ordene fra teksten med ERSTATT-funksjonen