Extract...

[How_Do_I]

Hello all... Is there a way to do this please... I need to extract the topics from my data... So in cell B7 after the word "Agenda"... I'd want the text after "1." but before "2." in C7. Then agenda 2. in D7 etc across the row... I'm assuming numbers in Agenda 1 would mess everything up but I have 1000s of these so anything that helps speed things up would be appreciated…

Excel Workbook
	B	C	D
6		1	2
7	Record Type: Conclusion. Former Reference: WM (44) 51. Attendees: W Churchill; C Attlee; E Bevin; H Morrison; J Anderson; O Lyttelton; Woolton; S Bruce; F Khan Noon; L Amery; J Grigg; S Cripps; R Law; A Cadogan; C Portal; Jammu and Kashmir; Beaverbrook; A Alexander; A Sinclair; B Bracken; Cherwell; A Cunningham; A Brooke. Agenda: 1. His Highness the Maharaja of Kashmir and Sir Firoz Khan Noon; 2. Naval, Military and Air Operations - Air Operations - Home Theatre. 'Crossbow' Targets. Mediterranean - Naval Operations - India. Mediterranean - Military Operations - Russia. Burma. New Guinea; 3. Russia - Loan of British Warships; 4. Foreign Affairs - Greece - Italy - France - Egypt; 5. 'Overlord' Security - Censorship of Diplomatic Communications from this country.	His Highness the Maharaja of Kashmir and Sir Firoz Khan Noon	Naval, Military and Air Operations - Air Operations - Home Theatre. 'Crossbow' Targets. Mediterranean - Naval Operations - India. Mediterranean - Military Operations - Russia. Burma. New Guinea;
Sheet1

 

[jtakw]

Hi,

Formula in C7 copied Across as far as needed, for additional Text Strings in Column B, copy Down and Across as far as needed:

Book1
	B	C	D	E	F	G
7	Record Type: Conclusion. Former Reference: WM (44) 51. Attendees: W Churchill; C Attlee; E Bevin; H Morrison; J Anderson; O Lyttelton; Woolton; S Bruce; F Khan Noon; L Amery; J Grigg; S Cripps; R Law; A Cadogan; C Portal; Jammu and Kashmir; Beaverbrook; A Alexander; A Sinclair; B Bracken; Cherwell; A Cunningham; A Brooke. Agenda: 1. His Highness the Maharaja of Kashmir and Sir Firoz Khan Noon; 2. Naval, Military and Air Operations - Air Operations - Home Theatre. 'Crossbow' Targets. Mediterranean - Naval Operations - India. Mediterranean - Military Operations - Russia. Burma. New Guinea; 3. Russia - Loan of British Warships; 4. Foreign Affairs - Greece - Italy - France - Egypt; 5. 'Overlord' Security - Censorship of Diplomatic Communications from this country.	His Highness the Maharaja of Kashmir and Sir Firoz Khan Noon;	Naval, Military and Air Operations - Air Operations - Home Theatre. 'Crossbow' Targets. Mediterranean - Naval Operations - India. Mediterranean - Military Operations - Russia. Burma. New Guinea;	Russia - Loan of British Warships;	Foreign Affairs - Greece - Italy - France - Egypt;	'Overlord' Security - Censorship of Diplomatic Communications from this country.
Sheet540

Cell Formulas
Range	Formula
C7	=TRIM(MID(SUBSTITUTE(SUBSTITUTE(REPLACE($B7,1,SEARCH("Agenda:",$B7)+7,""),COLUMNS($C7:C7)&".",REPT(" ",LEN($B7))),COLUMNS($C7:C7)+1&".",REPT(" ",LEN($B7))),LEN($B7),LEN($B7)))

 

[How_Do_I]

Thank you very much jtakw, that is working and it's exactly what I'm looking for...!

 

[How_Do_I]

Could anyone help me with this set up please... This time I'm after the text that follows a number and a period, "1.", "2." etc...

Excel Workbook
	B	C	D
24		1	2
25	1. Pacific War Council - Meeting on Tuesday 3rd August 1943. 2. Floods in North East India. 3. India - Appointment of Director of Combined Operations. 4. Move of Additional Chinese Troops to India. 5. Priorities for Research and Development. 6. German Long Range Rocket. 7. Manning of Landing Ships and Craft. 8. Operation 'OVERLORD' - Landing Craft. 9. Morale of Italian Troops in North East Italy, the Balkans, Greece and Aegean. 10. LSI's for 'AVALANCHE'. 11. Action in the Event of Italian collapse. 12. Aircraft for SOE Purposes. 13. 'INGOT' Negotiations	Pacific War Council - Meeting on Tuesday 3rd August 1943.	Floods in North East India.
Sheet1

 

[jtakw]

Thank you very much jtakw, that is working and it's exactly what I'm looking for...!

You're welcome.

 

[jtakw]

C25 formula copied across as far as needed:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
25	1. Pacific War Council - Meeting on Tuesday 3rd August 1943. 2. Floods in North East India. 3. India - Appointment of Director of Combined Operations. 4. Move of Additional Chinese Troops to India. 5. Priorities for Research and Development. 6. German Long Range Rocket. 7. Manning of Landing Ships and Craft. 8. Operation 'OVERLORD' - Landing Craft. 9. Morale of Italian Troops in North East Italy, the Balkans, Greece and Aegean. 10. LSI's for 'AVALANCHE'. 11. Action in the Event of Italian collapse. 12. Aircraft for SOE Purposes. 13. 'INGOT' Negotiations Pacific War Council - Meeting on Tuesday 3rd	Pacific War Council - Meeting on Tuesday 3rd August 1943.	Floods in North East India.	India - Appointment of Director of Combined Operations.	Move of Additional Chinese Troops to India.	Priorities for Research and Development.	German Long Range Rocket.	Manning of Landing Ships and Craft.	Operation 'OVERLORD' - Landing Craft.	Morale of Italian Troops in North East Italy, the Balkans, Greece and Aegean.	LSI's for 'AVALANCHE'.	Action in the Event of Italian collapse.	Aircraft for SOE Purposes.	'INGOT' Negotiations Pacific War Council - Meeting on Tuesday 3rd
Sheet540

Cell Formulas
Range	Formula
C25	=TRIM(MID(SUBSTITUTE(SUBSTITUTE(" "&$B25," "&COLUMNS($C25:C25)&".",REPT(" ",LEN($B25)))," "&COLUMNS($C25:C25)+1&".",REPT(" ",LEN($B25))),LEN($B25),LEN($B25)))

 

[How_Do_I]

Amazing, thank you so much jtakw…

Could I be really cheeky and ask if you could make the formulas I'm using in my other "Extract" post more efficient please... I can't work out how to drag the formula across to get the results I need...

https://www.mrexcel.com/forum/excel-questions/1086993-extract-2-a.html

 

[jtakw]

Tag me to that post, I'll get on it in the morning, it's rather late where I am.

 

[How_Do_I]

Thanks... not sure how I tag you to it but the link is up there in reply #7 ... No rush whatsoever, I have thousands of pieces of data to look at using the two methods you have already shown me...!

 

[How_Do_I]

I've ran in to difficulties with the solution in post # 6 because of extra "." in the data... Is there a way of using " 1. " (space one period space), " 2. " etc please?