Formula to filter out text in text & numbers in a cell

[Clueless in UK]

Hi all,

I am after a formula that will find text in a cell that contains text and numbers and extract the text and put it in the next column along, see below example.

Before:

37 RUE DE LA SPINETTE ZONING IND DE KEUMIEE

After:

RUE DE LA SPINETTE ZONING IND DE KEUMIEE

There are also cases where there is multiples of numbers say, BUILDING 13, 28 LANCASTER STREET.

I look forward to some assistance - many thanks

[brokenbiscuits]

It might seem like a stupid question, but better to check:

In cases such as BUILDING 13, 28 LANCASTER STREET, would you just want to bring back LANCASTER STREET, or BUILDING, LANCASTER STREET?

[BlueWhale]

I am not sure if this is the right solution but you can do something like this:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0",),"1",),"2",),"3",),"4",),"5",),"6",),"7",),"8",),"9",)," ",))

[Clueless in UK]

I am not sure if this is the right solution but you can do something like this:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0",),"1",),"2",),"3",),"4",),"5",),"6",),"7",),"8",),"9",)," ",))

Hi - thank you for the formula. I tried it and it sort of worked but it removed the spaces between the words which need to remain, see example below:

Before:

HAASRODE RESEARCHPARK ZONE 3 GELDENAAKSEBAAN 464

After:

HAASRODERESEARCHPARKZONEGELDENAAKSEBAAN

Bump....

[Clueless in UK]

It might seem like a stupid question, but better to check:

In cases such as BUILDING 13, 28 LANCASTER STREET, would you just want to bring back LANCASTER STREET, or BUILDING, LANCASTER STREET?

Hi - no not a stupid questions at all. Yes I would ideally like to only have the street name but I can't see that is possible as other text that isn't the street name can be before or after the street name and can be any country in Europe (so for France street is Rue etc...)