Hace unos días publiqué un post donde exponía una manera de extraer los primeros y últimos caracteres no numéricos de una cadena de texto (ver aquí).
Si bien al ponerla en práctica y probarla en real salto un fallo... y es que cuando se repetían los dígitos en la cadena de texto o había otras letras intercaladas, mis fórmulas, no recuperaban correctamente los caracteres no numéricos finales.
:'(
Así pues me puse a buscar alternativas.
Una primera 'solución' la encontré para aquellos casos que la estructura de la cadena de texto era:
letras dígitos (repetidos o no) letras
Para este caso la fórmula matricial necesaria era:
=DERECHA(B8;LARGO(B8)-(MIN(SI.ERROR(ENCONTRAR(digitos;B8);LARGO(B8)))+SUMAPRODUCTO(LARGO(B8)-LARGO(SUSTITUIR(B8;digitos;""))))+1)
siendo B8 una cadena de texto como:
MJA23112223PK
Recuerda que digitos era un nombre definido que representa los valores de 0,1,2,3,4,5,6,7,8 y 9. Revisa el link comentado al inicio.
La clave de esta fórmula es descubrir cuantos dígitos existen en la cadena.. lo que conseguimos con la fórmula:
SUMAPRODUCTO(LARGO(B8)-LARGO(SUSTITUIR(B8;digitos;"")))
que identifica el número total de números en la matrícula anterior...
Luego a ese número le sumamos la primera posición de dígito encontrado y listo, ya tendríamos el dato buscado, i.e., la posición del último dígito dentro de la cadena de texto.
Y esta fórmula solucionaba el problema encontrado inicialmente... pero como no hay dos sin tres, surgió una nueva situación.. un nuevo revés.
Resulta que era posible que nuestra matrícula tuviera más letras intercaladas entre los dígitos!!!
Y obviamente la fórmula anterior, de nuevo, no era válida.
Tiempo de repensar la fórmula.
La solución definitiva, para cualquier caso, incluso para matrículas con patrón:
letras dígitos (repetidos o no) letras dígitos (repetidos o no) letras
por ejemplo, en la celda B11
M4212AB3434PKY
fue:
=DERECHA(B11;LARGO(B11)-MAX(SI(ESNUMERO(VALOR(EXTRAE(B11;FILA(INDIRECTO("1:"&LARGO(B11)));1)));FILA(INDIRECTO("1:"&LARGO(B11))))))
(ejecutada, por supuesto, matricialmente)
Para este caso, opté por identificar caracter a caracter, y buscar aquellos que fueran numéricos.. para obtener la posición únicamente de éstos.
Si revisamos la fórmula, la primera parte
EXTRAE(B11;FILA(INDIRECTO("1:"&LARGO(B11)));1)
descompone caracter a caracter la cadena de texto.
Con la función VALOR convertimos a número los dígitos... ya que con la función EXTRAE los números eran tratados como texto.
ESNUMERO convierte a valor lógico (VERDADERO o FALSO) lo que sea número, lo cual aprovechamos para con el SI condicional obtener su posición.
El resto es como siempre, aplicamos la función DERECHA y LARGO para obtener los caracteres finales (por la derecha) hasta el último dígito encontrado.
Conseguido!!!... tres intentos confirman el dicho: A la tercera va a la vencida
:D
Una última curiosidad, en el proceso de búsqueda, entre prueba y error, salto una fórmula curiosa que me permitía sumar los valores de los dígitos...
Si en B15 tenemos
MJA23B112223PK
la fórmula matricial sería:
=SUMAPRODUCTO(SI.ERROR(VALOR(EXTRAE(B15;FILA(INDIRECTO("1:"&LARGO(B15)));1));0))
con resultado, para el ejemplo, de 16 (2+3+1+1+2+2+2+3).
Curioso... no sé para que puede ser útil, pero ahí queda.
Si bien al ponerla en práctica y probarla en real salto un fallo... y es que cuando se repetían los dígitos en la cadena de texto o había otras letras intercaladas, mis fórmulas, no recuperaban correctamente los caracteres no numéricos finales.
:'(
Una primera 'solución' la encontré para aquellos casos que la estructura de la cadena de texto era:
letras dígitos (repetidos o no) letras
Para este caso la fórmula matricial necesaria era:
=DERECHA(B8;LARGO(B8)-(MIN(SI.ERROR(ENCONTRAR(digitos;B8);LARGO(B8)))+SUMAPRODUCTO(LARGO(B8)-LARGO(SUSTITUIR(B8;digitos;""))))+1)
siendo B8 una cadena de texto como:
MJA23112223PK
Recuerda que digitos era un nombre definido que representa los valores de 0,1,2,3,4,5,6,7,8 y 9. Revisa el link comentado al inicio.
La clave de esta fórmula es descubrir cuantos dígitos existen en la cadena.. lo que conseguimos con la fórmula:
SUMAPRODUCTO(LARGO(B8)-LARGO(SUSTITUIR(B8;digitos;"")))
que identifica el número total de números en la matrícula anterior...
Luego a ese número le sumamos la primera posición de dígito encontrado y listo, ya tendríamos el dato buscado, i.e., la posición del último dígito dentro de la cadena de texto.
Y esta fórmula solucionaba el problema encontrado inicialmente... pero como no hay dos sin tres, surgió una nueva situación.. un nuevo revés.
Resulta que era posible que nuestra matrícula tuviera más letras intercaladas entre los dígitos!!!
Y obviamente la fórmula anterior, de nuevo, no era válida.
Tiempo de repensar la fórmula.
La solución definitiva, para cualquier caso, incluso para matrículas con patrón:
letras dígitos (repetidos o no) letras dígitos (repetidos o no) letras
por ejemplo, en la celda B11
M4212AB3434PKY
fue:
=DERECHA(B11;LARGO(B11)-MAX(SI(ESNUMERO(VALOR(EXTRAE(B11;FILA(INDIRECTO("1:"&LARGO(B11)));1)));FILA(INDIRECTO("1:"&LARGO(B11))))))
(ejecutada, por supuesto, matricialmente)
Para este caso, opté por identificar caracter a caracter, y buscar aquellos que fueran numéricos.. para obtener la posición únicamente de éstos.
Si revisamos la fórmula, la primera parte
EXTRAE(B11;FILA(INDIRECTO("1:"&LARGO(B11)));1)
descompone caracter a caracter la cadena de texto.
Con la función VALOR convertimos a número los dígitos... ya que con la función EXTRAE los números eran tratados como texto.
ESNUMERO convierte a valor lógico (VERDADERO o FALSO) lo que sea número, lo cual aprovechamos para con el SI condicional obtener su posición.
El resto es como siempre, aplicamos la función DERECHA y LARGO para obtener los caracteres finales (por la derecha) hasta el último dígito encontrado.
Conseguido!!!... tres intentos confirman el dicho: A la tercera va a la vencida
:D
Una última curiosidad, en el proceso de búsqueda, entre prueba y error, salto una fórmula curiosa que me permitía sumar los valores de los dígitos...
Si en B15 tenemos
MJA23B112223PK
la fórmula matricial sería:
=SUMAPRODUCTO(SI.ERROR(VALOR(EXTRAE(B15;FILA(INDIRECTO("1:"&LARGO(B15)));1));0))
con resultado, para el ejemplo, de 16 (2+3+1+1+2+2+2+3).
Curioso... no sé para que puede ser útil, pero ahí queda.
Excel
via [EXCEL FORO: EJERCICIOS, EJEMPLOS, SOLUCIONES, DUDAS] [https://ift.tt/2RTAmfK]
November 15, 2018 at 04:23AM
No hay comentarios.:
Publicar un comentario