sobrien1234
Board Regular
- Joined
- May 10, 2016
- Messages
- 175
- Office Version
- 365
- Platform
- Windows
I have a list as follows in column A:
Dr Jan Kay Adamson
House of help
Phone: 02 6111 0999
Address: 44 Davidson Street ALBURY NSW 2685
More Details
Dr Bob Asher
Phone: 02 2222 3222
Address: 3505 George Street SYDNEY NSW 2000
More Details
Dr Stephen Attwell
Phone: 02 98765 7000
Address: Shop 1A 24 Bell AvenueKOGARAH NSW 2217
More Details
As you can see some of them have the names of the business (which take up a row) and some don't. This means they all have varying levels of rows they take up. I need a formula:
1. for column B that somehow extrapolates the phone number and puts it in the same row as their name
2. For column C I need their street number and name (assume same row as their name for everything)
3. For column D I need the suburb - this is always in capital letters if that helps
4. For column E I need the state (i.e. the three capital letters such as NSW - there are 8 states comprising of 2 or 3 letters - NSW, VIC, QLD, SA, WA, ACT, NT, TAS )
5. For column F I need the postcode - i.e. the 4 digit number at the end
Everything needs to be in the same row as their name.
thanks in advance
Dr Jan Kay Adamson
House of help
Phone: 02 6111 0999
Address: 44 Davidson Street ALBURY NSW 2685
More Details
Dr Bob Asher
Phone: 02 2222 3222
Address: 3505 George Street SYDNEY NSW 2000
More Details
Dr Stephen Attwell
Phone: 02 98765 7000
Address: Shop 1A 24 Bell AvenueKOGARAH NSW 2217
More Details
As you can see some of them have the names of the business (which take up a row) and some don't. This means they all have varying levels of rows they take up. I need a formula:
1. for column B that somehow extrapolates the phone number and puts it in the same row as their name
2. For column C I need their street number and name (assume same row as their name for everything)
3. For column D I need the suburb - this is always in capital letters if that helps
4. For column E I need the state (i.e. the three capital letters such as NSW - there are 8 states comprising of 2 or 3 letters - NSW, VIC, QLD, SA, WA, ACT, NT, TAS )
5. For column F I need the postcode - i.e. the 4 digit number at the end
Everything needs to be in the same row as their name.
thanks in advance
Last edited by a moderator:
