L’última paraula

Un problema senzill, a primera vista, amb una solució no òbvia: extreu l'última paraula d'una línia de text. Bé, o, en el cas general, l'últim fragment, separat per un caràcter delimitador determinat (espai, coma, etc.) És a dir, cal implementar una cerca inversa (de final a principi) a la cadena d'un caràcter donat i després extreu tots els caràcters que hi ha a la dreta.

Vegem les diverses maneres tradicionals de triar: fórmules, macros i mitjançant Power Query.

Mètode 1. Fórmules

Per facilitar la comprensió de l'essència i la mecànica de la fórmula, comencem una mica de lluny. En primer lloc, augmentem el nombre d'espais entre paraules del nostre text font fins a, per exemple, 20 peces. Podeu fer-ho amb la funció de substitució. SUPLENT (SUBSTITUT) i la funció de repetir un caràcter donat N-vegades - REPETRE (REPT):

Ara tallem 20 caràcters del final del text resultant mitjançant la funció DRET (DRET):

Fa més calor, oi? Queda per eliminar espais addicionals mitjançant la funció TRIM (TRIM) i el problema es resoldrà:

A la versió en anglès, la nostra fórmula serà així:

=TALLA(DRETA(SUBSTITUÏT(A1;» «;REPT(» «;20));20))

Espero que quedi clar que, en principi, no és necessari inserir exactament 20 espais; qualsevol nombre servirà, sempre que sigui més que la longitud de la paraula més llarga del text font.

I si el text font no s'ha de dividir per un espai, sinó per un altre caràcter separador (per exemple, amb una coma), la nostra fórmula s'haurà de corregir lleugerament:

Mètode 2. Funció macro

La tasca d'extreure l'última paraula o fragment del text també es pot resoldre mitjançant macros, és a dir, escriure una funció de cerca inversa en Visual Basic que farà el que necessitem: buscar una subcadena determinada en una cadena en sentit contrari, des de el final al principi.

Premeu la drecera del teclat Alt+F11 o botó Visual Basic llengüeta revelador (Desenvolupador)per obrir l'editor de macros. A continuació, afegiu un mòdul nou mitjançant el menú Inserir – Mòdul i copieu-hi el codi següent:

 Funció LastWord(txt As String, Optional delim As String = " ", Opcional n As Integer = 1) As String arFragments = Split(txt, delim) LastWord = arFragments(UBound(arFragments) - n + 1) Funció final  

Ara podeu desar el llibre de treball (en un format habilitat per a macros!) i utilitzar la funció creada amb la sintaxi següent:

=LastWord(txt; delim; n)

where

• txt – cel·la amb text font
• delim — caràcter separador (per defecte — espai)
• n - quina paraula s'ha d'extreure del final (per defecte, la primera del final)

Amb qualsevol canvi en el text d'origen en el futur, la nostra funció de macro es tornarà a calcular sobre la marxa, com qualsevol funció estàndard de full d'Excel.

Mètode 3. Power Query

Power Consulta és un complement gratuït de Microsoft per importar dades a Excel des de gairebé qualsevol font i després transformar les dades descarregades a qualsevol forma. La potència i la frescor d'aquest complement són tan grans que Microsoft ha incorporat totes les seves funcions a Excel 2016 de manera predeterminada. Per a Excel 2010-2013 Power Query es pot descarregar gratuïtament des d'aquí.

La nostra tasca de separar l'última paraula o fragment mitjançant un separador determinat mitjançant Power Query es resol molt fàcilment.

Primer, convertim la nostra taula de dades en una taula intel·ligent mitjançant tecles de drecera. Ctrl+T o ordres Inici – Format com a taula (Inici — Format com a taula):

A continuació, carreguem la "taula intel·ligent" creada a Power Query mitjançant l'ordre De taula/gama (Des de la taula/range) llengüeta dades (si teniu Excel 2016) o a la pestanya Power Consulta (si teniu Excel 2010-2013):

A la finestra de l'editor de consultes que s'obre, a la pestanya Transformació (Transformar) triar un equip Dividir la columna: per delimitador (Dividir la columna — Per delimitador) i després queda establir el caràcter separador i seleccionar l'opció Delimitador més a la dretaper tallar no totes les paraules, sinó només l'última:

Després de fer clic OK l'última paraula es separarà en una nova columna. La primera columna innecessària es pot eliminar fent clic amb el botó dret a la seva capçalera i seleccionant-la Remove (Suprimeix). També podeu canviar el nom de la columna restant a la capçalera de la taula.

Els resultats es poden tornar a carregar al full mitjançant l'ordre Inici — Tancar i carregar — Tancar i carregar a... (Inici — Tancar i carregar — Tancar i carregar a...):

I com a resultat obtenim:

Així: barat i alegre, sense fórmules ni macros, gairebé sense tocar el teclat 🙂

Si la llista original canvia en el futur, n'hi haurà prou amb fer clic amb el botó dret o utilitzar una drecera del teclat Ctrl+Alt+F5 actualitzar la nostra petició.

• Dividir el text enganxós en columnes
• Anàlisi i anàlisi de text amb expressions regulars
• Extracció de les primeres paraules del text amb la funció SUBSTITUTE