+ Reply to Thread
Results 1 to 5 of 5

formula to extract text out of a paragraph

  1. #1
    The Moose
    Guest

    formula to extract text out of a paragraph

    I have a paragraph in a spreadsheet, like so:

    -----------------------
    1,000 Vegetarian Recipes by: Gelles, Carol DESCRIPTION: ISBN:
    0-02-542965-5 Title: 1,000 Vegetarian Recipes Author: Gelles, Carol
    Category: Cooking, Food Wine : Baking : General Publisher: John Wiley
    Sons Inc Date: May 1996 Format: Hardcover Condition: Used; Very Good
    Seller's Notes: Small smudge on the inside fold of the dustcover.
    Record Number: 328 ABOUT THE BOOK: Book Description Not just for
    vegetarians, there's never been a better time for this award-winning
    book! Carol Gelles, one of t
    -----------------------

    Is there any way to extract the ISBN ('0-02-542965-5' in the example
    above) and the Author ('Gelles, Carol' in the example above) and the
    Format ('Hardcover' in the example above)??

    These columns are extracted from a data-source that cannot be modified.
    All of the paragraphs that will be extracted are not in exactly the
    same format -- some of them say "editor" instead of "author" and, of
    course, the information in the paragraph is all different lengths.

    Thanks.

    Barb


  2. #2
    The Moose
    Guest

    Re: formula to extract text out of a paragraph

    OK. I figured out how to get the ISBN and the FORMAT:

    =FIND("ISBN:",B2) <<--enter this formula in a new "finding" column
    =MID(B2,K2+6,13) <<-- Enter this formula in the ISBN column
    then convert formula to value and delete the "finding" column

    =FIND("Format:",B2) <<--enter this formula in a new "finding" column
    =MID(B2,N2+8,9) <<--enter this formula in the FORMAT column
    then convert formula to value and delete the "finding" column

    What I'm not how trouble with is the Author/Editor column because the
    names are all variable lengths. Still working on it.

    Barb


    The Moose wrote:
    > I have a paragraph in a spreadsheet, like so:
    >
    > -----------------------
    > 1,000 Vegetarian Recipes by: Gelles, Carol DESCRIPTION: ISBN:
    > 0-02-542965-5 Title: 1,000 Vegetarian Recipes Author: Gelles, Carol
    > Category: Cooking, Food Wine : Baking : General Publisher: John Wiley
    > Sons Inc Date: May 1996 Format: Hardcover Condition: Used; Very Good
    > Seller's Notes: Small smudge on the inside fold of the dustcover.
    > Record Number: 328 ABOUT THE BOOK: Book Description Not just for
    > vegetarians, there's never been a better time for this award-winning
    > book! Carol Gelles, one of t
    > -----------------------
    >
    > Is there any way to extract the ISBN ('0-02-542965-5' in the example
    > above) and the Author ('Gelles, Carol' in the example above) and the
    > Format ('Hardcover' in the example above)??
    >
    > These columns are extracted from a data-source that cannot be modified.
    > All of the paragraphs that will be extracted are not in exactly the
    > same format -- some of them say "editor" instead of "author" and, of
    > course, the information in the paragraph is all different lengths.
    >
    > Thanks.
    >
    > Barb



  3. #3
    Toppers
    Guest

    Re: formula to extract text out of a paragraph

    Try:


    ISBN:

    =MID(A1,FIND("ISBN:",A1,1)+5,14)

    Author:

    =MID($A$1,FIND("Author:",$A$1,1)+7,(FIND("Category",$A$1,1)-FIND("Author:",$A$1,1)-7))

    Format:
    =MID($A$1,FIND("Format:",$A$1,1)+7,(FIND("Condition:",$A$1,1)-FIND("Format:",$A$1,1)-7))

    HTH

    "The Moose" wrote:

    > OK. I figured out how to get the ISBN and the FORMAT:
    >
    > =FIND("ISBN:",B2) <<--enter this formula in a new "finding" column
    > =MID(B2,K2+6,13) <<-- Enter this formula in the ISBN column
    > then convert formula to value and delete the "finding" column
    >
    > =FIND("Format:",B2) <<--enter this formula in a new "finding" column
    > =MID(B2,N2+8,9) <<--enter this formula in the FORMAT column
    > then convert formula to value and delete the "finding" column
    >
    > What I'm not how trouble with is the Author/Editor column because the
    > names are all variable lengths. Still working on it.
    >
    > Barb
    >
    >
    > The Moose wrote:
    > > I have a paragraph in a spreadsheet, like so:
    > >
    > > -----------------------
    > > 1,000 Vegetarian Recipes by: Gelles, Carol DESCRIPTION: ISBN:
    > > 0-02-542965-5 Title: 1,000 Vegetarian Recipes Author: Gelles, Carol
    > > Category: Cooking, Food Wine : Baking : General Publisher: John Wiley
    > > Sons Inc Date: May 1996 Format: Hardcover Condition: Used; Very Good
    > > Seller's Notes: Small smudge on the inside fold of the dustcover.
    > > Record Number: 328 ABOUT THE BOOK: Book Description Not just for
    > > vegetarians, there's never been a better time for this award-winning
    > > book! Carol Gelles, one of t
    > > -----------------------
    > >
    > > Is there any way to extract the ISBN ('0-02-542965-5' in the example
    > > above) and the Author ('Gelles, Carol' in the example above) and the
    > > Format ('Hardcover' in the example above)??
    > >
    > > These columns are extracted from a data-source that cannot be modified.
    > > All of the paragraphs that will be extracted are not in exactly the
    > > same format -- some of them say "editor" instead of "author" and, of
    > > course, the information in the paragraph is all different lengths.
    > >
    > > Thanks.
    > >
    > > Barb

    >
    >


  4. #4
    CLR
    Guest

    Re: formula to extract text out of a paragraph

    For author, maybe..........

    =MID(A1,FIND("by:",A1,1)+4,FIND("DESCRIPTION:",A1,1)-8-FIND("by:",A1,1)+4)

    Vaya con Dios,
    Chuck, CABGx3



    "The Moose" wrote:

    > OK. I figured out how to get the ISBN and the FORMAT:
    >
    > =FIND("ISBN:",B2) <<--enter this formula in a new "finding" column
    > =MID(B2,K2+6,13) <<-- Enter this formula in the ISBN column
    > then convert formula to value and delete the "finding" column
    >
    > =FIND("Format:",B2) <<--enter this formula in a new "finding" column
    > =MID(B2,N2+8,9) <<--enter this formula in the FORMAT column
    > then convert formula to value and delete the "finding" column
    >
    > What I'm not how trouble with is the Author/Editor column because the
    > names are all variable lengths. Still working on it.
    >
    > Barb
    >
    >
    > The Moose wrote:
    > > I have a paragraph in a spreadsheet, like so:
    > >
    > > -----------------------
    > > 1,000 Vegetarian Recipes by: Gelles, Carol DESCRIPTION: ISBN:
    > > 0-02-542965-5 Title: 1,000 Vegetarian Recipes Author: Gelles, Carol
    > > Category: Cooking, Food Wine : Baking : General Publisher: John Wiley
    > > Sons Inc Date: May 1996 Format: Hardcover Condition: Used; Very Good
    > > Seller's Notes: Small smudge on the inside fold of the dustcover.
    > > Record Number: 328 ABOUT THE BOOK: Book Description Not just for
    > > vegetarians, there's never been a better time for this award-winning
    > > book! Carol Gelles, one of t
    > > -----------------------
    > >
    > > Is there any way to extract the ISBN ('0-02-542965-5' in the example
    > > above) and the Author ('Gelles, Carol' in the example above) and the
    > > Format ('Hardcover' in the example above)??
    > >
    > > These columns are extracted from a data-source that cannot be modified.
    > > All of the paragraphs that will be extracted are not in exactly the
    > > same format -- some of them say "editor" instead of "author" and, of
    > > course, the information in the paragraph is all different lengths.
    > >
    > > Thanks.
    > >
    > > Barb

    >
    >


  5. #5
    The Moose
    Guest

    Re: formula to extract text out of a paragraph

    Much better than the futzy 'thing' that I came up with :GRIN: Thanks.

    Barb

    Toppers wrote:
    > Try:
    >
    >
    > ISBN:
    >
    > =MID(A1,FIND("ISBN:",A1,1)+5,14)
    >
    > Author:
    >
    > =MID($A$1,FIND("Author:",$A$1,1)+7,(FIND("Category",$A$1,1)-FIND("Author:",$A$1,1)-7))
    >
    > Format:
    > =MID($A$1,FIND("Format:",$A$1,1)+7,(FIND("Condition:",$A$1,1)-FIND("Format:",$A$1,1)-7))
    >
    > HTH
    >
    > "The Moose" wrote:
    >
    > > OK. I figured out how to get the ISBN and the FORMAT:
    > >
    > > =FIND("ISBN:",B2) <<--enter this formula in a new "finding" column
    > > =MID(B2,K2+6,13) <<-- Enter this formula in the ISBN column
    > > then convert formula to value and delete the "finding" column
    > >
    > > =FIND("Format:",B2) <<--enter this formula in a new "finding" column
    > > =MID(B2,N2+8,9) <<--enter this formula in the FORMAT column
    > > then convert formula to value and delete the "finding" column
    > >
    > > What I'm not how trouble with is the Author/Editor column because the
    > > names are all variable lengths. Still working on it.
    > >
    > > Barb
    > >
    > >
    > > The Moose wrote:
    > > > I have a paragraph in a spreadsheet, like so:
    > > >
    > > > -----------------------
    > > > 1,000 Vegetarian Recipes by: Gelles, Carol DESCRIPTION: ISBN:
    > > > 0-02-542965-5 Title: 1,000 Vegetarian Recipes Author: Gelles, Carol
    > > > Category: Cooking, Food Wine : Baking : General Publisher: John Wiley
    > > > Sons Inc Date: May 1996 Format: Hardcover Condition: Used; Very Good
    > > > Seller's Notes: Small smudge on the inside fold of the dustcover.
    > > > Record Number: 328 ABOUT THE BOOK: Book Description Not just for
    > > > vegetarians, there's never been a better time for this award-winning
    > > > book! Carol Gelles, one of t
    > > > -----------------------
    > > >
    > > > Is there any way to extract the ISBN ('0-02-542965-5' in the example
    > > > above) and the Author ('Gelles, Carol' in the example above) and the
    > > > Format ('Hardcover' in the example above)??
    > > >
    > > > These columns are extracted from a data-source that cannot be modified.
    > > > All of the paragraphs that will be extracted are not in exactly the
    > > > same format -- some of them say "editor" instead of "author" and, of
    > > > course, the information in the paragraph is all different lengths.
    > > >
    > > > Thanks.
    > > >
    > > > Barb

    > >
    > >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1