| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditional formula: sum a range if text present in another range | Excel Discussion (Misc queries) | |||
| COUNTIF formula multiple text exclusions | Excel Discussion (Misc queries) | |||
| formula OR text | Excel Discussion (Misc queries) | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
