 # 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!