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]

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?

Post # 6 formula uses " 1." (space # period), now changed to " 1. " (space # period space) per your request:

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)))

 

[jtakw]

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...!

I see you already have a couple of answers in that thread, but I posted a formula there as well.

 

[How_Do_I]

Thanks jtakw... I'm trying to put the 3 versions into my workbook now with IFERROR... It's proving a little difficult but I'm determined to try and do something after you've given me all the solutions...!

 

[jtakw]

You're welcome.

Are you saying you have variations of Text Strings like in Post # 1 and Post # 4 of this thread, And Post # 1 of your Other thread ( "Extract 2..." ) all in the same Column, and you're trying to "Stitch" the 3 formulas together, so it'll work for All 3 scenarios as you copy the formula down ??

 

[How_Do_I]

Yes jtakw, I need to "stich" all the formulas together... I used IFERROR and was getting the issue which I mentioned some where but this seems to be working... There is one issue which I'll mention in the next post...

Excel Workbook
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
20	1. German Long Range Rocket Development. COS(43)189(O). 2. Chemical Warfare. COS(43)188(O). 3. Proposed New Ceiling for AA Command. 4. Establishment of Trans-Atlantic Radio Telephone Circuit - New Project. COS(43)124. 5. Shipment of Mechanical Vehicles.	3																German Long Range Rocket Development. COS(43)189(O).
Sheet1

 

[How_Do_I]

It's this format jtakw... Is there anything that can be done to stop the data in the lasts cells when I drag the formula across please...?

Excel Workbook
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
11	1. Pacific War Council. 2. Floods. 3. India. 4. Move. 5. Development. 6. German. 7. Manning. 8. Operation. 9. Morale of Italian Troops.																	Pacific War Council.	Floods.	India.	Move.	Development.	German.	Manning.	Operation.	Morale of Italian Troops.	s.	s.	s.
Sheet1

 

[ukmikeb]

Re: Post #16 try :-

=IF(ISNUMBER(FIND(COLUMNS($AA11:AA11)&".",$J11)),TRIM(MID(SUBSTITUTE(SUBSTITUTE(" "&$J11," "&COLUMNS($K11:K11)&".",REPT(" ",LEN($J11)))," "&COLUMNS($K11:K11)+1&".",REPT(" ",LEN($J11))),LEN($J11),LEN($J11))),"")

hth

 

[jtakw]

Yes jtakw, I need to "stich" all the formulas together... I used IFERROR and was getting the issue which I mentioned some where but this seems to be working... There is one issue which I'll mention in the next post...

It's this format jtakw... Is there anything that can be done to stop the data in the lasts cells when I drag the formula across please...?

Here's a version of my formulas Stitched/Combined for all the scenarios you've shown without the need of a "Helper Cell" and without the CHOOSE function.

Wherever you place the formula, Be Sure to Adjust All the Columns(reference) to the Cell where the formula resides (i.e. if you place the formula in AA11, change All instances of COLUMNS($C30:C30) in my formula below to COLUMNS($AA11:AA11), this will make the formula Robust against errors due to Column insertions between the Data Cell (which contains the Original Text string), and the formula cell:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
30	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.
31	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
32	Record Type: Memorandum. Former Reference: WP (44) 642. Title: German Leaflet dropped from Flying Bomb. Author: Herbert Morrison.	Memorandum	WP (44) 642	German Leaflet dropped from Flying Bomb	Herbert Morrison.
33	1. German Long Range Rocket Development. COS(43)189(O). 2. Chemical Warfare. COS(43)188(O). 3. Proposed New Ceiling for AA Command. 4. Establishment of Trans-Atlantic Radio Telephone Circuit - New Project. COS(43)124. 5. Shipment of Mechanical Vehicles.	German Long Range Rocket Development. COS(43)189(O).	Chemical Warfare. COS(43)188(O).	Proposed New Ceiling for AA Command.	Establishment of Trans-Atlantic Radio Telephone Circuit - New Project. COS(43)124.	Shipment of Mechanical Vehicles.
34	1. Pacific War Council. 2. Floods. 3. India. 4. Move. 5. Development. 6. German. 7. Manning. 8. Operation. 9. Morale of Italian Troops.	Pacific War Council.	Floods.	India.	Move.	Development.	German.	Manning.	Operation.	Morale of Italian Troops.
Sheet540

Cell Formulas
Range	Formula
C30	=TRIM(MID(IF(ISNUMBER(FIND(" 1. "," "&$B30)),SUBSTITUTE(SUBSTITUTE(" "&IF(ISNUMBER(SEARCH("Agenda:",$B30)),REPLACE($B30,1,SEARCH("Agenda:",$B30)+7,""),$B30)," "&COLUMNS($C30:C30)&". ",REPT(" ",LEN($B30)))," "&COLUMNS($C30:C30)+1&". ",REPT(" ",LEN($B30))),SUBSTITUTE(SUBSTITUTE($B30,": ",REPT(" ",LEN($B30))),". ",REPT(" ",LEN($B30)))),IF(ISNUMBER(FIND(" 1. "," "&$B30)),LEN($B30)+COLUMNS($C30:C30),(COLUMNS($C30:C30)*2-1)*LEN($B30)),LEN($B30)))

Formula copied down and across.

 

[How_Do_I]

Thank you so much jtakw… I've got some time to have a look at your formula in post # 18 now... Thank you too Mike...

 

[jtakw]

You're welcome.