Extract 2...

[How_Do_I]

Hello, can anyone tidy these formulas up for me please... Is there a formula that I could place in the first cell and drag aross the row...?

Excel Workbook
	B	C	D	E	F
21		Record Type:	Former Reference:	Title:	Author:
22	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.
Sheet1

 

[steve the fish]

This should:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A22,":",REPT(" ",LEN($A22))),".",REPT(" ",LEN($A22))),(COLUMNS($A$22:A22)-1)*LEN($A22)*2+LEN($A22),LEN($A22)))

 

[bobsan42]

try this:

=TRIM(MID($B22,FIND(C$21,$B22)+LEN(C$21),IF(ISBLANK(D$21),9999,FIND(D$21,$B22)-FIND(C$21,$B22)-LEN(C$21))))

 

[How_Do_I]

This should:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A22,":",REPT(" ",LEN($A22))),".",REPT(" ",LEN($A22))),(COLUMNS($A$22:A22)-1)*LEN($A22)*2+LEN($A22),LEN($A22)))

Thank you very much steve the fish...!

 

[How_Do_I]

try this:

=TRIM(MID($B22,FIND(C$21,$B22)+LEN(C$21),IF(ISBLANK(D$21),9999,FIND(D$21,$B22)-FIND(C$21,$B22)-LEN(C$21))))

Thank you too bobsan42...

 

[jtakw]

Hi,

Here's another one, formula copied across as far as needed:

Book1
	B	C	D	E	F
22	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
Sheet540

Cell Formulas
Range	Formula
C22	=TRIM(MID(SUBSTITUTE(SUBSTITUTE($B22,": ",REPT(" ",LEN($B22))),". ",REPT(" ",LEN($B22))),(COLUMNS($B$22:B22)*2-1)*LEN($B22),LEN($B22)))

 

[How_Do_I]

Thanks jtakw...

 

[jtakw]

You're welcome.

 

[ukmikeb]

Hi Gus

An alternative formula which combines your original formulae :-

=TRIM(MID($B$22,SEARCH(C$21,$B$22)+LEN(C$21),IF(COLUMNS($C$21:C$21)<counta($c$21:$z$21),search(d$21,$b$22)-search(c$21,$b$22)-len(c$21),len($b$22)-search(c$21,$b$22)+len(c$21))))[ code]
in C22 and dragged across.

hth

Sorry, or  should that be Sarri, about Sundays loss to Man City.</counta($c$21:$z$21),search(d$21,$b$22)-search(c$21,$b$22)-len(c$21),len($b$22)-search(c$21,$b$22)+len(c$21))))[>

 

[ukmikeb]

Gus

The above seems to have mucked up with the editing here is the formula :-

=TRIM(MID($B$22,SEARCH(C$21,$B$22)+LEN(C$21),IF(COLUMNS($C$21:C$21) < COUNTA($C$21:$Z$21),SEARCH(D$21,$B$22)-SEARCH(C$21,$B$22)-LEN(C$21),LEN($B$22)-SEARCH(C$21,$B$22)+LEN(C$21))))