Extract a block range from a street address and remove a community from a string

    After importing a comma delimited text file one column is "5701-5799 Rutgers St- West University Place "
    "101-99 Gold St- Highlands "
    "2-38 Small St- West University Place "
    The result prefered is to place the block numbers in an empty column preceding the street name plus removing the quotation marks and area information with it's dash.


    | 1 | 1 |5701-5799|Rutgers St | 105 |07/19/05|
    | 2 | 1 | 101-99|Gold Blvd | 98 |07/19/05|
    | 3 | 1 | 2-38|Small Ct | 75 |07/19/05|


    The final result appearing in columns C and D
    If not possible at least the extraction of the street name for column D. Thanks in advance for reading this thread.
    Mr Ed

    Re: Extract a block range from a street address and remove a community from a string


    I would suggest you:
    • Do a find and replace (CTRL+H) for the quote marks. Find the quote and replace with nothing.
    • Do another find replace for the dash mark (this assumes you always have a space after the dash and the block numbers have no space between the dash). In the find box put "- *" (not including the quote marks) and put nothing in the replace box.
    • Then parse the data using TextToColumns and select space as your delimiter.


    Please post back your results or if you have any further questions.


    Regards,

    Barrie Davidson
    My Excel Web Page
    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

    Re: Extract a block range from a street address and remove a community from a string


    thanks, I will try it in the morning and advise. It sounds promising. I will have to do this for about 6500 entries a month so it will have to drop down on cell at a time,until the last entry.

    Re: Extract a block range from a street address and remove a community from a string


    Quote from mredwz

    I will have to do this for about 6500 entries a month so it will have to drop down on cell at a time,until the last entry.


    No need! Just select all the cells you want to do this to and then do your "find and replace" followed by the parsing.

    Barrie Davidson
    My Excel Web Page
    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

    Re: Extract a block range from a street address and remove a community from a string


    :thanx: Find and replace did the job removing the comunity. Then I saved the sheet one and copied to sheet two. I used TEXT to Columns to seperate the address range and copied it back to sheet one in a preceding column. Ctrl+H removing the address range from the street name. Outstanding Barrie..Ed

Participate now!

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

Register Yourself Login