+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    11-23-2011
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    3

    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. #2
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    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. #3
    Registered User
    Join Date
    11-17-2011
    Location
    lahore
    MS-Off Ver
    Excel 2012
    Posts
    4

    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",)," ",))
    http://www.trademeters.com/ point of sale software

  4. #4
    Registered User
    Join Date
    11-23-2011
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    3

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

    Quote Originally Posted by brokenbiscuits View Post
    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. #5
    Registered User
    Join Date
    11-23-2011
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    3

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

    Quote Originally Posted by BlueWhale View Post
    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....
    Last edited by Clueless in UK; 11-24-2011 at 03:09 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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