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.
Hola Ismael
ResponderEliminarCreo que es más fácil y claro usando Power Query en vez de fórmulas matriciales de Excel.
En este ejemplo, el origen es una tabla de Excel llamada "Test" (original que es uno) con una sola columna llamada "Datos".
La consulta de PQ devuelte otra tabla con tres columnas adicionales "Texto_izda", "Texto_dcha" y "Texto_interior". Las fórmulas de PQ para generar los valores de estas columnas creo que se entienden solas y, en el fondo, son muy similares a las de Excel.
Un saludo
let
Source = Excel.CurrentWorkbook(){[Name="Test"]}[Content],
Numeros = {"0","1","2","3","4","5","6","7","8","9"},
Izda = Table.AddColumn(Source,"Texto_izda", each Text.Start([Datos], Text.PositionOfAny([Datos],Numeros,Occurrence.First))),
Dcha = Table.AddColumn(Izda,"Texto_dcha", each Text.Range([Datos], Text.PositionOfAny([Datos],Numeros,Occurrence.Last) + 1)),
Interior = Table.AddColumn(Dcha,"Texto_interior", each Text.Range([Datos], Text.PositionOfAny([Datos],Numeros,Occurrence.First), Text.PositionOfAny([Datos],Numeros,Occurrence.Last) - Text.PositionOfAny([Datos],Numeros,Occurrence.First) + 1 ))
in
Interior
Hola
Eliminar;-)
Muy bueno!!
Sin duda que PQ tiene una variedad de funciones más amplia que la propia hoja de cálculo...
Subiré un post, con tu permiso, explicando la consulta.
Un cordial saludo y gracias por el aporte.
Permiso concedido, no lo necesitas ;-)
ResponderEliminarEncantado de poder ayudar.