Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull whole words from a cell up to a set number of characters.
How can I pull up to a set number of characters from a cell without cutting
off a word? I have several cells that contain more than 50 characters, but the database I am importing these cells to has a limit of 50 characters for that field. I can put anything over 50 charaters in additional fields, but I do not want to cut off words. For example I have a cell that contains: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM. If I just do a LEFT(A2,50) it leaves off the EAM in CREAM. I want it to leave off the whole word and put it in a different cell. Make sense? Is this possible? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull whole words from a cell up to a set number of characters.
One way - if your data is in A1 and assuming you only have to split it once
(ie your second string will surely have < 50 characters or you don't need to split it), try this in B1: =LEFT(A1,IF(LEN(A1)50,MATCH(50,IF(MID(A1,ROW(INDI RECT("1:"&LEN(A1))),1)=" ", ROW(INDIRECT("1:"&LEN(A1)))))-1,LEN(A1))) array entered with Cntrl+Shift+Enter then in C1 =SUBSTITUTE(A1,B1,"") There will be a leading space in C1. If desired use =TRIM(SUBSTITUTE(A1,B1,"")) instead to remove it If you then have to split the result in C1, you could use the first formula on it. "nmp" wrote: How can I pull up to a set number of characters from a cell without cutting off a word? I have several cells that contain more than 50 characters, but the database I am importing these cells to has a limit of 50 characters for that field. I can put anything over 50 charaters in additional fields, but I do not want to cut off words. For example I have a cell that contains: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM. If I just do a LEFT(A2,50) it leaves off the EAM in CREAM. I want it to leave off the whole word and put it in a different cell. Make sense? Is this possible? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull whole words from a cell up to a set number of characters.
The NG wrapped the post a little funny
&LEN(A1))),1)=" ", There is a space in between the quotes. "JMB" wrote: One way - if your data is in A1 and assuming you only have to split it once (ie your second string will surely have < 50 characters or you don't need to split it), try this in B1: =LEFT(A1,IF(LEN(A1)50,MATCH(50,IF(MID(A1,ROW(INDI RECT("1:"&LEN(A1))),1)=" ", ROW(INDIRECT("1:"&LEN(A1)))))-1,LEN(A1))) array entered with Cntrl+Shift+Enter then in C1 =SUBSTITUTE(A1,B1,"") There will be a leading space in C1. If desired use =TRIM(SUBSTITUTE(A1,B1,"")) instead to remove it If you then have to split the result in C1, you could use the first formula on it. "nmp" wrote: How can I pull up to a set number of characters from a cell without cutting off a word? I have several cells that contain more than 50 characters, but the database I am importing these cells to has a limit of 50 characters for that field. I can put anything over 50 charaters in additional fields, but I do not want to cut off words. For example I have a cell that contains: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM. If I just do a LEFT(A2,50) it leaves off the EAM in CREAM. I want it to leave off the whole word and put it in a different cell. Make sense? Is this possible? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull whole words from a cell up to a set number of characters.
On Wed, 11 Oct 2006 14:04:01 -0700, nmp wrote:
How can I pull up to a set number of characters from a cell without cutting off a word? I have several cells that contain more than 50 characters, but the database I am importing these cells to has a limit of 50 characters for that field. I can put anything over 50 charaters in additional fields, but I do not want to cut off words. For example I have a cell that contains: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM. If I just do a LEFT(A2,50) it leaves off the EAM in CREAM. I want it to leave off the whole word and put it in a different cell. Make sense? Is this possible? Thanks! One way would be to use "Regular Expressions" If your string length will be less than 256 characters, you could download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr Then use this formula: A1: your_string B1: =REGEX.MID($A1,"(?s)\b.{1,50}\b",COLUMNS($A:A)) copy/drag right as needed. Note that the Columns($a:a) parameter is a counter that indicates which instance of up to 50 character strings to return. If you were putting the formula in A2:An, you should change that argument to ROWS($1:1). Or you could just manually enter 1, 2, ... n. If the strings might be longer than 255 characters, a UDF will allow you to use a similar regular expression to accomplish the same thing. I can post that if necessary. Either of the above can also be done in VBA. --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull whole words from a cell up to a set number of characters.
On Wed, 11 Oct 2006 23:05:34 -0400, Ron Rosenfeld
wrote: A1: your_string B1: =REGEX.MID($A1,"(?s)\b.{1,50}\b",COLUMNS($A:A)) For compatibility with the VBA variant, I would make a minor change in the above: =REGEX.MID($A1,"\b[\s\S]{1,50}\b",COLUMNS($A:A)) Either will work with Longre's add-in, but I do not think the first will work for those using the VBScript flavor. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In excel, how do we pull the first letter from words in a sentence | Excel Worksheet Functions | |||
Count Number or words in Cell | Excel Discussion (Misc queries) | |||
How do I pull just the numeric characters in a cell? | Excel Discussion (Misc queries) | |||
How do I count the number of words in a cell? | Excel Worksheet Functions | |||
How many words or characters maximum in a cell? | Excel Discussion (Misc queries) |