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

1. ## Formula to filter out text in text & numbers in a cell

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

2. ## Re: Formula to filter out text in text & numbers in a cell

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?

3. ## Re: Formula to filter out text in text & numbers in a cell

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",)," ",))

4. ## Re: Formula to filter out text in text & numbers in a cell

Originally Posted by 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?
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...)

5. ## Re: Formula to filter out text in text & numbers in a cell

Originally Posted by 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",)," ",))
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....

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1