Tengo direcciones en una celda y quiero extraerlas en diferentes celdas de la misma fila. Algunas celdas tienen cuatro líneas de dirección y otras tres. Puedo dividir fácilmente usando texto en columnas y varios delimitadores para los que tienen tres, pero no para los que tienen cuatro.
ingrese la descripción de la imagen aquí
En el primer ejemplo tengo cuatro líneas y el segundo tiene tres.
Anchorage Oncology Centre
3801 University Lake Drive
Suite 300-B2
Anchorage, AK 99508 US
Me gustaría que lo anterior se divida en 5 celdas. Una celda para dirección, ciudad, estado, código postal y país
Anchorage Oncology Centre
3801 University Lake Drive
Suite 300-B2
Anchorage
AK
99508
US
en el segundo ejemplo a continuación
Providence Alaska Medical Center
3200 Providence Drive
Anchorage, AK 99508 US
Me gustaría
Providence Alaska Medical Center
3200 Providence Drive
Anchorage
AK
99508
US
¿Podría hacerse esto usando una fórmula?
Gracias
Respuesta1
Suponiendo que el texto "Anchorage, AK 99508 US" esté ubicado en A1, ingrese estas fórmulas.
A4 --> =MID(A1,1,(FIND(",",A1,1))-1)
B4 --> =MID($A$1,(FIND(" ",$A$1,A3))+1,C3-(FIND(" ",$A$1,A3))-1)
C4 --> =MID($A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1,(FIND(" ",$A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1))-(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))-1)
D4 --> =MID($A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1))+1,LEN(A1))
clave: find() se utilizó para identificar el valor inicial/final de mid(). aquí encontramos cada "," & " " en el texto A1.
Respuesta2
O... podrías adoptar un enfoque bastante diferente. De hecho, dos.
Una es utilizar la FILTERXML()técnica. Uno se dedica a dividir los datos en dos partes, el "resto" y la última línea. El "resto" va a la primera celda de salida. (A continuación mostraré una forma bastante fácil de dividir las piezas). La última línea se trabaja como una cadena para reemplazar el espacio de coma y los espacios con etiquetas HTML ( </Something><Something>), transformando la cadena a la mitad en una cadena HTML, luego la etiqueta de apertura antepuesta y la etiqueta de cierre adjunta, agregando además las etiquetas HTML externas que envuelven todo eso. Esta es la entrada real a la FILTERXML()función y su salida son las cuatro celdas que contienen la información de la última línea.
Eso es más fácil de lo que parece y se vuelve sencillo después de usarlo varias veces, si se acerca el tiempo. El esfuerzo principal es dividir los datos en partes y prepararlas. Esto se debe a que cada nuevo uso parece tener datos empaquetados de manera diferente.
Una ventaja, además de la naturaleza sencilla después de la práctica, es que produce una salida de matriz "naturalmente" en lugar de necesitar {CSE}una entrada ( Ctrl-Shift-Enter). Otra es que puede seleccionar los elementos de salida por número o con [Last](y este elemento del direccionamiento se puede construir con técnicas de cadena) para que pueda extraer datos posicionales exactos si puede especificarlos.
Un enfoque diferente es utilizar un Excel 4 Macrocomando ("E4M") llamado EVALUATE(). No puede usarlo directamente en una fórmula de celda, pero debe crear un rango con nombre para contenerlo. Puede crear lo que le presenta en celdas o resolverlo todo en una única fórmula y colocarla dentro de la función en el rango con nombre. Lo que funcione mejor para usted o se adapte mejor a sus inclinaciones.
En cualquier caso, al igual que con FILTERXML(), debes preparar la entrada. La diferencia aquí es que las cosas que haces de esta manera son muy familiares y las pequeñas cosas que salen mal, omitiendo una coma o cometiendo un error tipográfico, parecen "naturales" y son relativamente fáciles de encontrar. Todo FILTERXML()parece "incorrecto" y, por un tiempo, no estará familiarizado con cómo debería verse el resultado, por lo que los errores son más fáciles de cometer y más difíciles de encontrar. Ventaja, EVALUATE().
Entonces, ¿cómo preparar los datos? Necesitas dos partes: la primera parte (que llamé "el resto" arriba) que simplemente extraerás y presentarás, y la parte que deseas que funcione de una manera especial. Luego, en la última parte, creará una cadena que se parece al tipo de matriz que Excel produce internamente en sus fórmulas. Uno que se mostraría en una fila podría verse así {"a","b",1,"K"}y los mismos datos creados para mostrarse en una columna se verían {"a";"b";1;"K"}y, por supuesto, se pueden mezclar para mostrarlos en un rango de columnas y filas. Desea crear uno como este último, con punto y coma como delimitadores.
Pero no tienes un único delimitador en la última línea. Tienes ", " y " ". Primero, tomas esa pieza y la colocas SUBSTITUTE()para cambiar ", " ("coma-espacio") por solo el espacio. Ahora tiene un solo delimitador y puede cambiarlo con SUBSTITUTE()";" Que tu necesitas. También puedes aplicar la mayoría de las comillas dobles que necesitas alrededor de todos los elementos usando ";"en lugar de solo ;. De hecho, dado que Excel hace que el uso de comillas dobles como elementos de fórmula sea bastante desagradable, usarlos CHAR(34)es mucho más fácil. "La mayoría de los" porque solo puedes agregarlos entre elementos SUBSTIITUTE()dejando las comillas dobles al principio y al final aún necesarias. Entonces los agrega en las cadenas iniciales y finales junto con la parte correspondiente de las llaves. Ahora tienes un hilo adecuado que EVALUATE()se puede romper.
Constrúyalo en una celda y, una vez que funcione, cree el rango con nombre y colóquelo todo entre EVALUATE()paréntesis. Verás en la fórmula a continuación cómo se ve. No está claro cómo se ubicará su salida, en relación con los datos de entrada, por lo que solo usé las dos filas debajo de una entrada, por lo que ingresa en A1, genera en A2 y A3. Puedes ajustarlo como desees. Entonces, dentro del rango con nombre:
=EVALUATE("{"&CHAR(34)&SUBSTITUTE(SUBSTITUTE(RIGHT(A1,LEN(A1)-MAX(IFERROR(FIND(CHAR(10),A1,SEQUENCE(1,LEN(A1))),""))),", "," ")," ",CHAR(34)&";"&CHAR(34))&CHAR(34)&"}")
SEQUENCE()podría haber estado disponible cuando se escribió la pregunta. Puede que no. Lo usé por simplicidad ya que, después de todo, ESTOY escribiendo para personas que buscan el problema pero desean una solución actualizada. Sin embargo, entonces existían sustitutos, utilizándose el común ROW(1:xxx)de manera simple o compleja.
"En el día", es posible que haya sido necesario ingresarlo con {CSE}la entrada.
Hace todas las cosas de las que ya se habló.
El pequeño elemento aquí es cómo dividir la entrada. Cada problema es diferente, pero el tuyo no es demasiado difícil. Cada línea en la celda de entrada termina con Alt-Enter, o CHAR(10). Así que puedes contarlos usando la SUBSTITUTE()técnica de "longitud del contenido menos la longitud del contenido después de eliminarlos". Luego envuelves ese a FIND()que hace algo que normalmente no se hace: usa el parámetro "número de posición" (tercero) para tomar una matriz de valores desde 1 hasta la longitud de la entrada para que tengas una matriz de FIND()cada uno de los que mira. colóquelo más adentro de la entrada. Eso produce una matriz de valores de la siguiente aparición a medida que pasa por la entrada con errores al final, ya que hay cosas después del último. No se pueden utilizar esos errores, así que IFERROR()los borra. El número más alto encontrado es el punto de ruptura de la entrada, así que utilícelo MAX()para generarlo. Luego haces dos fórmulas, una LEFT()de esa cantidad de caracteres para que sea fácil, simplemente coloca las primeras líneas en su lugar (por muchas, 3,4,5, lo que sea, que sean). Luego, una RIGHT()de las entradas de la longitud de la entrada menos este punto de ruptura recoge el resto. Esa es la entrada para la EVALUATE()función.
Al igual que con FILTERXML(), todo eso es bastante fácil ya que el masaje del material debe realizarse en cualquier dirección que vayas. Luego simplemente se hunde EVALUATE().