Extraer contenido de una celda en Excel
https://exceltotal.com/extraer-contenido-de-una-celda-en-excel/En ocasiones necesitamos extraer contenido de una celda de Excel para utilizarlo en alguna otra fórmula o simplemente porque simplemente necesitamos una parte de la cadena de texto contenida en la celda.
Extraer caracteres al inicio o al final
Si necesitas extraer un número determinado de caracteres que se encuentran al inicio o al final de una cadena de texto podemos utilizar la función IZQUIERDA o la función DERECHA. Observa el siguiente ejemplo que extrae los primeros 5 caracteres de la cadena de texto en A1:
En este ejemplo utilice la función IZQUIERDA pero puedo utilizar la función DERECHA para extraer los últimos 4 caracteres de la misma cadena:
Extraer caracteres intermedios
Si por el contrario, necesitas extraer sólo una parte que se encuentra en medio de la cadena de texto, podemos utilizar la función EXTRAE que nos permite indicar la posición inicial dentro de la cadena donde se iniciará la extracción y adicionalmente la cantidad de caracteres que deseamos extraer.
En el siguiente ejemplo podrás observar que he extraído la palabra “una” especificando a la función EXTRAE que inicie en la posición 14 y obtenga solamente 3 caracteres.
Para poder utilizar la función EXTRAE es indispensable conocer la posición inicial donde comenzará la extracción y su longitud en caracteres.
Las tres funciones mencionadas: IZQUIERDA, DERECHA, EXTRAE son las funciones con las cuales podemosextraer contenido de una celda en Excel y a partir de estas funciones básicas podemos hacer combinaciones con otras funciones para otros usos como los que menciono a continuación.
Extraer la primera palabra
Para extraer la primera palabra de una celda utilizamos la función IZQUIERDA junto con la función ENCONTRAR que nos permitirá
encontrar la posición del primer espacio en blanco lo cual delimita la primera palabra. La fórmula utilizada es la siguiente:
=IZQUIERDA(A1,ENCONTRAR(" ",A1) -1)
El primer argumento de la función ENCONTRAR es una espacio en blanco y al resultado devuelto le resto el número 1 para poder obtener la longitud correcta de caracteres de la primera palabra. Observa el resultado:
Extraer la última palabra
Para poder extraer la última palabra la situación se complica porque es necesario encontrar el último espacio y a partir de ahí tomar los caracteres de la derecha. Pero para simplificarte el trabajo, esta es la fórmula que obtiene el resultado adecuado:
=ESPACIOS(DERECHA(SUSTITUIR(A1," ",REPETIR("
",LARGO(A1))),LARGO(A1)))
Esta fórmula devolverá el resultado correcto en nuestro ejemplo:
Extraer la n-ésima palabra
Si lo que necesitas es extraer algún número de palabra específica puedes utilizar la siguiente fórmula:
=ESPACIOS(EXTRAE(SUSTITUIR(A1," ",REPETIR(" ",LARGO(A1))), (D8-
1)*LARGO(A1)+1, LARGO(A1)))
En esta fórmula el número de palabra se encuentra en la celda D8 por lo que si quiero obtener la segunda palabra debo colocar el valor 2 en dicha celda. Observa el ejemplo:
Dividir texto entre columnas mediante funcionesLas funciones de texto sirven para manipular cadenas de datos como, por
ejemplo, distribuir, el nombre, el segundo nombre y el apellido en tres
columnas distintas.
En este artículo se explica cómo usar combinaciones de las siguientes funciones
de texto para extraer y copiar componentes del nombre en celdas distintas.
Función Sintaxis
IZQUIERDA IZQUIERDA(texto;núm_de_caracteres)
EXTRAE EXTRAE(texto,núm_inicial,núm_de_caracteres)
DERECHA DERECHA(texto;núm_de_caracteres)
HALLAR HALLAR(texto_buscado,dentro_del_texto,núm_inicial)
LARGO LARGO(texto)
Extraer componentes del nombre
La clave para distribuir componentes del nombre con funciones de texto es la
posición de cada carácter dentro de una cadena de texto. Las posiciones de los
espacios dentro de la cadena de texto son importantes porque indican el
principio y el fin de los componentes del nombre en una cadena.
Por ejemplo, en una celda que contiene sólo un nombre y un apellido, el
apellido comienza detrás de la primera instancia de un espacio. Algunos
nombres de la lista pueden contener un segundo nombre, en cuyo caso el
apellido comienza detrás de la segunda instancia de un espacio.
En este artículo se indica cómo extraer los distintos componentes de una gran
variedad de formatos de nombre. Haga clic en un nombre de ejemplo de la
siguiente tabla para ver las fórmulas de extracción de los componentes de ese
nombre.
NOTA Las fórmulas pueden resultar más fáciles de entender si las copia en una
hoja de cálculo en blanco.
Cómo copiar un ejemplo
1. Cree un libro o una hoja de cálculo en blanco.
2. Seleccione el texto de ejemplo y las fórmulas en la tabla.
NOTA No seleccione los encabezados de fila ni de columna.
3. Presione CTRL+C.
4. En la hoja de cálculo, seleccione la celda A1 y presione CTRL+V.
5. Para cambiar entre ver los resultados y las fórmulas que devuelven los
resultados, presione Alt+º (ordinal masculino) o bien, en el grupo Auditoría de
fórmulas de la ficha Fórmulas, haga clic en el botón Mostrar fórmulas.
Nombre de ejemplo Descripción Nombre Segundo
nombre
Apellido Sufijo
1 Juan Saavedra Sin segundo nombre Juan Saavedra
2 José Ignacio Peiro
Alba
Una inicial en medio José I. Peiro Alba
3 Janaina B. G. Bueno Dos iniciales en medio Janaina B. G. Bueno
4 Patino, Jaime
Humberto
Primero el apellido con una coma Jaime Humberto Patino
5 Mary Kay D.
Andersen
Nombre con dos partes Mary
Kay
D. Andersen
6 Paula Barreto de
Mattos
Apellido con tres partes Paula Barreto de
Mattos
7 James van Eaton Apellido con dos partes James van Eaton
8 Bacon Jr., Dan K. Primero el apellido y el sufijo con
una coma
Dan K. Bacon Jr.
9 Gary Altman III Con sufijo Gary Altman III
10 Sr. Tomás Navarro Con prefijo Tomás Navarro
11 Francisco Pérez-
Olaeta
Apellido con un guión Francisco Pérez-Olaeta
Ejemplo 1: Juan Saavedra
En este ejemplo, sólo hay dos componentes: el nombre y el apellido. Los dos
componentes están separados por un único espacio.
1
2
A B C
Nombre
completo
Nombre Apellido
Juan Saavedra =IZQUIERDA(A2, HALLAR("
",A2,1))
=DERECHA(A2,LARGO(A2)-HALLAR("
",A2,1))
NOTA En los gráficos siguientes, la parte resaltada del nombre completo indica
el carácter que busca la fórmula HALLAR coincidencias.
Nombre
El nombre comienza en el primer carácter de la cadena (J) y termina en el
quinto carácter (el espacio). La fórmula devuelve cinco caracteres en A2,
empezando desde la izquierda.
Utilice la función HALLAR para buscar el valor de núm_caracteres:
Busca la posición numérica del espacio en A2, empezando desde la izquierda.
(5)
Apellido
El apellido comienza en el espacio, ocupa ocho caracteres desde la derecha y
termina en el último carácter de la derecha (a). La fórmula extrae ocho
caracteres en A2, empezando desde la derecha.
Utilice las funciones HALLAR y LARGO para buscar el valor de núm_caracteres:
Busca la posición numérica del espacio en A2, empezando desde la izquierda.
(5)
Cuenta la longitud total de la cadena de texto y, a continuación, resta el
número de caracteres desde la izquierda al primer espacio hallado en el paso 1.
(13 - 5 = 8)
Volver al principio
Ejemplo 2: José Ignacio Peiro
En este ejemplo, el nombre completo tiene tres componentes: el nombre, la
inicial y el apellido. Cada componente del nombre está separado por un
espacio.
1
2
A B C D
Nombre Nombre (José) Segundo nombre (I.) Apellido (Peiro)
José I.
Peiro
=IZQUIERDA(A2,
HALLAR(" ",A2,1))
=EXTRAE(A2,HALLAR("
",A2,1)+1,HALLAR("
",A2,HALLAR(" ",A2,1)+1)-
HALLAR(" ",A2,1))
=DERECHA(A2,LARGO(A2)-
HALLAR(" ",A2,HALLAR("
",A2,1)+1))
NOTA En los gráficos siguientes, la parte resaltada del nombre completo indica
el carácter que busca la fórmula HALLAR coincidencias.
Nombre
El nombre comienza en el primer carácter de la izquierda (J) y termina en el
quinto carácter (el primer espacio). La fórmula extrae los cinco primeros
caracteres en A2, empezando desde la izquierda.
Utilice la función HALLAR para buscar el valor de núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (5)
Segundo nombre
El segundo nombre comienza en el sexto carácter (I) y termina en la octava
posición (el segundo espacio). Esta fórmula incluye funciones HALLAR anidadas
para buscar la segunda instancia de un espacio.
La fórmula extrae tres caracteres, empezando desde la sexta posición.
Utilice la función HALLAR para buscar el valor de núm_inicial:
Busca la posición numérica del primer espacio en A2, empezando desde el
primer carácter de la izquierda. (5)
Agrega 1 para obtener la posición del carácter situado detrás del primer
espacio (I). Esta posición numérica es la posición inicial del segundo nombre. (5
+ 1 = 6)
Utilice funciones HALLAR anidadas para buscar el valor de núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde el
primer carácter de la izquierda. (5)
Agrega 1 para obtener la posición del carácter situado detrás del primer espacio
(I). El resultado es el número de carácter en el que desea iniciar la búsqueda de
la segunda instancia del espacio. (5 + 1 = 6)
Busca la segunda instancia del espacio en A2, empezando desde la sexta
posición (I) hallada en el paso 4. Este número de carácter es la posición final del
segundo nombre. (8)
Busca la posición numérica del espacio en A2, empezando desde el primer
carácter de la izquierda. (5)
Toma el número de carácter del segundo espacio hallado en el paso 5 y lo resta
del primer espacio hallado en el paso 6. El resultado es el número de caracteres
que la función EXTRAE extrae del texto empezando en la sexta posición hallada
en el paso 2. (8 – 5 = 3)
Apellido
El apellido comienza en el quinto carácter desde la derecha (P) y termina en el
primer carácter de la derecha (o). Esta fórmula incluye funciones HALLAR
anidadas para buscar la segunda y tercera instancia del espacio (que se
encuentra en las posiciones quinta y octava desde la izquierda).
La fórmula extrae cinco caracteres en A2, empezando desde la derecha.
Utilice las funciones LARGO y HALLAR para buscar el valor de núm_caracteres:
Busca la posición numérica del espacio en A2, empezando desde el primer
carácter de la izquierda. (5)
Agrega 1 para obtener la posición del carácter situado detrás del primer
espacio (I). El resultado es el número de carácter en el que desea iniciar la
búsqueda de la segunda instancia del espacio. (5 + 1 = 6)
Busca la segunda instancia del espacio en A2, empezando desde la sexta
posición (I) hallada en el paso 2. Este número de carácter es la posición final del
segundo nombre. (8)
Cuenta la longitud total de la cadena de texto en A2 y, a continuación, resta el
número de caracteres desde la izquierda hasta la segunda instancia del espacio
hallada en el paso 3. El resultado es el número de caracteres que se van a
extraer desde la derecha del nombre completo. (13 – 8 = 5)
Volver al principio
Ejemplo 3: Janaina B. G. Bueno
En este ejemplo hay dos iniciales intermedias. Los componentes del nombre se
separan con el segundo y el tercer espacio.
1
2
A B C D
Nombre Nombre (Janaina) Segundo nombre (B. G.) Apellido (Bueno)
Janaina
B. G.
Bueno
=IZQUIERDA(A2,
HALLAR(" ",A2,1))
=EXTRAE(A2,HALLAR("
",A2,1)+1,HALLAR("
",A2,HALLAR(" ",A2,HALLAR("
",A2,1)+1)+1)-HALLAR(" ",A2,1))
=DERECHA(A2,LARGO(A2)-
HALLAR(" ",A2,HALLAR("
",A2,HALLAR(" ",A2,1)+1)+1))
NOTA En los gráficos siguientes, la parte resaltada del nombre completo indica
el carácter que busca la fórmula HALLAR coincidencias.
Nombre
El nombre comienza en el primer carácter de la izquierda (J) y termina en el
octavo carácter (el primer espacio). La fórmula extrae los ocho primeros
caracteres en A2, empezando desde la izquierda.
Utilice la función HALLAR para buscar el valor de núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (8)
Segundo nombre
El segundo nombre comienza en la novena posición (B) y termina en la
decimocuarta posición (el tercer espacio). Esta fórmula incluye funciones
HALLAR anidadas para buscar la primera, segunda y tercera instancia del
espacio en la octava, novena y decimocuarta posición.
La fórmula extrae cinco caracteres, empezando desde la novena posición.
Utilice la función HALLAR para buscar el valor de núm_inicial:
Busca la posición numérica del primer espacio en A2, empezando desde el
primer carácter de la izquierda. (8)
Agrega 1 para obtener la posición del carácter situado detrás del primer
espacio (B). Esta posición numérica es la posición inicial del segundo nombre.
(8 + 1 = 9)
Utilice funciones HALLAR anidadas para buscar el valor de núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde el
primer carácter de la izquierda. (8)
Agrega 1 para obtener la posición del carácter situado detrás del primer espacio
(B). El resultado es el número de carácter en el que desea iniciar la búsqueda
de la segunda instancia del espacio. (8 + 1 = 9)
Busca el segundo espacio en A2, empezando desde la novena posición (B)
hallada en el paso 4. (11)
Agrega 1 para obtener la posición del carácter situado detrás del segundo
espacio (G). Este número de carácter es la posición inicial en la que desea
iniciar la búsqueda del tercer espacio. (11 + 1 = 12)
Busca el tercer espacio en A2, empezando desde la duodécima posición hallada
en el paso 6. (14)
Busca la posición numérica del primer espacio en A2. (8)
Toma el número de carácter del tercer espacio hallado en el paso 7 y lo resta
del primer espacio hallado en el paso 6. El resultado es el número de caracteres
que la función EXTRAE extrae de la cadena de texto empezando en la novena
posición hallada en el paso 2.
Apellido
El apellido comienza en el quinto carácter desde la derecha (B) y termina en el
primer carácter de la derecha (o). Esta fórmula incluye funciones HALLAR
anidadas para buscar la primera, segunda y tercera instancia del espacio.
La fórmula extrae ocho caracteres en A2, empezando desde la derecha del
nombre completo.
Utilice las funciones anidadas HALLAR y LARGO para buscar el valor de
núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde el
primer carácter de la izquierda. (8)
Agrega 1 para obtener la posición del carácter situado detrás del primer
espacio (B). El resultado es el número de carácter en el que desea iniciar la
búsqueda de la segunda instancia del espacio. (8 + 1 = 9)
Busca el segundo espacio en A2, empezando desde la novena posición (B)
hallada en el paso 2. (11)
Agrega 1 para obtener la posición del carácter situado detrás del segundo
espacio (G). Este número de carácter es la posición inicial en la que desea
iniciar la búsqueda del tercer espacio. (11 + 1 = 12)
Busca el tercer espacio en A2, empezando en la duodécima posición (G) hallada
en el paso 6. (14)
Cuenta la longitud total de la cadena de texto en A2 y, a continuación, resta el
número de caracteres desde la izquierda hasta el tercer espacio hallado en el
paso 5. El resultado es el número de caracteres que se van a extraer desde la
derecha del nombre completo. (19 - 14 = 5)
Volver al principio
Ejemplo 4: Patino, Jaime Humberto
En este ejemplo, el apellido precede al nombre y el segundo nombre aparece al
final. La coma indica el final del apellido y un espacio separa cada componente
del nombre.
1
2
A B C D
Nombre Nombre (Jaime) Segundo nombre (Humberto) Apellido (Patino)
Patino,
Jaime
Humberto
=EXTRAE(A2,HALLAR("
",A2,1)+1,HALLAR("
",A2,HALLAR(" ",A2,1)+1)-
HALLAR(" ",A2,1))
=DERECHA(A2,LARGO(A2)-
HALLAR(" ",A2,HALLAR("
",A2,1)+1))
=IZQUIERDA(A2,
HALLAR(" ",A2,1)-
2)
NOTA En los gráficos siguientes, la parte resaltada del nombre completo indica
el carácter que busca la fórmula HALLAR coincidencias.
Nombre
El nombre comienza en el noveno carácter de la izquierda (J) y termina en el
decimocuarto carácter (el segundo espacio). Como el nombre aparece en medio
del nombre completo, necesita utilizar la función EXTRAE para extraerlo.
La fórmula extrae ocho caracteres, empezando desde la novena posición.
Utilice la función HALLAR para buscar el valor de núm_inicial:
Busca la posición numérica del primer espacio en A2, empezando desde el
primer carácter de la izquierda. (8)
Agrega 1 para obtener la posición del carácter situado detrás del primer
espacio (J). Esta posición numérica es la posición inicial del nombre. (8 + 1 = 9)
Utilice funciones HALLAR anidadas para buscar el valor de núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde el
primer carácter de la izquierda. (8)
Agrega 1 para obtener la posición del carácter situado detrás del primer espacio
(J). El resultado es número de carácter en el que desea iniciar la búsqueda del
segundo espacio. (8 + 1 = 9)
Busca el segundo espacio en A2, empezando desde la novena posición (J)
hallada en el paso 4. (14)
Busca la posición numérica del primer espacio en A2, empezando desde el
primer carácter de la izquierda. (8)
Toma el número de carácter del segundo espacio hallado en el paso 5 y lo resta
del primer espacio hallado en el paso 6. El resultado es el número de caracteres
que la función EXTRAE extrae de la cadena de texto empezando en la novena
posición hallada en el paso 2. (14 - 8 = 6)
Segundo nombre
El segundo nombre comienza en el octavo carácter de la derecha (H) y termina
en el primer carácter de la derecha (o). Esta fórmula incluye funciones HALLAR
anidadas para buscar la primera y segunda instancia del espacio en las
posiciones octava y decimocuarta desde la izquierda.
La fórmula extrae ocho caracteres, empezando desde la derecha.
Utilice las funciones HALLAR y LARGO anidadas para buscar el valor de
núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde el
primer carácter de la izquierda. (8)
Agrega 1 para obtener la posición del carácter situado detrás del primer
espacio (J). El resultado es número de carácter en el que desea iniciar la
búsqueda del segundo espacio. (8 + 1 = 9)
Busca la segunda instancia del espacio en A2, empezando desde la novena
posición (J) hallada en el paso 2. (14)
Cuenta la longitud total de la cadena de texto en A2 y, a continuación, resta el
número de caracteres desde la izquierda hasta el segundo espacio hallado en el
paso 3. El resultado es el número de caracteres que se van a extraer desde la
derecha del nombre completo. (22 -14 = 8)
Apellido
El apellido comienza en el primer carácter de la izquierda (P) y termina en el
sexto carácter (o). La fórmula extrae seis caracteres, empezando desde la
izquierda.
Utilice la función HALLAR para buscar el valor de núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde el
primer carácter de la izquierda. (8)
Resta 2 para obtener la posición numérica del carácter final del apellido (o).
El resultado es el número de caracteres que la función IZQUIERDA debe extraer.
(8 - 2 = 6)
Volver al principio
Ejemplo 5: Mary Kay D. Andersen
En este ejemplo el nombre tiene dos partes: Mary Kay. El segundo y el tercer
espacio separan cada componente del nombre.
1
2
A B C D
Nombre Nombre (Mary Kay) Segundo nombre (D.) Apellido (Andersen)
A B C D
Mary Kay
D.
Anderson
=IZQUIERDA(A2,
HALLAR("
",A2,HALLAR("
",A2,1)+1))
=EXTRAE(A2,HALLAR("
",A2,HALLAR("
",A2,1)+1)+1,HALLAR("
",A2,HALLAR(" ",A2,HALLAR("
",A2,1)+1)+1)-(HALLAR("
",A2,HALLAR(" ",A2,1)+1)+1))
=DERECHA(A2,LARGO(A2)-
HALLAR(" ",A2,HALLAR("
",A2,HALLAR(" ",A2,1)+1)+1))
NOTA En los gráficos siguientes, la parte resaltada del nombre completo indica
el carácter que busca la fórmula HALLAR coincidencias.
Nombre
El nombre comienza en el primer carácter de la izquierda y termina en el
noveno carácter (el segundo espacio). Esta fórmula incluye funciones HALLAR
anidadas para buscar la segunda instancia del espacio desde la izquierda.
La fórmula extrae nueve caracteres, empezando desde la izquierda.
Utilice funciones HALLAR anidadas para buscar el valor de núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde el
primer carácter de la izquierda. (5)
Agrega 1 para obtener la posición del carácter situado detrás del primer
espacio (K). El resultado es el número de carácter en el que desea iniciar la
búsqueda de la segunda instancia del espacio. (5 + 1 = 6)
Busca la segunda instancia del espacio en A2, empezando desde la sexta
posición (K) hallada en el paso 2. El resultado es el número de caracteres que la
función IZQUIERDA extrae de la cadena de texto. (9)
Segundo nombre
El segundo nombre comienza en la décima posición (D) y termina en la
duodécima posición (el tercer espacio). Esta fórmula incluye funciones HALLAR
anidadas para buscar la primera, segunda y tercera instancia del espacio.
La fórmula extrae dos caracteres del medio, empezando desde la décima
posición.
Utilice funciones HALLAR anidadas para buscar el valor de núm_inicial:
Busca la posición numérica del primer espacio en A2, empezando desde el
primer carácter de la izquierda. (5)
Agrega 1 para obtener el carácter situado detrás del primer espacio (K). El
resultado es el número de carácter en el que desea iniciar la búsqueda del
segundo espacio. (5 + 1 = 6)
Busca la posición de la segunda instancia del espacio en A2, empezando desde
la sexta posición (K) hallada en el paso 2. El resultado es el número de
caracteres que la función IZQUIERDA extrae desde la izquierda. (9)
Agrega 1 para obtener el carácter situado detrás del segundo espacio (D). El
resultado es la posición inicial del segundo nombre. (9 + 1 = 10)
Utilice funciones HALLAR anidadas para buscar el valor de núm_caracteres:
Busca la posición numérica del carácter situado detrás del segundo espacio (D).
El resultado es el número de carácter en el que desea iniciar la búsqueda del
tercer espacio. (10)
Busca la posición numérica del tercer espacio en A2, empezando desde la
izquierda. El resultado es la posición final del segundo nombre. (12)
Busca la posición numérica del carácter situado detrás del segundo espacio (D).
El resultado es la posición inicial del segundo nombre. (10)
Toma el número de carácter del tercer espacio hallado en el paso 6 y resta el
número de carácter de “D” hallado en el paso 7. El resultado es el número de
caracteres que la función EXTRAE extrae de la cadena de texto empezando en
la décima posición hallada en el paso 4. (12 - 10 = 2)
Apellido
El apellido empieza en el octavo carácter de la derecha. Esta fórmula incluye
funciones HALLAR anidadas para buscar la primera, segunda y tercera instancia
del espacio en la quinta, novena y duodécima posición.
La fórmula extrae ocho caracteres desde la derecha.
Utilice las funciones HALLAR y LARGO anidadas para buscar el valor de
núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (5)
Agrega 1 para obtener el carácter situado detrás del primer espacio (K). El
resultado es el número de carácter en el que desea iniciar la búsqueda del
espacio. (5 + 1 = 6)
Busca el segundo espacio en A2, empezando desde la sexta posición (K) hallada
en el paso 2. (9)
Agrega 1 para obtener la posición del carácter situado detrás del segundo
espacio (D). El resultado es la posición inicial del segundo nombre. (9 + 1 = 10)
Busca la posición numérica del tercer espacio en A2, empezando desde la
izquierda. El resultado es la posición final del segundo nombre. (12)
Cuenta la longitud total de la cadena de texto en A2 y, a continuación, resta el
número de caracteres desde la izquierda hasta el tercer espacio hallado en el
paso 5. El resultado es el número de caracteres que se van a extraer desde la
derecha del nombre completo. (20 -12 = 8)
Volver al principio
Ejemplo 6: Paula Barreto de Mattos
En este ejemplo, el apellido consta de tres partes: Barreto de Mattos. El primer
espacio indica el final del nombre y el inicio del apellido.
1
2
A B D
Nombre Nombre (Paula) Apellido (Barreto de Mattos)
Paula Barreto de
Mattos
=IZQUIERDA(A2, HALLAR("
",A2,1))
=DERECHA(A2,LARGO(A2)-HALLAR("
",A2,1))
NOTA En los gráficos siguientes, la parte resaltada del nombre completo indica
el carácter que busca la fórmula HALLAR coincidencias.
Nombre
El nombre comienza en el primer carácter de la izquierda (P) y termina en el
sexto carácter (el primer espacio). La fórmula extrae seis caracteres desde la
izquierda.
Utilice la función HALLAR para buscar el valor de núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (6)
Apellido
El apellido comienza en el séptimo carácter desde la derecha (B) y termina en
el primer carácter de la derecha (s). La fórmula extrae diecisiete caracteres
desde la derecha.
Utilice las funciones LARGO y HALLAR para buscar el valor de núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (6)
Cuenta la longitud total de la cadena de texto en A2 y, a continuación, resta
el número de caracteres desde la izquierda hasta el primer espacio hallado en
el paso 1. El resultado es el número de caracteres que se van a extraer desde la
derecha del nombre completo. (23 - 6 = 17)
Volver al principio
Ejemplo 7: James van Eaton
En este ejemplo, el apellido consta de dos partes: van Eaton. El primer espacio
indica el final del nombre y el inicio del apellido.
1
2
A B D
Nombre Nombre (James) Apellido (van Eaton)
James van Eaton =IZQUIERDA(A2, HALLAR(" ",A2,1)) =DERECHA(A2,LARGO(A2)-HALLAR(" ",A2,1))
NOTA En los gráficos siguientes, la parte resaltada del nombre completo indica
el carácter que busca la fórmula HALLAR coincidencias.
Nombre
El nombre comienza en el primer carácter de la izquierda (J) y termina en el
octavo carácter (el primer espacio). La fórmula extrae seis caracteres desde la
izquierda.
Utilice la función HALLAR para buscar el valor de núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (6)
Apellido
El apellido comienza en el noveno carácter desde la derecha (v) y termina en el
primer carácter de la derecha (n). La fórmula extrae nueve caracteres desde la
derecha del nombre completo.
Utilice las funciones LARGO y HALLAR para buscar el valor de núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (6)
Cuenta la longitud total de la cadena de texto en A2 y, a continuación, resta
el número de caracteres desde la izquierda hasta el primer espacio hallado en
el paso 1. El resultado es el número de caracteres que se van a extraer desde la
derecha del nombre completo. (15 - 6 = 9)
Volver al principio
Ejemplo 8: Bacon Jr., Dan K.
En este ejemplo, el apellido ocupa el primer lugar, seguido del sufijo. La coma
separa el apellido y el sufijo del nombre y de la inicial.
1
2
A B C D E
Nombr
e
Nombre (Dan) Segundo nombre (K.) Apellido (Bacon) Sufijo (Jr.)
Bacon
Jr., Dan
K.
=EXTRAE(A2,HALLAR
(" ",A2,HALLAR("
",A2,1)+1)+1,HALLAR(
" ",A2,HALLAR("
",A2,HALLAR("
",A2,1)+1)+1)-
HALLAR("
",A2,HALLAR("
",A2,1)+1))
=DERECHA(A2,LARGO(A
2)-HALLAR("
",A2,HALLAR("
",A2,HALLAR("
",A2,1)+1)+1))
=IZQUIERDA(A
2, HALLAR("
",A2,1))
=EXTRAE(A2,HALLAR
(" ", A2,1)+1,
(HALLAR("
",A2,HALLAR("
",A2,1)+1)-2)-
HALLAR(" ",A2,1))
NOTA En los gráficos siguientes, la parte resaltada del nombre completo indica
el carácter que busca la fórmula HALLAR coincidencias.
Nombre
El nombre comienza en el duodécimo carácter (D) y termina en el decimoquinto
carácter (el tercer espacio). La fórmula extrae tres caracteres, empezando
desde la decimoquinta posición.
Utilice funciones HALLAR anidadas para buscar el valor de núm_inicial:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (6)
Agrega 1 para obtener el carácter situado detrás del primer espacio (J). El
resultado es el número de carácter en el que desea iniciar la búsqueda del
segundo espacio. (6 + 1 = 7)
Busca el segundo espacio en A2, empezando desde la séptima posición (J)
hallada en el paso 2. (11)
Agrega 1 para obtener el carácter situado detrás del segundo espacio (D). El
resultado es la posición inicial del nombre. (11 + 1 = 12)
Utilice funciones HALLAR anidadas para buscar el valor de núm_caracteres:
Busca la posición numérica del carácter situado detrás del segundo espacio (D).
El resultado es el número de carácter en el que desea iniciar la búsqueda del
tercer espacio. (12)
Busca la posición numérica del tercer espacio en A2, empezando desde la
izquierda. El resultado es la posición final del nombre. (15)
Busca la posición numérica del carácter situado detrás del segundo espacio (D).
El resultado es la posición inicial del nombre. (12)
Toma el número de carácter del tercer espacio hallado en el paso 6 y resta el
número de carácter de “D” hallado en el paso 7. El resultado es el número de
caracteres que la función EXTRAE extrae de la cadena de texto empezando en
la duodécima posición hallada en el paso 4. (15 -12 = 3)
Segundo nombre
El segundo nombre empieza en el segundo carácter de la derecha (K). La
fórmula extrae dos caracteres desde la derecha.
Utilice funciones HALLAR anidadas para buscar el valor de núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (6)
Agrega 1 para obtener el carácter situado detrás del primer espacio (J). El
resultado es el número de carácter en el que desea iniciar la búsqueda del
segundo espacio. (6 + 1 = 7)
Busca el segundo espacio en A2, empezando desde la séptima posición (J)
hallada en el paso 2. (11)
Agrega 1 para obtener el carácter situado detrás del segundo espacio (D). El
resultado es la posición inicial del nombre. (11 + 1 = 12)
Busca la posición numérica del tercer espacio en A2, empezando desde la
izquierda. El resultado es la posición final del segundo nombre. (15)
Cuenta la longitud total de la cadena de texto en A2 y, a continuación, resta el
número de caracteres desde la izquierda hasta el tercer espacio hallado en el
paso 5. El resultado es el número de caracteres que se van a extraer desde la
derecha del nombre completo. (17 -15 = 2)
Apellido
El apellido comienza en el primer carácter de la izquierda (B) y termina en el
sexto carácter (el primer espacio). Por tanto, la fórmula extrae seis caracteres
desde la izquierda.
Utilice la función HALLAR para buscar el valor de núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (6)
Sufijo
El sufijo empieza en el séptimo carácter de la izquierda (J) y termina en el
noveno carácter de la izquierda (.). La fórmula extrae tres caracteres,
empezando desde el séptimo carácter.
Utilice la función HALLAR para buscar el valor de núm_inicial:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (6)
Agrega 1 para obtener el carácter situado detrás del primer espacio (J). El
resultado es la posición inicial del sufijo. (6 + 1 = 7)
Utilice funciones HALLAR anidadas para buscar el valor de núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (6)
Agrega 1 para obtener la posición numérica del carácter situado detrás del
primer espacio (J). El resultado es número de carácter en el que desea iniciar la
búsqueda del segundo espacio. (7)
Busca la posición numérica del segundo espacio en A2, empezando desde el
séptimo carácter hallado en el paso 4. (11)
Resta 1 del número de carácter del segundo espacio hallado en el paso 4 para
obtener el número de carácter de “,”. El resultado es la posición final del sufijo.
(11 - 1 = 10)
y Busca la posición numérica del carácter situado detrás del primer espacio (J),
hallada también en los pasos 3 y 4. (7)
Toma el número de carácter de “,” hallado en el paso 6 y resta el número de
carácter de “J” hallado en los pasos 3 y 4. El resultado es el número de
caracteres que la función EXTRAE extrae de la cadena de texto empezando en
la séptima posición hallada en el paso 2. (10 - 7 = 3)
Volver al principio
Ejemplo 9: Gary Altman III
En este ejemplo, el nombre está al principio de la cadena y el sufijo está al final
del nombre. La fórmula que se utiliza para los componentes del nombre es
similar a la del ejemplo 2, en la que el nombre se extrae mediante la función
IZQUIERDA, el apellido se extrae mediante la función EXTRAE y el sufijo se
extrae mediante la función DERECHA.
1
2
A B C D
Nombre Nombre (Gary) Apellido (Altman) Sufijo (III)
Gary
Altman
III
=IZQUIERDA(A2,
HALLAR(" ",A2,1))
=EXTRAE(A2,HALLAR("
",A2,1)+1,HALLAR("
",A2,HALLAR(" ",A2,1)+1)-
(HALLAR(" ",A2,1)+1))
=DERECHA(A2,LARGO(A2)-
HALLAR(" ",A2,HALLAR("
",A2,1)+1))
NOTA En los gráficos siguientes, la parte resaltada del nombre completo indica
el carácter que busca la fórmula HALLAR coincidencias.
Nombre
El nombre comienza en el primer carácter de la izquierda (G) y termina en el
quinto carácter (el primer espacio). Por tanto, la fórmula extrae cinco caracteres
desde la izquierda del nombre completo.
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (5)
Apellido
El apellido comienza en el sexto carácter de la izquierda (A) y termina en el
undécimo carácter (el segundo espacio). Esta fórmula incluye funciones HALLAR
anidadas para buscar las posiciones de los espacios.
La fórmula extrae seis caracteres del medio, empezando desde el sexto
carácter.
Utilice la función HALLAR para buscar el valor de núm_inicial:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (5)
Agrega 1 para obtener la posición del carácter situado detrás del primer
espacio (A). El resultado es la posición inicial del apellido. (5 + 1 = 6)
Utilice funciones HALLAR anidadas para buscar el valor de núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (5)
Agrega 1 para obtener la posición del carácter situado detrás del primer espacio
(A). El resultado es el número de carácter en el que desea iniciar la búsqueda
del segundo espacio. (5 + 1 = 6)
Busca la posición numérica del segundo espacio en A2, empezando desde el
sexto carácter hallado en el paso 4. Este número de carácter es la posición final
del apellido. (12)
y Busca la posición numérica del carácter situado detrás del primer espacio (A),
hallada también en los pasos 3 y 4. (6)
Toma el número de carácter del segundo espacio hallado en el paso 5 y, a
continuación, resta el número de carácter de “A” hallado en los pasos 6 y 7. El
resultado es el número de caracteres que la función EXTRAE extrae de la
cadena de texto empezando en la sexta posición hallada en el paso 2. (12 - 6 =
6)
Sufijo
El sufijo comienza en el tercer carácter de la derecha. Esta fórmula incluye
funciones HALLAR anidadas para buscar las posiciones de los espacios.
Utilice las funciones HALLAR y LARGO anidadas para buscar el valor de
núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (5)
Agrega 1 para obtener el carácter situado detrás del primer espacio (A). El
resultado es el número de carácter en el que desea iniciar la búsqueda del
segundo espacio. (5 + 1 = 6)
Busca el segundo espacio en A2, empezando desde la sexta posición (A) hallada
en el paso 2. (12)
Cuenta la longitud total de la cadena de texto en A2 y, a continuación, resta el
número de caracteres desde la izquierda hasta el segundo espacio hallado en el
paso 3. El resultado es el número de caracteres que se van a extraer desde la
derecha del nombre completo. (15 -12 = 3)
Volver al principio
Ejemplo 10: Sr. Tomás Navarro
En este ejemplo, el nombre completo está precedido por un prefijo. Las
fórmulas que se utilizan para los componentes del nombre son similares a las
del ejemplo 2, en las que el nombre se extrae mediante la función EXTRAE y el
apellido se extrae mediante la función DERECHA.
1
2
A B C
Nombre Nombre (Tomás) Apellido (Navarro)
Sr. Tomás
Navarro
=EXTRAE(A2,HALLAR(" ",A2,1)+1,HALLAR("
",A2,HALLAR(" ",A2,1)+1)-(HALLAR("
",A2,1)+1))
=DERECHA(A2,LARGO(A2)-HALLAR("
",A2,HALLAR(" ",A2,1)+1))
NOTA En los gráficos siguientes, la parte resaltada del nombre completo indica
el carácter que busca la fórmula HALLAR coincidencias.
Nombre
El nombre comienza en el quinto carácter de la izquierda (T) y termina en el
décimo carácter (el segundo espacio). Esta fórmula incluye funciones HALLAR
anidadas para buscar las posiciones de los espacios.
La fórmula extrae cuatro caracteres, empezando desde la quinta posición.
Utilice la función HALLAR para buscar el valor de núm_inicial:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (4)
Agrega 1 para obtener la posición del carácter situado detrás del primer
espacio (T). El resultado es la posición inicial del nombre. (4 + 1 = 5)
Utilice funciones HALLAR anidadas para buscar el valor de núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (4)
Agrega 1 para obtener la posición del carácter situado detrás del primer espacio
(T). El resultado es el número de carácter en el que desea iniciar la búsqueda
del segundo espacio. (4 + 1 = 5)
Busca la posición numérica del segundo espacio en A2, empezando desde el
quinto carácter hallado en los pasos 3 y 4. Este número de carácter es la
posición final del nombre. (10)
y Busca la posición numérica del carácter situado detrás del primer espacio (T),
hallada también en los pasos 3 y 4. (5)
Toma el número de carácter del segundo espacio hallado en el paso 5 y, a
continuación, resta el número de carácter de “T” hallado en los pasos 6 y 7. El
resultado es el número de caracteres que la función EXTRAE extrae de la
cadena de texto empezando en la quinta posición hallada en el paso 2. (10 - 5
= 5)
Apellido
El apellido comienza en el séptimo carácter de la derecha. Esta fórmula incluye
funciones HALLAR anidadas para buscar las posiciones de los espacios.
Utilice las funciones HALLAR y LARGO anidadas para buscar el valor de
núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (4)
Agrega 1 para obtener la posición del carácter situado detrás del primer
espacio (T). El resultado es el número de carácter en el que desea iniciar la
búsqueda del segundo espacio. (4 + 1 = 5)
Busca el segundo espacio en A2, empezando desde la quinta posición (T)
hallada en el paso 2. (10)
Cuenta la longitud total de la cadena de texto en A2 y, a continuación, resta el
número de caracteres desde la izquierda hasta el segundo espacio hallado en el
paso 3. El resultado es el número de caracteres que se van a extraer desde la
derecha del nombre completo. (17 - 10 = 7)
Volver al principio
Ejemplo 11: Francisco Pérez-Olaeta
En este ejemplo, el apellido contiene un guión. Cada componente del nombre
está separado por un espacio.
1
2
A B C
Nombre Nombre (Francisco) Apellido (Pérez-Olaeta)
Francisco Pérez-
Olaeta
=IZQUIERDA(A2, HALLAR("
",A2,1))
=DERECHA(A2,LARGO(A2)-HALLAR("
",A2,1))
NOTA En los gráficos siguientes, la parte resaltada del nombre completo indica
el carácter que busca la fórmula HALLAR coincidencias.
Nombre
El nombre comienza en el primer carácter de la izquierda (F) y termina en la
décima posición (el primer espacio). La fórmula extrae diez caracteres desde la
izquierda.
Utilice la función HALLAR para buscar el valor de núm_caracteres:
Busca la posición numérica del primer espacio en A2, empezando desde la
izquierda. (6)
Apellido
El apellido completo comienza en el duodécimo carácter desde la derecha (P) y
termina en el primer carácter de la derecha (a).
Utilice las funciones LARGO y HALLAR para buscar el valor de núm_caracteres:
Busca la posición numérica del espacio en A2, empezando desde el primer
carácter de la izquierda. (10)
Cuenta la longitud total de la cadena de texto que se va a extraer y, a
continuación, resta el número de caracteres desde la izquierda al primer
espacio, según el resultado del paso 1. (22 - 10 = 12)
Cómo resaltar la fila de la celda activa en ExcelCuando tienes una tabla en Excel con muchas columnas es fácil perder de vista la fila a la que pertenecen los datos. Hoy aprenderemos cómo resaltar la fila de la celda activade manera que podamos distinguir fácilmente todos los valores de una fila.
Nuestro objetivo será crear un apoyo visual para identificar fácilmente los datos que pertenecen a la misma fila de la celda activa. Al momento de seleccionar una nueva celda, la fila anterior quedará sin resaltar y la nueva fila será resaltada. Observa la siguiente animación donde queda claro nuestro objetivo:
El código VBA que desarrollaremos hará que todas las hojas de nuestro libro tengan este comportamiento sin necesidad de copiarlo en cada una de ellas. Así que sin mayor preámbulo, comencemos con el desarrollo de esta funcionalidad.
Crear una variable VBA global
El primer paso será crear una variable global en VBA donde almacenaremos el valor de la celda activa “actual” y el de la celda activa “anterior”. Esto es necesario ya que al movernos a una nueva fila debemos quitar cualquier formato de la fila previamente resaltada y por lo tanto necesito almacenar una referencia a dicha fila. Para crear esta variable debes abrir el Editor de Visual Basic y agregar un módulo de código haciendo clic derecho sobre el nombre del proyecto > Insertar > Módulo:
Una vez creado este módulo debemos colocar la siguiente línea de código:
1 Public celdaActiva(1) As Range
El hecho de que la variable sea Public nos permite tener acceso a ella desde cualquier otro módulo de código. Esta variable es un arreglo en VBA que no es más que una colección de “casillas” donde podemos almacenar valores. En este caso la variableceldaActiva tiene dos casillas del tipo Range las cuales podemos acceder con los índices 0 y 1.
Inicializar la variable celdaActiva
En este punto la variable celdaActiva creada en el paso anterior aún está vacía, así que haremos uso del evento Workbook_Open para indicarle cuál es la celda activa al momento de abrir nuestro libro de Excel. Inserta el siguiente código dentro del eventoWorkbook_Open del objeto ThisWorkbook:
1
2
3
4
Private Sub Workbook_Open()
Set celdaActiva(1) = ActiveCell
ActiveCell.EntireRow.Interior.Color = RGB(255, 145, 145)
End Sub
Al abrir nuestro libro se ejecutará el código del evento WorkbookOpen y por lo tanto el elemento 1 del arreglo celdaActiva tendrá una referencia a la celda activa de nuestro libro. La segunda instrucción que se ejecuta cambiará el valor de la
propiedad ActiveCell.EntireRow.Interior.Color para establecer un color de fondo para toda la fila de la celda activa. En este caso utilizo la función RGB para elegir un color de fondo con una combinación específica de colores rojo, verde y azul. Si quieres saber un poco más sobre los colores en Excel y la función RGB, consulta el artículo Evaluar el color de fondo de una celda.
Además de inicializar la variable celdaActiva al momento de abrir nuestro libro de Excel, lo haremos también al momento de activar una nueva hoja lo que significa que debemos hacer uso del evento Workbook_SheetActivate que también se encuentra dentro del objeto ThisWorkbook. Después de haber insertado el mismo código para ambos eventos, deberás tener una vista en el Editor de Visual Basic como la siguiente:
Resaltar la fila de la celda activa
Ahora solo nos resta resaltar la fila de la celda activa en el momento en que seleccionamos una nueva celda. Ese instante está representado en Excel por el eventoWorkbook_SheetSelectionChange donde colocaremos el siguiente código:
1
2
3
4
5
6
7
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Set celdaActiva(0) = celdaActiva(1)
Set celdaActiva(1) = Target
On Error Resume Next
celdaActiva(0).EntireRow.Interior.Color = xlNone
Target.EntireRow.Interior.Color = RGB(255, 145, 145)
8 End Sub
Ya que este evento se “dispara” en el momento en que seleccionamos una nueva celda, la primera instrucción almacena el valor de la celda activa anterior dentro deceldaActiva(0) e inmediatamente después guardamos la referencia a la nueva celda activa dentro de celdaActiva(1) y que en ese instante está referenciada por la variableTarget. Las últimas dos instrucciones cambian el color de fondo de las filas. Primero igualamos el color de celda activa anterior a xlNone que significa que removeremos cualquier color de fondo y enseguida establecemos un color de fondo con la función RGB para la fila de la nueva celda activa. Al finalizar todos estos pasos tendremos un código como el siguiente:
Eso será todo. Al haber seguido estos pasos tendrás un libro de Excel que resaltará la fila de la celda activa en todas las hojas.
Observaciones sobre el método utilizado
El método utilizado para resaltar la fila de la celda activa en Excel mostrado en este artículo modifica el valor de la propiedad Interior.Color de cada una de las celdas de la fila. Por esta razón, si tus celdas tienen aplicado algún color, éste será removido al momento de seleccionar una celda de la misma fila.
Es importante recordar que los colores que serán removidos serán los aplicados en el relleno de las celdas a través de las herramientas de
formato. Los colores aplicados por formato condicional quedarán intactos ya que dichos colores se almacenan en una propiedad diferente. De hecho, si tus datos tienen reglas de formato condicional, dichos colores estarán sobre puestos al color aplicado por el código de este ejemplo.
Resaltar fila y columna de la celda activa
Una variante interesante del ejemplo mostrado anteriormente es agregar algunas líneas de código para resaltar la columna de la celda activa y de esa manera ubicarla tanto horizontal como verticalmente. El código a utilizar será el siguiente:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Private Sub Workbook_Open()
Set celdaActiva(1) = ActiveCell
ActiveCell.EntireRow.Interior.Color = RGB(255, 145, 145)
ActiveCell.EntireColumn.Interior.Color = RGB(255, 145, 145)
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Set celdaActiva(1) = ActiveCell
ActiveCell.EntireRow.Interior.Color = RGB(255, 145, 145)
ActiveCell.EntireColumn.Interior.Color = RGB(255, 145, 145)
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Set celdaActiva(0) = celdaActiva(1)
Set celdaActiva(1) = Target
On Error Resume Next
celdaActiva(0).EntireRow.Interior.Color = xlNone
celdaActiva(0).EntireColumn.Interior.Color = xlNone
Target.EntireRow.Interior.Color = RGB(255, 145, 145)
Target.EntireColumn.Interior.Color = RGB(255, 145, 145)
20
21
22
End Sub
Observa que lo único que he hecho es agregar las líneas de código que hacen referencia a la propiedad EntireColumn.Interior.Color que resaltan la columna de la celda activa. Por supuesto que esta versión de código también necesitará de una variable global declarada en un Módulo de manera similar que en el primer ejemplo. El resultado de utilizar este código es el siguiente:
Descarga el archivo comprimido donde encontrarás dos libros de Excel. El primero contiene el ejemplo que resalta la fila de la celda activa y el segundo el que resalta tanto la fila como la columna.
Top Related