Thanks Thanks:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Extract 2...

  1. #1
    Board Regular How_Do_I's Avatar
    Join Date
    Oct 2009
    Posts
    1,747
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

     BCDEF
    21 Record Type:Former Reference:Title:Author:
    22Record 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.

    Spreadsheet Formulas
    CellFormula
    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
    Xl 2010

  2. #2
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    6,990
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    2 Thread(s)

    Default 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. #3
    Board Regular bobsan42's Avatar
    Join Date
    Jul 2010
    Location
    Bulgaria, GMT+2 (42.891813,25.313594)
    Posts
    1,128
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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))))
    "...it's sad that in our blindness we gather thorns for flowers..."
    mostly using:
    windows 7 +10 (64-bit) / excel 2013 +2016 (32-bit) / access 2013 +2016 (32-bit) / some imagination & Google of course
    You don't need to read between the lines - just read them all!

  4. #4
    Board Regular How_Do_I's Avatar
    Join Date
    Oct 2009
    Posts
    1,747
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract 2...

    Quote Originally Posted by steve the fish View Post
    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...!
    Xl 2010

  5. #5
    Board Regular How_Do_I's Avatar
    Join Date
    Oct 2009
    Posts
    1,747
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract 2...

    Quote Originally Posted by bobsan42 View Post
    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...
    Xl 2010

  6. #6
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,031
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    2 Thread(s)

    Default 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. #7
    Board Regular How_Do_I's Avatar
    Join Date
    Oct 2009
    Posts
    1,747
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract 2...

    Thanks jtakw...
    Xl 2010

  8. #8
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,031
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Extract 2...

    You're welcome.

  9. #9
    Board Regular
    Join Date
    Jul 2009
    Location
    Woking UK
    Posts
    2,755
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Last edited by ukmikeb; Feb 12th, 2019 at 03:43 AM.
    Mike

    -----------------------------------------------
    Some solutions don't require an IF!

    When posting code wrap your code between [CODE] tags, eg. [ code ] your code [ /code] - no spaces .

    Paste your Excel data...

    MrExcel HTML Maker or Excel Jeanie

  10. #10
    Board Regular
    Join Date
    Jul 2009
    Location
    Woking UK
    Posts
    2,755
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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))))
    Last edited by ukmikeb; Feb 12th, 2019 at 03:50 AM.
    Mike

    -----------------------------------------------
    Some solutions don't require an IF!

    When posting code wrap your code between [CODE] tags, eg. [ code ] your code [ /code] - no spaces .

    Paste your Excel data...

    MrExcel HTML Maker or Excel Jeanie

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •