Extracting specific data from large inconsistent strings

Hello Experts,

I have large data running in row but in single column as follows :-

BELLA TRAVEL 17/34 Redmyre Rd Burwood NSW 5612 Phone:02 4567 1234 Fax: 02 4567 1111 Manager: King Wood Director: Paul Front Email: [email protected] Licence No: 4576
ROLAND TRAVEL
Member Eagle Business Group
Email: [email protected] Phone 02 7645 0956 Fax:7645 4455 Manager: Debbie Short
71 River St NSW 2034 Licence No: 76447956

I am tring to extract data for meaningful use by putting "Phone:" under Phone heading , "Fax:" under FAX heading, "Manager:" under Manager heading, and so for director, Emails and Licence number.
All data is very inconsistent but one thing is common company name are in uppercase and other details end with (semilcolon) :
lease see test2 file as ansample (Excel 2007)
Can you please edit functions?
Thanks
Karnik

Hi Aydeegee,
Thanks for your time and efforts
yes as rightly stated I need to look in to VB more closely, I have borrowed VB book for Dummies and hopefully I will be able have some direction.
Thank you for your suggestion
Cheers
Karnik

Hi Karnik

Good luck with your VB book!

I just love to prove a point - and I was right - there is no quick fix!

However, I have gone some way to getting a solution (in that it works for the 4 samples that you have given).

I have got round the problem of having no order in the file. BUT my system relies on the name being in CAPITALS, followed either by a group (NOT in capitals) or the address (NOT in capitals). For each line with a group, you have to mark the end of the group and beginning of the address with a carat (^) This has to be done before running the program. If the group is placed anywhere else, then tough!

If you look on the Replace sheet you will see my attempts at dealing with Manager / Managers, Licence No: / Licence No. / Licence / No.

If you copy your data on to any sheet (except Replace(!) ) and run the macro (press Ctrl+Shift+Q ) you will see how it works. (Be aware that my macro changes the data so make sure that you do not work on data that has not been backed up!)

To see the code, press Alt+F8 > Step into

If you want any of the logic or VBA explaining - let me know.

Regards
Alastair

Hi IcestationZbra,
I appreciate your time and efforts, your suggestion and function/array works perfectly, it saved numerous man hours !
Thanks genius,
Cheers
Karnik