Removing words and everything before these words in a column

    Hi and thanks in advance,


    We have tried various methods but cannot find what we are looking for. If we can find someone who shows ability to help us run macros and formulas for us we may look at paying for future data work as we have MUCH data and ongoing data work coming our way!


    Example Rows:


    195 Testing Road, Great Bay, Auckland
    8963 More Tests Road, Browns Bay, Auckland
    Fox Street, Excellent Area, Great City
    37 Great Tests Avenue, Burnside, Christchurch
    Great Barrier Island, Auckland


    We need to remove the words "road, street, avenue" and various other words and everything that appears before them (which I have put in bold in example) so we are left with only the "suburbs & cities" which I have made blue in the example.


    End result for above example should be:


    Great Bay, Auckland
    Browns Bay, Auckland
    Excellent Area, Great City
    Burnside, Christchurch
    Great Barrier Island, Auckland


    Kindest Regards :D

    Re: Removing words and everything before these words in a column


    Assuming your data is in column A...


    =MID(A1,SEARCH(",",A1,1)+2,LEN(A1)-SEARCH(",",A1,1))


    copied down. This is based on there being a comma (,) after the street name/number


    edit: modified formula to...
    =MID(A1,SEARCH(",",A1,1)+1,LEN(A1)-SEARCH(",",A1,1))

    Regards


    Ford

    Re: Removing words and everything before these words in a column


    We have been trying similar formulas - We replaced the comma in your formula with the word "Street" - The resulting problem with this formula is this:


    Where "Street" does not appear in originating cell we have "#VALUE!" returned in the new cell - but we need the cell contents to also copy over WITHOUT the "Street" and previous text string.


    Example Rows Before Formula with "Street":


    ROW 1: 195 Testing Road, Great Bay, Auckland
    ROW 2: 8963 More Tests Road, Browns Bay, Auckland
    ROW 3: Fox Street,Excellent Area, Great City
    ROW 4: 37 Great Tests Avenue, Burnside, Christchurch
    ROW 5: Great Barrier Island, Auckland

    Example Desired Result Rows After Running Formula With "Street" - (All other content in all cells appears in new column cells)

    ROW 1:     195 Testing Road, Great Bay, Auckland
    ROW 2: 8963 More Tests Road, Browns Bay, Auckland
    ROW 3: Excellent Area, Great City - (Street and all contents prior to the word have been deleted)
    ROW 4:     37 Great Tests Avenue, Burnside, Christchurch
    ROW 5: Great Barrier Island, Auckland


    Of course, if we can run ONE formula that handles ALL required words such as Road, Street, Avenue, Place, Terrace etc etc then that will save us running the formula multiple times, but we are happy to run a formula multiple times :)

    Re: Removing words and everything before these words in a column


    By using comma as the "slit", you dont need to determine street/avenue/road/other, thats whi I used the comma (,) in my formula.


    based on the samples provided, it returned what you wanted, in every instance. I have refined it slightly again, give it a try...
    =TRIM(IF(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))>=2,MID(A1,SEARCH(",",A1,1)+IF(ISERROR(SEARCH(", ",A1,1)),2,1),LEN(A1)-SEARCH(",",A1,1)),A1))

    Regards


    Ford

    Re: Removing words and everything before these words in a column


    Thank you for the effort,


    You are right that it works for these examples.... my apologies but...


    Some rows have many 2 or 3 or more suburbs and many of these are 2 or 3 worded like: suburb,suburbs,towns,more towns etc,cities,more cities (like how I have typed it ). However some rows only have one "Street", or "Avenue" etc etc which is only at the start of the cell (before the suburbs and cities etc start). Therefore it seems that "Street" (or user defined word) would have to be the factor in point for deletion of cell contents before that factor.

    Re: Removing words and everything before these words in a column


    ... that was a horrible explanation!...


    Most cells have 2 or more cities and two or more suburbs which must be retained. All are aeperated by commas. Issue is some rows have a "Street/Avenue/Place" name which is between commas also. Ideally, a formula would let us delete all contents in between two commas where the word "Street" appears.


    So:


    ,367 First Red Street,Go Bay,Red Bay,Byron Bay,Silly Street,Auckland,


    Would become:


    ,,Go Bay,,Red Bay,Byron Bay,Auckland


    (Of course we could easily then replace double commas after).

    Re: Removing words and everything before these words in a column


    UDF


    Use in cell like


    =RemoveStreet(A1)


    Paste the code on to a Standard module

    Re: Removing words and everything before these words in a column


    Please excuse my lack of experience in Excel, but could you give me instruction or a link to exactly how to apply this code :P


    Thanks again in advance - if it wasn't for forums like this man would have moved nowhere in the last 20 years :D

    Re: Removing words and everything before these words in a column


    1) Hit Alt + F11 to open VBE.
    2) Go to [Insert] - [Module].
    3) Paste the code on to the right pane.
    4) Hit Alt + Q to get back to Excel.
    5) Use in cell like =RemoveStreet(A1)

    Re: Removing words and everything before these words in a column


    jindon - You are an Excel Legend!! - It works and we can't thank you enough :D :D

    Re: Removing words and everything before these words in a column


    ... is it possible to use multiple words so we don't have to run it multiple times? like "Street, Avenue, Place, etc etc"

    Re: Removing words and everything before these words in a column


    Possible.
    Change the line of

    Code
    .Pattern = "[^,]+Street,?"


    To something like

    Code
    .Pattern = "[^,]+(Street|Avenue|Place|Creek),?"


    Add the word(s) separate with a pipe "|" in the bracket.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login