+ Reply to Thread
Results 1 to 5 of 5

Formula to delete words from text only if text in over certain amount of characters

  1. 06-02-2015, 10:40 PM #1
    erikpaulsen
    erikpaulsen is offline
    Registered User
    Join Date
    06-02-2015
    Location
    Cali
    MS-Off Ver
    2010
    Posts
    27

    Cool Formula to delete words from text only if text in over certain amount of characters

    This forum is awesome!

    Just like the title says, I need a solution to delete words from text only if text in over certain amount of characters.

    Say I only wanted the contents of the cell to be 68 characters long.

    Example: This forum is freakin awesome and whoever figures this out is the sh!t

    The above example is a total of 70 characters. The desired result would be the following:

    Result: This forum is freakin awesome and whoever figures this out is the

    The result deleted the entire last word to make the quantity of characters fall below 68 avoiding any half spelled words being left behind.

    Hope this makes sense, let me know if you need further clarification.

    Thanks!
    Reply With QuoteRegister To Reply
  2. 06-02-2015, 10:58 PM #2
    Shareez Saleem
    Shareez Saleem is offline
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    696

    Re: Formula to delete words from text only if text in over certain amount of characters

    =left(a1,68), this formula will extract the 68 words/characters including space.
    But to remove the half spelled word, i have no IDEA
    Reply With QuoteRegister To Reply
  3. 06-02-2015, 11:12 PM #3
    FDibbins
    FDibbins is online now
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Formula to delete words from text only if text in over certain amount of characters

    THis is far from perfect, words like it/at/an/on etc at the end may cause a problem, but see if this gets you headed in the right direction. Assume your text is in A1...
    B1=LEFT(A1,FIND("xx",SUBSTITUTE(LEFT(A1,68)," ","xx",LEN(LEFT(A1,68))-LEN(SUBSTITUTE(LEFT(A1,68)," ",""))),1)-1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 06-02-2015, 11:27 PM #4
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to delete words from text only if text in over certain amount of characters

    Try this

    =LEFT(A1,LOOKUP(68,FIND(" ",A1&" ",ROW(INDIRECT("1:"&LEN(A1))))-1))

    Row\Col
    A
    B
    1
    		 This forum is freakin awesome and whoever figures this out is the sh!t This forum is freakin awesome and whoever figures this out is the
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  5. 06-03-2015, 05:21 PM #5
    erikpaulsen
    erikpaulsen is offline
    Registered User
    Join Date
    06-02-2015
    Location
    Cali
    MS-Off Ver
    2010
    Posts
    27

    Re: Formula to delete words from text only if text in over certain amount of characters

    Alkey your solution worked perfectly. Thank you.
    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] Formula to delete certain amount of words from text
    By erikpaulsen in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-02-2015, 07:06 PM
  2. Splitting a long text by words not characters
    By Stwong in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-09-2015, 10:17 PM
  3. [SOLVED] Formula to Delete Text Between 2 Characters
    By Tellm in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-27-2015, 08:12 AM
  4. Convert Numeric to text (amount figure to amount in words)
    By esmond in forum Excel General
    Replies: 3
    Last Post: 08-04-2013, 09:40 PM
  5. Need to add spaces to end of text to reach a specific amount of characters.
    By keshido in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-23-2013, 08:37 PM

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