Fórmula genérica
=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))
Resumen
Para dividir texto en un delimitador arbitrario (coma, espacio, barra vertical, etc.) puede usar una fórmula basada en las funciones TRIM, MID, SUBSTITUTE, REPT y LEN. En el ejemplo que se muestra, la fórmula en C5 es:
=TRIM(MID(SUBSTITUTE($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))
Nota: las referencias a B5 y C4 son referencias mixtas para permitir que la fórmula se copie hacia arriba y hacia abajo.
Explicación
La esencia de esta fórmula es reemplazar un delimitador dado con una gran cantidad de espacios usando SUBSTITUTE y REPT, luego use la función MID para extraer el texto relacionado con la "enésima ocurrencia" y la función TRIM para deshacerse del espacio extra.
En este fragmento, el delimitador (delim) se reemplaza con un número de espacios igual a la longitud total de la cadena:
SUBSTITUTE(A1,delim,REPT(" ",LEN(A1)))
Luego, la fórmula usa la función MID para extraer la enésima subcadena. El punto de partida se calcula con el siguiente código, donde N representa "enésimo":
(N-1)*LEN(A1)+1
El total de caracteres extraídos es igual a la longitud de la cadena de texto completa. Luego, la función TRIM elimina todos los espacios adicionales y devuelve solo la enésima cadena.
Extrae solo una instancia
Aunque el ejemplo está configurado para extraer 5 subcadenas del texto en la columna B, puede extraer fácilmente solo 1 instancia. Por ejemplo, para extraer solo el cuarto elemento (ciudad), puede usar:
=TRIM(MID(SUBSTITUTE(B5,"|",REPT(" ",LEN(B5))),(4-1)*LEN(B5)+1,LEN(B5)))
Función de texto a columnas
Para conversiones manuales únicas, Excel tiene una función incorporada llamada "Texto a columnas" que puede dividir texto en celdas con un delimitador de su elección. Encontrará esta función en la pestaña Datos de la cinta en la sección Herramientas de datos.
