Extract capitals from text

    Hi,


    I have a problem to solve. I have 100's of cells in a colum with address data fomatted as one line, as such:


    Horsey's Lures 110 Aspinal St LEICHHARDT QLD 4305
    Hyper Lures 11 Tuna MANLY WEST QLD 4179
    Jarvis Walker 960 Stud Rd ROWVILLE VIC 3178
    Jaysea Lures PO Box 147 KALLANGUR QLD 4503



    I would like excel to extract the suburb, state and post code together. In other words all the capital letters and the remaining number, i.e.


    LEICHHARDT QLD 4305
    MANLY WEST QLD 4179
    ROWVILLE VIC 3178
    KALLANGUR QLD 4503



    However, not the capitals in PO Box....



    What would be the formula to extract the data I want? I'd also appreciate it if there is another, simpler alternative, that doesn't work 100%, say that would return the last 3 'words' of a string? Which is nearly the same as what I want. Most suburbs consist of just 1 word - and I'd manually extract he ones with two words if necessary


    If any one wants the code to extract the company name, and street address, this is what I've come up with.


    Code to extract company name up to the street number:


    Code
    =LEFT(A1276,MIN( IF(ISNUMBER(FIND("1",A1276)),FIND("1",A1276),999), IF(ISNUMBER(FIND("2",A1276)),FIND("2",A1276),999), IF(ISNUMBER(FIND("3",A1276)),FIND("3",A1276),999), IF(ISNUMBER(FIND("4",A1276)),FIND("4",A1276),999), IF(ISNUMBER(FIND("5",A1276)),FIND("5",A1276),999), IF(ISNUMBER(FIND("6",A1276)),FIND("6",A1276),999), IF(ISNUMBER(FIND("7",A1276)),FIND("7",A1276),999), IF(ISNUMBER(FIND("8",A1276)),FIND("8",A1276),999), IF(ISNUMBER(FIND("9",A1276)),FIND("9",A1276),999) )-1)


    Code to extract the street (ending with st, rd, pl, la ...etc etc):


    Code
    =MID(A1276,LEN(B1276)+1, MIN(IF(ISNUMBER(SEARCH(" st ",A1276)),SEARCH(" st ",A1276)+2,999),IF(ISNUMBER(SEARCH(" rd ",A1276)),SEARCH(" rd ",A1276)+2,999), IF(ISNUMBER(SEARCH(" pl ",A1276)),SEARCH(" pl ",A1276)+2,999), IF(ISNUMBER(SEARCH(" cl ",A1276)),SEARCH(" cl ",A1276)+2,999), IF(ISNUMBER(SEARCH(" la ",A1276)),SEARCH(" la ",A1276)+2,999),IF(ISNUMBER(SEARCH(" hwy ",A1276)),SEARCH(" hwy ",A1276)+3,999), IF(ISNUMBER(SEARCH(" drv ",A1276)),SEARCH(" drv ",A1276)+3,999), IF(ISNUMBER(SEARCH(" ave ",A1276)),SEARCH(" ave ",A1276)+3,999), IF(ISNUMBER(SEARCH(" pde ",A1276)),SEARCH(" pde ",A1276)+3,999), IF(ISNUMBER(SEARCH(" crt ",A1276)),SEARCH(" crt ",A1276)+3,999), IF(ISNUMBER(SEARCH(" tce ",A1276)),SEARCH(" tce ",A1276)+3,999), IF(ISNUMBER(SEARCH(" crs ",A1276)),SEARCH(" crs ",A1276)+3,999), IF(ISNUMBER(SEARCH(" lane ",A1276)),SEARCH(" lane",A1276)+4,999), IF(ISNUMBER(SEARCH(" road",A1276)),SEARCH(" road",A1276)+4,999), IF(ISNUMBER(SEARCH(" street ",A1276)),SEARCH(" street",A1276)+6,999))-LEN(B1276))

    Re: Extract capitals from text


    Kinda hard to qualify that kind of fuzzy logic into code:


    Find capitals so long as they're not PO unless of course were talking about POTTS POINT, PORTSEA etc..
    Or find the left most 3 words unless the suburb is 2 words like MANLY WEST
    Or look for everything after rd, cr, st etc... unless there isnt one like 11 Tuna MANLY WEST


    Not sure you'll have much luck with an algorith to do this....


    You may have to try something like text to columns to seperate all the data and then work with it from there...


    Cheers
    Weasel

    Re: Extract capitals from text


    Hi,


    Quote

    I'd also appreciate it if there is another, simpler alternative, that doesn't work 100%, say that would return the last 3 'words' of a string? Which is nearly the same as what I want.


    Try,


    Define:


    s=ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,LEN(Sheet1!$A1)))
    t=LEFT(Sheet1!$A1,LOOKUP(LEN(Sheet1!$A1),FIND(" ",Sheet1!$A1,s)))


    In B2 and copy down,


    =SUBSTITUTE(A2,D2,"")


    In C2 and copy down,


    =LEFT(t,LOOKUP(LEN(t),FIND(" ",TRIM(t),ROW($A$2:INDEX($A:$A,LEN(t))))))


    In D2 and copy down,


    =LEFT(C2,LOOKUP(LEN(C2),FIND(" ",TRIM(C2),ROW($A$2:INDEX($A:$A,LEN(C2))))))


    HTH

Participate now!

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

Register Yourself Login