Posts by 0199

  • Removing words and everything before these words in a column

    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).

  • Removing words and everything before these words in a column

    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.

  • Removing words and everything before these words in a column

    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 :)

  • 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