+ Reply to Thread
Results 1 to 11 of 11

Excel 2007 formula to extract 90-90 character sentences from a paragraph

  1. 08-11-2023, 06:34 PM #1
    ashokpatidar is offline
    Registered User
    Join Date
    05-09-2013
    Location
    Mysore
    MS-Off Ver
    Excel 2007
    Posts
    10

    Excel 2007 formula to extract 90-90 character sentences from a paragraph

    Paragraph in cell A1: Buy a Arlen Ness Big Sucker Stage 1 Air Kit with Beveled Cover for 17-Up HD Touring Softail and get FREE SHIPPING from our website xyz.com. Order today.

    Expected value in B1: Buy a Arlen Ness Big Sucker Stage 1 Air Kit with Beveled Cover for 17-Up HD Touring
    Expected value in C1: Softail and get FREE SHIPPING from our website xyz.com. Order today.

    What formula should I use in cell B1 and C1 to exactly pull those two 90-90 character sentences without breaking the end word in parts?

    I attempted =LEFT(A1,90), but it is giving the value where the last word is breaking i.e. Buy a Arlen Ness Big Sucker Stage 1 Air Kit with Beveled Cover for 17-Up HD Touring Softai

    I exactly need formula to give me a complete sentence without seeing end word breaking even if it is less than 90 character.
    Reply With QuoteRegister To Reply
  2. 08-11-2023, 07:08 PM #2
    GlennUK is offline
    Forum Contributor
    Join Date
    11-20-2007
    Location
    Felixstowe, England
    MS-Off Ver
    Excel 2013, 2019
    Posts
    217

    Re: Excel 2007 formula to extract 90-90 character sentences from a paragraph

    For B1 use this array formula (entered using Ctrl+Shift+Enter instead of Enter) ... note that Excel puts curly brackets around the formula to indicate that it is an array formula:

    =LEFT(A1,MAX(IF(MID(A1&" ",ROW(INDIRECT("1:91")),1)=" ",ROW(INDIRECT("1:91")),0))-1)

    and for C1 use:
    =TRIM(SUBSTITUTE(A1,B1,""))
    Last edited by GlennUK; 08-11-2023 at 07:22 PM.
    Reply With QuoteRegister To Reply
  3. 08-11-2023, 11:04 PM #3
    menem is offline
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Excel 2007 formula to extract 90-90 character sentences from a paragraph

    Please try

    B1
    =LEFT(LEFT(A1,90),90-LEN(TRIM( RIGHT( SUBSTITUTE(LEFT(A1,90)," ",REPT(" ",90)),90) )))

    C1
    =TRIM(MID(A1,LEN(B1),100))

    Regards.
    My English is very poor, so please be patient >_<"

    My Dynamic Related Dropdown post:
    https://www.excelforum.com/excel-for...ml#post5657376
    https://www.excelforum.com/excel-for...ml#post5655711
    https://www.excelforum.com/excel-for...ml#post5655762
    Reply With QuoteRegister To Reply
  4. 08-12-2023, 03:01 AM #4
    GlennUK is offline
    Forum Contributor
    Join Date
    11-20-2007
    Location
    Felixstowe, England
    MS-Off Ver
    Excel 2013, 2019
    Posts
    217

    Re: Excel 2007 formula to extract 90-90 character sentences from a paragraph

    Quote Originally Posted by menem View Post
    Please try

    B1
    =LEFT(LEFT(A1,90),90-LEN(TRIM( RIGHT( SUBSTITUTE(LEFT(A1,90)," ",REPT(" ",90)),90) )))

    C1
    =TRIM(MID(A1,LEN(B1),100))

    Regards.
    I think that splits before the last word prior to 90 characters when the break is on character 91 ... when the length of the first split part should have been 90 characters.
    Reply With QuoteRegister To Reply
  5. 08-12-2023, 03:49 AM #5
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,021

    Re: Excel 2007 formula to extract 90-90 character sentences from a paragraph

    @menem: I think C1 should be:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Edit:just tested Glenn's suggestion and B1 needs to be:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by TMS; 08-12-2023 at 04:00 AM.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  6. 08-12-2023, 04:36 AM #6
    GlennUK is offline
    Forum Contributor
    Join Date
    11-20-2007
    Location
    Felixstowe, England
    MS-Off Ver
    Excel 2013, 2019
    Posts
    217

    Re: Excel 2007 formula to extract 90-90 character sentences from a paragraph

    Quote Originally Posted by TMS View Post
    @menem: I think C1 should be:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Edit:just tested Glenn's suggestion and B1 needs to be:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    My solution works just fine, whereas yours breaks down if there are double spaces ruining your "count".
    Reply With QuoteRegister To Reply
  7. 08-12-2023, 04:42 AM #7
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,021

    Re: Excel 2007 formula to extract 90-90 character sentences from a paragraph

    . . . whereas yours breaks down if there are double spaces ruining your "count".
    Not my solution, menem's with a couple of tweaks.

    To be fair, I didn't test the double space condition. Not sure how likely that is to occur in a sentence. But can't argue with your comment.


    Edit: actually, having just tested the premise with some randomly inserted double spaces, your solution and menem's tweaked solution appear to produce the same output.
    Last edited by TMS; 08-12-2023 at 04:49 AM.
    Reply With QuoteRegister To Reply
  8. 08-12-2023, 05:11 AM #8
    GlennUK is offline
    Forum Contributor
    Join Date
    11-20-2007
    Location
    Felixstowe, England
    MS-Off Ver
    Excel 2013, 2019
    Posts
    217

    Re: Excel 2007 formula to extract 90-90 character sentences from a paragraph

    Quote Originally Posted by TMS View Post
    ... actually, having just tested the premise with some randomly inserted double spaces, your solution and menem's tweaked solution appear to produce the same output.
    ... except where the break is on character 91.
    Reply With QuoteRegister To Reply
  9. 08-12-2023, 07:44 AM #9
    menem is offline
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Excel 2007 formula to extract 90-90 character sentences from a paragraph

    Ah, yes it's will be wrong if a space is at 91.

    Regards.
    Reply With QuoteRegister To Reply
  10. 08-16-2023, 05:17 PM #10
    ashokpatidar is offline
    Registered User
    Join Date
    05-09-2013
    Location
    Mysore
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Excel 2007 formula to extract 90-90 character sentences from a paragraph

    Quote Originally Posted by GlennUK View Post
    My solution works just fine, whereas yours breaks down if there are double spaces ruining your "count".
    Thanks GlennUK, menem and TMS.

    GlennUK's formula for cell B1 is perfectly working. While TMS's forumula for cell C1 is giving up to 100 character value, but the word is breaking at the end. The last word in C1 should be a full word. Please check screenshot below. In short, can I get similar formula for C1 as for B1?

    Attachment 839792
    Reply With QuoteRegister To Reply
  11. 08-17-2023, 09:00 PM #11
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,829

    Re: Excel 2007 formula to extract 90-90 character sentences from a paragraph

    Selecting attachment 839792 results in the following: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Extract sentences containing a keyword and output results to Excel
    By reddwarf in forum Word Programming / VBA / Macros
    Replies: 7
    Last Post: 11-24-2022, 11:25 AM
  2. Excel. Extract word from sentences
    By erci74 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 10-22-2022, 05:01 PM
  3. vba code to extract 5 sentences from outlook body to excel for emails that have subject li
    By Natashar in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 09-07-2015, 04:05 PM
  4. How Do I break up a paragraph in a cell into newline-delimited sentences in the same cell?
    By rmlowrie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-06-2014, 08:35 AM
  5. [SOLVED] Combine selected sentences as a paragraph and create a message
    By 1tane1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-25-2014, 03:37 AM
  6. Text functions to add X character in between sentences
    By dvpe in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-07-2014, 12:54 PM
  7. [SOLVED] formula to extract text out of a paragraph
    By The Moose in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-10-2006, 11:49 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1