extraer contenido de una celda en excel

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.

http://www.office.com/redir/HP010062569





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

https://support.office.com/es-ar/article/Dividir-texto-entre-columnas-mediante-funciones-c2930414-9678-49d7-89bc-1bf66e219ea8#bmex11
















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))



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.


Busca la posición numérica del primer espacio en A2, empezando desde la

izquierda. (5)

https://support.office.com/es-ar/article/Dividir-texto-entre-columnas-mediante-funciones-c2930414-9678-49d7-89bc-1bf66e219ea8#top

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:



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:




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)


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))



",A2,HALLAR(" ",A2,1)+1)+1))



Nombre


octavo carácter (el primer espacio). La fórmula extrae los ocho primeros

caracteres en A2, empezando desde la izquierda.



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.





espacio (B). Esta posición numérica es la posición inicial del segundo nombre.

(8 + 1 = 9)





(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


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:




espacio (B). El resultado es el número de carácter en el que desea iniciar la


Busca el segundo espacio en A2, empezando desde la novena posición (B)



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)


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))



",A2,1)+1))

=IZQUIERDA(A2,

HALLAR(" ",A2,1)-

2)



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.






espacio (J). Esta posición numérica es la posición inicial del nombre. (8 + 1 = 9)





(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)




Toma el número de carácter del segundo espacio hallado en el paso 5 y lo resta


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:




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)


número de caracteres desde la izquierda hasta el segundo espacio hallado en el


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.




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))



",A2,HALLAR(" ",A2,1)+1)+1))



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.





espacio (K). El resultado es el número de carácter en el que desea iniciar la



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:



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


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)


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)


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.


núm_caracteres:


izquierda. (5)

Agrega 1 para obtener el carácter situado detrás del primer espacio (K). El


espacio. (5 + 1 = 6)

Busca el segundo espacio en A2, empezando desde la sexta posición (K) hallada

en el paso 2. (9)


espacio (D). El resultado es la posición inicial del segundo nombre. (9 + 1 = 10)







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))


",A2,1))



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.



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.




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


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))



Nombre


octavo carácter (el primer espacio). La fórmula extrae seis caracteres desde la

izquierda.



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.




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


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))




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:


izquierda. (6)

Agrega 1 para obtener el carácter situado detrás del primer espacio (J). El



Busca el segundo espacio en A2, empezando desde la séptima posición (J)



resultado es la posición inicial del nombre. (11 + 1 = 12)



El resultado es el número de carácter en el que desea iniciar la búsqueda del

tercer espacio. (12)


izquierda. El resultado es la posición final del nombre. (15)


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


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.



izquierda. (6)




Busca el segundo espacio en A2, empezando desde la séptima posición (J)



resultado es la posición inicial del nombre. (11 + 1 = 12)







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.



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.



izquierda. (6)


resultado es la posición inicial del sufijo. (6 + 1 = 7)



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


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))



",A2,1)+1))




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.


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.



izquierda. (5)


espacio (A). El resultado es la posición inicial del apellido. (5 + 1 = 6)



izquierda. (5)


(A). El resultado es el número de carácter en el que desea iniciar la búsqueda

del segundo espacio. (5 + 1 = 6)


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),


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.


núm_caracteres:


izquierda. (5)

Agrega 1 para obtener el carácter situado detrás del primer espacio (A). El



Busca el segundo espacio en A2, empezando desde la sexta posición (A) hallada

en el paso 2. (12)





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))


",A2,HALLAR(" ",A2,1)+1))



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.




izquierda. (4)


espacio (T). El resultado es la posición inicial del nombre. (4 + 1 = 5)



izquierda. (4)


(T). El resultado es el número de carácter en el que desea iniciar la búsqueda

del segundo espacio. (4 + 1 = 5)


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),


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.


núm_caracteres:


izquierda. (4)


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)






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))


",A2,1))



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.




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).




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:

https://exceltotal.com/el-editor-de-visual-basic/

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

https://exceltotal.com/arreglos-en-vba/

https://exceltotal.com/arreglos-en-vba/

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)

https://exceltotal.com/evaluar-el-color-de-fondo-de-una-celda/

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()



ActiveCell.EntireColumn.Interior.Color = RGB(255, 145, 145)

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)



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.

https://cdn.exceltotal.com/wp-content/uploads/2014/00/como-resaltar-la-fila-de-la-celda-activa-en-excel.zip

extraer contenido de una celda en excel

Documents