Extract data from cells

[windywocket]

Hey all,

I was hoping someone could help me out. I have a huge spreadsheet with addresses and need to extract the location. The address is on multiple lines within the cell. Please see the example below. The locations highlighted in red is the data i need to extract. All the addresses have Victoria and a postcode on the end if that helps.

Any assistance would be much appreciated.

"BARWON VALLEY LODGE
Address: 99 Barrabool Rd, Belmont, Victoria 3216
Phone: 03 5247 2500 | Fax: 03 5244 3377
Email: reception@barwonvalleylodge.com.au"

"BAY BREEZE ACCOMMODATION PORT CAMPBELL
Address: 7350 Cobden Port Campbell Rd, Port Campbell, Victoria 3269
Phone: 03 5598 6167 | Mobile: 0437 550 276 | Fax: 03 5598 6167
Email: bay.breeze.portcampbell@hotmail.com"

[Haseeb Avarakkan]

Try this,

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,SEARCH("Victoria",A1)-1),",",REPT(" ",100)),200))

Or,

=TRIM(MID(A1,FIND(",",A1)+1,FIND(",",A1,FIND(",",A1)+1)-FIND(",",A1)-1))

[windywocket]

Thank you. The top code fix my problem. Thanks again