¿Cómo hacer una comparación de cadenas parciales en Excel?

Question

Tengo dos columnas en Excel. Ambas columnas tienen una lista de cadenas con el sufijo de un carácter aleatorio. El formato del prefijo es de dos cadenas seguidas de un guión bajo y el sufijo también se agrega con un guión bajo.

es decir

ABC_DEF_a => (prefix = "ABC_DEF", suffix = "a") HJDSGDJ_KJ1_a10 => (prefix = "HJDSGDJ_KJ1", suffix = "a10"

¿Cómo comparo si el prefijo de la columna B está en la columna A?

Editar: sé que puedo hacer esto dividiendo la cadena en tres secciones y luego combinando las dos primeras secciones en una columna y luego verificando si la columna está en la otra con una BUSCARV. Pero estaba buscando una función oneliner.

Answer 1

Supongo que en las celdas A1 y B1 tienes:

Y en la columna C hay un TRUE(VERDADEIRO) o FALSE(FALSO) que devuelve si el prefijo es igual o no. (perdón por la foto en portugués)

La fórmula que se me ocurrió es:

=EXACT(LEFT(SUBSTITUTE(A1;"_";"\";2);SEARCH("\";SUBSTITUTE(A1;"_";"\";2))-1);LEFT(SUBSTITUTE(B1;"_";"\";2);SEARCH("\";SUBSTITUTE(B1;"_";"\";2))-1))

---

Explicación:

=EXACT(str1,str2)

Compara si las cadenas str1 y str2 son iguales o no. Debemos extraer el prefijo de las columnas A y B. Para ello utilizamos:

=LEFT(text, [num_chars])

Extrae [num_chars]caracteres de la cadena text. Para saber cuántos caracteres debemos extraer, sustituimos el segundo guión bajo _por un carácter ficticio \:

=SUBSTITUTE(text, old_text, new_text, [instance])
=SUBSTITUTE(A1;"_";"\";2)

El truco aquí es el argumento opcional [instance]. Lo configuramos en 2, para sustituir la segunda aparición del carácter de subrayado.

=SEARCH("\";SUBSTITUTE(A1;"_";"\";2))-1)

Con esta fórmula encontramos la posición del personaje ficticio \. Poniéndolo todo junto, obtenemos la fórmula anterior.

Por pasos, si desglosamos la fórmula, tenemos:

Answer 2

Leí su pregunta como si quisiera saber para cada cadena en la Columna B, si su prefijo se encuentra en algún lugar de la Columna A. Entonces, esta respuesta resuelve ese problema.

No pude ponerlo en una línea (porque tiene que buscar en todas partes en la Columna A). Pero sólo requiere una columna adicional.

Primero, una fórmula para extraer el prefijo de cualquier cadena:

=LEFT(Column_A, FIND(Delimiter, Column_A, 1 + FIND(Delimiter, Column_A)) - 1)

• Delimiteres el carácter "_"
• La llamada interna FIND()encuentra la ubicación del primer guión bajo. Luego lo usa como punto de partida para FINDla ubicación del segundo guión bajo.
• La LEFT()función devuelve una cantidad de caracteres comenzando desde la izquierda de la cadena. Entonces restamos 1de la ubicación del segundo guión bajo y obtenemos el prefijo de la cadena.

Entonces usa esto para obtener una lista de cada prefijo de la Columna A. Luego, utiliza una fórmula matricial para comprobar si cada prefijo de la columna B está presente en la lista de prefijos de la columna A.

{=OR(LEFT(Column_B, FIND(Delimiter, Column_B, 1 + FIND(Delimiter, Column_B)) - 1)=Prefix_A)}

• Prefix_Aes la lista completa de prefijos de la Columna A.
• Esta es la misma fórmula para extraer prefijos que se aplicó a la Columna A
• Esta debe ser una fórmula matricial porque la declaración dentro de la OR()función devuelve una matriz de valores TRUEyFALSE

Aquí hay una captura de pantalla de la hoja de cálculo que creé para responder esto:

Utilicé FormulaChop para generar estas fórmulas de ejemplo (divulgación completa: escribí FormulaChop).Aquíes una captura de pantalla del resultado de FormulaChop para la primera fórmula.Aquíes un enlace a la hoja de cálculo que creé para responder esta pregunta.