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

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

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("Conditio n:",$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

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

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("Conditio n:",$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