Fórmula para separar las direcciones de los números

Una pregunta la cual es importante que me confirmes. Estoy tratando de obtener un patrón en tus datos para obtener la fórmula.
¿En la primera dirección es correcta "BJ"· Debe de estar ahí o no?
¿Todas las direcciones terminan con un numero?
Te pediría por favor que aun no finalices esta consulta, dado que primero necesito tu confirmación. Podrás finalizar cuando el tema este completamente resuelto.
Christian

Hola Christian, a tu primera duda, las palabras BJ, significan "bajo": ej AV FEDERICO GARCÍA LORCA 9 bajo.
A la segunda pregunta, la mayoría de las direcciones si acaban en número, hay algunas que incluso hasta de tres cifras, pero de un listado que tengo de unos 7.000 clientes, el 80% no acaban en un número, como lo de BJ.
Gracias por el interés mostrado, y espero que puedas resolver mí problema...
Un Saludo. Dualbios.

No creas que me he olvidado de tu caso. Agradezco tu paciencia y colaboración por ello.
Tengo unas dos primeras fórmulas que quiero compartir y luego que la pruebes me confirmas si esto es lo que necesitas. Me confirmas nada más, aun no finalices. Estad fórmulas sirven cuando la dirección termina en números.
1.- Valores en la columna A:
A1: av felipe ii 13
A2: av floranes 51
***
2.- En la celda D1 escribe o copia y pega esta formula:
=IZQUIERDA(A1;ENCONTRAR("*";SUSTITUIR(A1;" ";"*";LARGO(A1)-LARGO(SUSTITUIR(A1;" ";"")))))
[Fijate si tu separador de argumento de funciones es punto y coma o coma. Yo he empleado punto y coma ";"]
****
3.- En la celda E1 copia esta segunda formula:
=DERECHA(A1;LARGO(A1)-ENCONTRAR("*";SUSTITUIR(A1;" ";"*";LARGO(A1)-LARGO(SUSTITUIR(A1;" ";"")))))
[Igual, fijate en tu separador]
***+
4.- La primera te devuelve la dirección sin el numero y la segunda te devuelve el numero nada más. El problema surge cuando no termina en numero. La idea la tengo clara de como resolver mediante una condicional. Sin embargo dudo mucho que Excel me acepte puesto que seria una gran megaformula. Quizá sea posible reducirlo mediante VBA.
***
5.- A la espera de tus comentarios. Estad os fórmulas funcionan al menos para el caso citado. Aun no finalices por favor.
Christian
****

Sí señor... la verdad es que la idea va por ahí..., el único pero que podríamos poner es de que cuando no acaba en número no sale bien, pero como ya te comenté al principio, la mayoría de las direcciones si acaban en número... si ves que puedes con ello, estoy a tu entera disposición.
Seguimos en contacto... y con esto que me has mandado, yo empiezo a trabajar.
Gracias. Dualbios.
Ah, con punto y como va bien! Je je.

De acuerdo mi estimado amigo!
Tendremos que hacer dos pares de fórmulas. Una de ellas es la que te di ayer. Haré dos más que precisamente harán el 80% del trabajo dado que como me coemntas la gran mayoría de direcciones termina en letras.
Descuida amigo, que en la tarde saliendo del trabajo con más calma hago el resto. Lo que prometo lo cumplo :D
Christian

Como te había comentado en mi post anterior. Si hubiéramos encontrado un patrón en las direcciones solo bastaría con un par de fórmulas para solucionar el caso. Tu caso es uno de los más especiales con la cual me he topado hasta el momento.
He hecho dos pares de fórmulas. El primer par sirve cuando la dirección termina en números. El siguiente par de fórmulas que ahora te daré sirve cuando la dirección no termina en números, como el caso de AV FEDERICO GARCÍA LORCA 9 BJ
===
A) FÓRMULAS PARA DIRECCIONES QUE TERMINAN EN NÚMEROS:(Las que te pase ayer)
Caso: AV FLORANES 51
 =IZQUIERDA(A1;ENCONTRAR("*";SUSTITUIR(A1;" ";"*";LARGO(A1)-LARGO(SUSTITUIR(A1;" ";"")))))
[Fijate si tu separador de argumentos es punto y coma o coma. Yo he empleado punto y coma ";"]
****
=DERECHA(A1;LARGO(A1)-ENCONTRAR("*";SUSTITUIR(A1;" ";"*";LARGO(A1)-LARGO(SUSTITUIR(A1;" ";"")))))
[Fijate en tu clase de separador]
===================================
=================================
b) Fórmulas para direcciones que no terminan en números:
Caso: av federico García lorca 9 bj
***
Asumiendo que en A1 esta la dirección entonces en C1 copia esta fórmula (Ojo es la celda C1 primero). Hago esto porque la fórmula que habrá en B1 luego depende del resultado de la fórmula de C1.
Fórmula para C1 es:
=EXTRAE(A1;ENCONTRAR("*";SUSTITUIR(A1;" ";"*";LARGO(A1)-LARGO(SUSTITUIR(A1;" ";""))-1))+1;ENCONTRAR("*";SUSTITUIR(A1;" ";"*";LARGO(A1)-LARGO(SUSTITUIR(A1;" ";""))))-ENCONTRAR("*";SUSTITUIR(A1;" ";"*";LARGO(A1)-LARGO(SUSTITUIR(A1;" ";""))-1))-1)
[Fijate en tu clase de separador] Esta fórmula te devuelve el numero nada más.
***
Ahora la fórmula para la celda B1 que depende de C1 es:
=ESPACIOS(SUSTITUIR(A1;C1;""))
[Fijate en tu separador]
=====
He probado con la dirección de AV FEDERICO GARCÍA LORCA 9 BJ y funciona. Te separa las letras a una celda y los números a otra.
Espero haberte ayudado en tu caso.
No olvides ahora si de poder "Finalizar y Puntuar" al final de esta página.