Formula's to extrapolate phone number and address from a list

[sobrien1234]

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

 

[Peter_SSs]

Hi Peter, points noted.

I'm not sure they have been. What I pointed you to states

Likewise, please do not answer questions by creating solutions elsewhere and then referencing those solutions via file links. We ask that you answer the question within the thread itself (note: providing links to existing articles on the internet is acceptable).

To that effect, do not simply create a file with a solution, and provide a link to that. Some of the issues with providing links to files uploaded to the internet are: those links often expire, many users cannot download files from file-sharing sites due to network security restrictions, many users are reluctant to download files for personal security reasons and the ability to search the forum effectively is reduced.

 

[sobrien1234]

Thanks for trying. I will see what I can do with the formula's you gave me. Might need a manual touch. Thanks again!!

 

[vicedo]

glad i could help

 

[Peter_SSs]

Thanks Peter - is there anyway of getting all the data on the same row and fixing the errors?

Possibly. Can you post some small (dummy) sample data and the expected results and layout? It's best if you can do that directly in the forum. My signature block below has a link for suggestions for how to do that.

Would it be acceptable to have a macro solution if it turns out not to be feasible with formulas?