Thanks:  0

1. ## Extract 2...

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

Sheet1

 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.

 Cell Formula C22 =TRIM(MID(\$B\$22,SEARCH(C21,\$B\$22)+LEN(C21),SEARCH(D21,\$B\$22)-SEARCH(C21,\$B\$22)-LEN(C21))) D22 =TRIM(MID(\$B\$22,SEARCH(D21,\$B\$22)+LEN(D21),SEARCH(E21,\$B\$22)-SEARCH(D21,\$B\$22)-LEN(D21))) E22 =TRIM(MID(\$B\$22,SEARCH(E21,\$B\$22)+LEN(E21),SEARCH(F21,\$B\$22)-SEARCH(E21,\$B\$22)-LEN(E21))) F22 =TRIM(MID(\$B\$22,SEARCH(F21,\$B\$22)+LEN(F21),LEN(\$B\$22)-SEARCH(F21,\$B\$22)+LEN(F21)))

Excel tables to the web >> Excel Jeanie HTML 4

2. ## Re: Extract 2...

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

3. ## Re: Extract 2...

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

4. ## Re: Extract 2...

Originally Posted by 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)))
Thank you very much steve the fish...!

5. ## Re: Extract 2...

Originally Posted by 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))))
Thank you too bobsan42...

6. ## Re: Extract 2...

Hi,

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

BCDEF
22Record Type: Memorandum. Former Reference: WP (44) 642. Title: German Leaflet dropped from Flying Bomb. Author: Herbert Morrison.MemorandumWP (44) 642German Leaflet dropped from Flying BombHerbert Morrison

Sheet540

Worksheet Formulas
CellFormula
C22=TRIM(MID(SUBSTITUTE(SUBSTITUTE(\$B22,": ",REPT(" ",LEN(\$B22))),". ",REPT(" ",LEN(\$B22))),(COLUMNS(\$B\$22:B22)*2-1)*LEN(\$B22),LEN(\$B22)))

7. ## Re: Extract 2...

Thanks jtakw...

8. ## Re: Extract 2...

You're welcome.

9. ## Re: Extract 2...

Hi Gus

An alternative formula which combines your original formulae :-
[CODE]=TRIM(MID(\$B\$22,SEARCH(C\$21,\$B\$22)+LEN(C\$21),IF(COLUMNS(\$C\$21:C\$21) in C22 and dragged across.

hth

Sorry, or should that be Sarri, about Sundays loss to Man City.

10. ## Re: Extract 2...

Gus

The above seems to have mucked up with the editing here is the formula :-
Code:
`=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))))`