How do I limit words in a cell

**emosquito** · 09-28-2010, 06:04 PM

Hi I have a string of words that I would want to limit to 12 Characters by deleting the last words

For example "Hi how are you doing" should be "Hi how are"

How would I do this ?

**Richard Buttrey** · 09-28-2010, 06:11 PM

Hi,

Are you wanting to delete complete words leaving a maximum of 12 characters and perhaps fewer as in this example where 10 characters cover complete words?

Are you counting spaces as a character?

Rgds

**emosquito** · 09-28-2010, 06:19 PM

I want to leave a max of 12 characters,
and let it delete the words that run over 12+ so that the final string could be less than 12

"Hello how do you do sir" should become "Hello how do"

and

"That elephant is large" should become "That" not "That elephan"

**JBeaucaire** · 09-28-2010, 06:28 PM

No one has mentioned yet, this can only be done in a cell by activating some VBA on the sheet, a ws_change event watchdogging that particular column.

Is VBA OK? Not everyone wants macros enabled on their workbooks...

What column? Starting at what row?

**emosquito** · 09-29-2010, 11:51 AM

Ok I solved it using some nifty stuff I found, it needed 2 cell instead of 1 but it worked:

Assuming the info was in A1

Cell B1 Formula (This gets rid of words after character #13 and if a word is going through position 13 it displays that word):

=IF(LEN(A1)>12,(RIGHT(LEFT(A1,13),LEN(LEFT(A1,13))-FIND("^^",SUBSTITUTE(LEFT(A1,13)," ","^^",LEN(LEFT(A1,13))
-LEN(SUBSTITUTE(LEFT(A1,13)," ","")))))),"")

Cell C1 Formula (This subtracts the word spit out from b1 from a1):

=LEFT(LEFT(A1,12),((LEN(LEFT(A1,13)))-(LEN(B1))))

**JBeaucaire** · 09-29-2010, 12:01 PM

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

**alfie4030** · 06-16-2021, 03:40 AM

Originally Posted by emosquito

Ok I solved it using some nifty stuff I found, it needed 2 cell instead of 1 but it worked:

Assuming the info was in A1

Cell B1 Formula (This gets rid of words after character #13 and if a word is going through position 13 it displays that word):

=IF(LEN(A1)>12,(RIGHT(LEFT(A1,13),LEN(LEFT(A1,13))-FIND("^^",SUBSTITUTE(LEFT(A1,13)," ","^^",LEN(LEFT(A1,13))
-LEN(SUBSTITUTE(LEFT(A1,13)," ","")))))),"")

Cell C1 Formula (This subtracts the word spit out from b1 from a1):

=LEFT(LEFT(A1,12),((LEN(LEFT(A1,13)))-(LEN(B1))))

hey just wanna ask if you can show the words that are in excess. not just "that"

**Glenn Kennedy** · 06-16-2021, 05:51 AM

Hi. This is a VERY old thread. Please prepare a sample file (see yellow banner - top of page) with some expected results and start a NEW thread please. I am closing this thread, now.