Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull a set number of characters from a cell without cutting off a
I need to be able to pull a set number of characters from a cell, but I do
not what to cut off a word if that set number ends up in the middle of a word. For example I have: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM . By using a LEFT(A2,50) it cuts off the EAM in CREAM. I actually want to cut off the whole word and put it in a different cell. Make sense? Is that possible? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull a set number of characters from a cell without cutting off a
I think the difficulty would be in recognising whether or not the last
"word" is a full word or part of a word. For example, if =LEFT(A2,50) ended in "HOT", is that the word "HOT" or part of the word "HOTEL" or something even longer. And if the other data in the cell were shorter, "CREAM" could come at the end of the data and should be retained. You might be able to put together a User Defined Function (UDF) that truncates the data to 50 characters, makes use of the InStrRev function to find the last space in the data, extract the last word (in the truncated data) and look it up in a table of "valid" words. In short, I don't think it would be a simple task. Regards Trevor "nmp" wrote in message ... I need to be able to pull a set number of characters from a cell, but I do not what to cut off a word if that set number ends up in the middle of a word. For example I have: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM . By using a LEFT(A2,50) it cuts off the EAM in CREAM. I actually want to cut off the whole word and put it in a different cell. Make sense? Is that possible? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull a set number of characters from a cell without cutting off a
This will work, but it is likely not the best solution. It looks at the
right most value of the 50 charaters starting from the left. Biggest problem is that there are only 7 options, so if a word has more than seven letters you are out of luck! =IF(RIGHT(LEFT(A2,50),1)=" ",LEFT(A2,49),IF(RIGHT(LEFT(A2,49),1)=" ",LEFT(A2,48),IF(RIGHT(LEFT(A2,48),1)=" ",LEFT(A2,47),IF(RIGHT(LEFT(A2,47),1)=" ",LEFT(A2,46),IF(RIGHT(LEFT(A2,46),1)=" ",LEFT(A2,45),IF(RIGHT(LEFT(A2,45),1)=" ",LEFT(A2,44),LEFT(A2,43))))))) -- JNW "nmp" wrote: I need to be able to pull a set number of characters from a cell, but I do not what to cut off a word if that set number ends up in the middle of a word. For example I have: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM . By using a LEFT(A2,50) it cuts off the EAM in CREAM. I actually want to cut off the whole word and put it in a different cell. Make sense? Is that possible? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull a set number of characters from a cell without cutting off a
Possibly:
Public Function TruncateOnWholeWord(SampleText As Variant, Optional MaxLength As Long = 50) As String 'No problem, just return the input If Len(SampleText) <= MaxLength Then TruncateOnWholeWord = SampleText Exit Function End If 'If the 51st char is a [SPACE], then we can take the first 50 chars OK 'And other tests for what you consider the End-Of-Word char If Mid(SampleText, 51, 1) = " " Then TruncateOnWholeWord = Left(SampleText, 50) Exit Function End If 'OK, need to do some work 'Find the first [SPACE] before the 51st char TruncateOnWholeWord = Left(SampleText, InStrRev(SampleText, " ", 51) - 1) End Function Depending on your requirements, you can add Trim to strip spaces NickHK "nmp" wrote in message ... I need to be able to pull a set number of characters from a cell, but I do not what to cut off a word if that set number ends up in the middle of a word. For example I have: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM .. By using a LEFT(A2,50) it cuts off the EAM in CREAM. I actually want to cut off the whole word and put it in a different cell. Make sense? Is that possible? Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull a set number of characters from a cell without cutting off a
On Wed, 11 Oct 2006 14:07:01 -0700, nmp wrote:
I need to be able to pull a set number of characters from a cell, but I do not what to cut off a word if that set number ends up in the middle of a word. For example I have: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM . By using a LEFT(A2,50) it cuts off the EAM in CREAM. I actually want to cut off the whole word and put it in a different cell. Make sense? Is that possible? Thanks! Posting in multiple threads is confusing. You have two solutions posted about five hours earlier than this. Was there a problem with those solutions? --ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull a set number of characters from a cell without cutting off a
On Wed, 11 Oct 2006 14:07:01 -0700, nmp wrote:
I need to be able to pull a set number of characters from a cell, but I do not what to cut off a word if that set number ends up in the middle of a word. For example I have: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM . By using a LEFT(A2,50) it cuts off the EAM in CREAM. I actually want to cut off the whole word and put it in a different cell. Make sense? Is that possible? Thanks! OOps, sorry, the solutions in the other thread were posted later than this one. In any event, multiple postings for the same question tend to fragment your responses. And there are two other solutions in the other thread. Here's mine: 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 --ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull a set number of characters from a cell without cutting off a
On Thu, 12 Oct 2006 06:49:11 -0400, Ron Rosenfeld
wrote: On Wed, 11 Oct 2006 14:07:01 -0700, nmp wrote: I need to be able to pull a set number of characters from a cell, but I do not what to cut off a word if that set number ends up in the middle of a word. For example I have: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM . By using a LEFT(A2,50) it cuts off the EAM in CREAM. I actually want to cut off the whole word and put it in a different cell. Make sense? Is that possible? Thanks! OOps, sorry, the solutions in the other thread were posted later than this one. In any event, multiple postings for the same question tend to fragment your responses. And there are two other solutions in the other thread. Here's mine: 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 --ron 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 --ron |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull a set number of characters from a cell without cutting of
Ron,
I did not post in multiple threads on purpose. When I clicked on POST for the first one it gave me an error so I posted again. I guess the first one actually went through even though it said it didn't. My apologies! Now I need to see if I can get one of these solutions to work! Thanks! "Ron Rosenfeld" wrote: On Wed, 11 Oct 2006 14:07:01 -0700, nmp wrote: I need to be able to pull a set number of characters from a cell, but I do not what to cut off a word if that set number ends up in the middle of a word. For example I have: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM . By using a LEFT(A2,50) it cuts off the EAM in CREAM. I actually want to cut off the whole word and put it in a different cell. Make sense? Is that possible? Thanks! Posting in multiple threads is confusing. You have two solutions posted about five hours earlier than this. Was there a problem with those solutions? --ron |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull a set number of characters from a cell without cutting of
OK. I'm not that great at VBA and macros. Any chance you can step me
through exactly what I need to do if my data is in E2 through E1587? Here is some more detail on what I have: I have 3 fields available for my description. Description Field 1 needs to be limited to 50 characters. Anything over 50 characters I want to break on the whole word and put in Description Field 2 which needs to be limited to 40 characters. There are a few descriptions that are over 90 characters long, in which case I want to put anything remaining in Description Field 3. Field 3 needs to be limited to 40 characters too, but I do not have any descriptions over 130 characters. Currently the full descriptions are all in column E. Hope this helps. Thanks! "NickHK" wrote: Possibly: Public Function TruncateOnWholeWord(SampleText As Variant, Optional MaxLength As Long = 50) As String 'No problem, just return the input If Len(SampleText) <= MaxLength Then TruncateOnWholeWord = SampleText Exit Function End If 'If the 51st char is a [SPACE], then we can take the first 50 chars OK 'And other tests for what you consider the End-Of-Word char If Mid(SampleText, 51, 1) = " " Then TruncateOnWholeWord = Left(SampleText, 50) Exit Function End If 'OK, need to do some work 'Find the first [SPACE] before the 51st char TruncateOnWholeWord = Left(SampleText, InStrRev(SampleText, " ", 51) - 1) End Function Depending on your requirements, you can add Trim to strip spaces NickHK "nmp" wrote in message ... I need to be able to pull a set number of characters from a cell, but I do not what to cut off a word if that set number ends up in the middle of a word. For example I have: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM .. By using a LEFT(A2,50) it cuts off the EAM in CREAM. I actually want to cut off the whole word and put it in a different cell. Make sense? Is that possible? Thanks! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull a set number of characters from a cell without cutting of
If you don't want to go Ron's RegEx route:
For the subsequent fields, you can pass the length of the string returned from the previous call as the value of the StartPos parament. Adjust the value of MaxLength as required. SampleText remans the same for all calls. Public Function TruncateOnWholeWord2(SampleText As Variant, Optional MaxLength As Long = 50, Optional StartPos As Long = 1) As String Dim TempStr As String TempStr = Mid(SampleText, StartPos) 'No problem, just return the input If Len(TempStr) <= MaxLength Then TruncateOnWholeWord2 = TempStr Exit Function End If 'OK, need to do some work 'Find the first [SPACE] before the MaxLength +1 char TruncateOnWholeWord2 = Left(TempStr, InStrRev(TempStr, " ", MaxLength + 1) - 1) End Function As for using it, with you first value in E2 F2: =TruncateOnWholeWord2(E2,50) G2: =TruncateOnWholeWord2(E2,40,len(F2)) H2: =TruncateOnWholeWord2(E2,40,Len(F2)+len(G2)) NickHK "nmp" wrote in message ... OK. I'm not that great at VBA and macros. Any chance you can step me through exactly what I need to do if my data is in E2 through E1587? Here is some more detail on what I have: I have 3 fields available for my description. Description Field 1 needs to be limited to 50 characters. Anything over 50 characters I want to break on the whole word and put in Description Field 2 which needs to be limited to 40 characters. There are a few descriptions that are over 90 characters long, in which case I want to put anything remaining in Description Field 3. Field 3 needs to be limited to 40 characters too, but I do not have any descriptions over 130 characters. Currently the full descriptions are all in column E. Hope this helps. Thanks! "NickHK" wrote: Possibly: Public Function TruncateOnWholeWord(SampleText As Variant, Optional MaxLength As Long = 50) As String 'No problem, just return the input If Len(SampleText) <= MaxLength Then TruncateOnWholeWord = SampleText Exit Function End If 'If the 51st char is a [SPACE], then we can take the first 50 chars OK 'And other tests for what you consider the End-Of-Word char If Mid(SampleText, 51, 1) = " " Then TruncateOnWholeWord = Left(SampleText, 50) Exit Function End If 'OK, need to do some work 'Find the first [SPACE] before the 51st char TruncateOnWholeWord = Left(SampleText, InStrRev(SampleText, " ", 51) - 1) End Function Depending on your requirements, you can add Trim to strip spaces NickHK "nmp" wrote in message ... I need to be able to pull a set number of characters from a cell, but I do not what to cut off a word if that set number ends up in the middle of a word. For example I have: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM .. By using a LEFT(A2,50) it cuts off the EAM in CREAM. I actually want to cut off the whole word and put it in a different cell. Make sense? Is that possible? Thanks! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull a set number of characters from a cell without cutting of
I didn't want to use Ron's because I didn't want to have to install anything.
My company is pretty protective about that kind of stuff. Anyway, maybe I should have said that I really don't know much at all about VBA and macros! The extent of my experience is just recording macros. I don't understand half of the words you used below. I took what you have below and pasted it in the VB Editor of my sheet and saved it. Is that all I need to do? I then put the first formula in F2 but it gave me a Compile Error Expected: Identifier. What did I do wrong? "NickHK" wrote: If you don't want to go Ron's RegEx route: For the subsequent fields, you can pass the length of the string returned from the previous call as the value of the StartPos parament. Adjust the value of MaxLength as required. SampleText remans the same for all calls. Public Function TruncateOnWholeWord2(SampleText As Variant, Optional MaxLength As Long = 50, Optional StartPos As Long = 1) As String Dim TempStr As String TempStr = Mid(SampleText, StartPos) 'No problem, just return the input If Len(TempStr) <= MaxLength Then TruncateOnWholeWord2 = TempStr Exit Function End If 'OK, need to do some work 'Find the first [SPACE] before the MaxLength +1 char TruncateOnWholeWord2 = Left(TempStr, InStrRev(TempStr, " ", MaxLength + 1) - 1) End Function As for using it, with you first value in E2 F2: =TruncateOnWholeWord2(E2,50) G2: =TruncateOnWholeWord2(E2,40,len(F2)) H2: =TruncateOnWholeWord2(E2,40,Len(F2)+len(G2)) NickHK "nmp" wrote in message ... OK. I'm not that great at VBA and macros. Any chance you can step me through exactly what I need to do if my data is in E2 through E1587? Here is some more detail on what I have: I have 3 fields available for my description. Description Field 1 needs to be limited to 50 characters. Anything over 50 characters I want to break on the whole word and put in Description Field 2 which needs to be limited to 40 characters. There are a few descriptions that are over 90 characters long, in which case I want to put anything remaining in Description Field 3. Field 3 needs to be limited to 40 characters too, but I do not have any descriptions over 130 characters. Currently the full descriptions are all in column E. Hope this helps. Thanks! "NickHK" wrote: Possibly: Public Function TruncateOnWholeWord(SampleText As Variant, Optional MaxLength As Long = 50) As String 'No problem, just return the input If Len(SampleText) <= MaxLength Then TruncateOnWholeWord = SampleText Exit Function End If 'If the 51st char is a [SPACE], then we can take the first 50 chars OK 'And other tests for what you consider the End-Of-Word char If Mid(SampleText, 51, 1) = " " Then TruncateOnWholeWord = Left(SampleText, 50) Exit Function End If 'OK, need to do some work 'Find the first [SPACE] before the 51st char TruncateOnWholeWord = Left(SampleText, InStrRev(SampleText, " ", 51) - 1) End Function Depending on your requirements, you can add Trim to strip spaces NickHK "nmp" wrote in message ... I need to be able to pull a set number of characters from a cell, but I do not what to cut off a word if that set number ends up in the middle of a word. For example I have: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM .. By using a LEFT(A2,50) it cuts off the EAM in CREAM. I actually want to cut off the whole word and put it in a different cell. Make sense? Is that possible? Thanks! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull a set number of characters from a cell without cutting of
You need to add a module to the workbook.
In the VBE, right click on the document tree for your workbook, select InsertModule. Paste the function code below. Then you can call the function from a cell as described and copy down your data, as with Excel's built in functions. NickHK "nmp" wrote in message ... I didn't want to use Ron's because I didn't want to have to install anything. My company is pretty protective about that kind of stuff. Anyway, maybe I should have said that I really don't know much at all about VBA and macros! The extent of my experience is just recording macros. I don't understand half of the words you used below. I took what you have below and pasted it in the VB Editor of my sheet and saved it. Is that all I need to do? I then put the first formula in F2 but it gave me a Compile Error Expected: Identifier. What did I do wrong? "NickHK" wrote: If you don't want to go Ron's RegEx route: For the subsequent fields, you can pass the length of the string returned from the previous call as the value of the StartPos parament. Adjust the value of MaxLength as required. SampleText remans the same for all calls. Public Function TruncateOnWholeWord2(SampleText As Variant, Optional MaxLength As Long = 50, Optional StartPos As Long = 1) As String Dim TempStr As String TempStr = Mid(SampleText, StartPos) 'No problem, just return the input If Len(TempStr) <= MaxLength Then TruncateOnWholeWord2 = TempStr Exit Function End If 'OK, need to do some work 'Find the first [SPACE] before the MaxLength +1 char TruncateOnWholeWord2 = Left(TempStr, InStrRev(TempStr, " ", MaxLength + 1) - 1) End Function As for using it, with you first value in E2 F2: =TruncateOnWholeWord2(E2,50) G2: =TruncateOnWholeWord2(E2,40,len(F2)) H2: =TruncateOnWholeWord2(E2,40,Len(F2)+len(G2)) NickHK "nmp" wrote in message ... OK. I'm not that great at VBA and macros. Any chance you can step me through exactly what I need to do if my data is in E2 through E1587? Here is some more detail on what I have: I have 3 fields available for my description. Description Field 1 needs to be limited to 50 characters. Anything over 50 characters I want to break on the whole word and put in Description Field 2 which needs to be limited to 40 characters. There are a few descriptions that are over 90 characters long, in which case I want to put anything remaining in Description Field 3. Field 3 needs to be limited to 40 characters too, but I do not have any descriptions over 130 characters. Currently the full descriptions are all in column E. Hope this helps. Thanks! "NickHK" wrote: Possibly: Public Function TruncateOnWholeWord(SampleText As Variant, Optional MaxLength As Long = 50) As String 'No problem, just return the input If Len(SampleText) <= MaxLength Then TruncateOnWholeWord = SampleText Exit Function End If 'If the 51st char is a [SPACE], then we can take the first 50 chars OK 'And other tests for what you consider the End-Of-Word char If Mid(SampleText, 51, 1) = " " Then TruncateOnWholeWord = Left(SampleText, 50) Exit Function End If 'OK, need to do some work 'Find the first [SPACE] before the 51st char TruncateOnWholeWord = Left(SampleText, InStrRev(SampleText, " ", 51) - 1) End Function Depending on your requirements, you can add Trim to strip spaces NickHK "nmp" wrote in message ... I need to be able to pull a set number of characters from a cell, but I do not what to cut off a word if that set number ends up in the middle of a word. For example I have: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM .. By using a LEFT(A2,50) it cuts off the EAM in CREAM. I actually want to cut off the whole word and put it in a different cell. Make sense? Is that possible? Thanks! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull a set number of characters from a cell without cutting of
I already did that. When you open the VBE it automatically opens a module.
I copied and pasted what you have below and I am still getting the Compile Error Expected: Identifier. "NickHK" wrote: You need to add a module to the workbook. In the VBE, right click on the document tree for your workbook, select InsertModule. Paste the function code below. Then you can call the function from a cell as described and copy down your data, as with Excel's built in functions. NickHK "nmp" wrote in message ... I didn't want to use Ron's because I didn't want to have to install anything. My company is pretty protective about that kind of stuff. Anyway, maybe I should have said that I really don't know much at all about VBA and macros! The extent of my experience is just recording macros. I don't understand half of the words you used below. I took what you have below and pasted it in the VB Editor of my sheet and saved it. Is that all I need to do? I then put the first formula in F2 but it gave me a Compile Error Expected: Identifier. What did I do wrong? "NickHK" wrote: If you don't want to go Ron's RegEx route: For the subsequent fields, you can pass the length of the string returned from the previous call as the value of the StartPos parament. Adjust the value of MaxLength as required. SampleText remans the same for all calls. Public Function TruncateOnWholeWord2(SampleText As Variant, Optional MaxLength As Long = 50, Optional StartPos As Long = 1) As String Dim TempStr As String TempStr = Mid(SampleText, StartPos) 'No problem, just return the input If Len(TempStr) <= MaxLength Then TruncateOnWholeWord2 = TempStr Exit Function End If 'OK, need to do some work 'Find the first [SPACE] before the MaxLength +1 char TruncateOnWholeWord2 = Left(TempStr, InStrRev(TempStr, " ", MaxLength + 1) - 1) End Function As for using it, with you first value in E2 F2: =TruncateOnWholeWord2(E2,50) G2: =TruncateOnWholeWord2(E2,40,len(F2)) H2: =TruncateOnWholeWord2(E2,40,Len(F2)+len(G2)) NickHK "nmp" wrote in message ... OK. I'm not that great at VBA and macros. Any chance you can step me through exactly what I need to do if my data is in E2 through E1587? Here is some more detail on what I have: I have 3 fields available for my description. Description Field 1 needs to be limited to 50 characters. Anything over 50 characters I want to break on the whole word and put in Description Field 2 which needs to be limited to 40 characters. There are a few descriptions that are over 90 characters long, in which case I want to put anything remaining in Description Field 3. Field 3 needs to be limited to 40 characters too, but I do not have any descriptions over 130 characters. Currently the full descriptions are all in column E. Hope this helps. Thanks! "NickHK" wrote: Possibly: Public Function TruncateOnWholeWord(SampleText As Variant, Optional MaxLength As Long = 50) As String 'No problem, just return the input If Len(SampleText) <= MaxLength Then TruncateOnWholeWord = SampleText Exit Function End If 'If the 51st char is a [SPACE], then we can take the first 50 chars OK 'And other tests for what you consider the End-Of-Word char If Mid(SampleText, 51, 1) = " " Then TruncateOnWholeWord = Left(SampleText, 50) Exit Function End If 'OK, need to do some work 'Find the first [SPACE] before the 51st char TruncateOnWholeWord = Left(SampleText, InStrRev(SampleText, " ", 51) - 1) End Function Depending on your requirements, you can add Trim to strip spaces NickHK "nmp" wrote in message ... I need to be able to pull a set number of characters from a cell, but I do not what to cut off a word if that set number ends up in the middle of a word. For example I have: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM .. By using a LEFT(A2,50) it cuts off the EAM in CREAM. I actually want to cut off the whole word and put it in a different cell. Make sense? Is that possible? Thanks! |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull a set number of characters from a cell without cutting of
On Thu, 12 Oct 2006 06:15:02 -0700, nmp wrote:
Ron, I did not post in multiple threads on purpose. When I clicked on POST for the first one it gave me an error so I posted again. I guess the first one actually went through even though it said it didn't. My apologies! Now I need to see if I can get one of these solutions to work! Thanks! I see in another message that there is an issue with downloading add-ins. Well, the same principle can be used through VBA. The formula is similar: A1: Your String B1: =remid($A1,"\b[\s\S]{1,50}\b",COLUMNS($A:A)) Before using the formula: <alt-F11 to open the VB Editor **Ensure your project is highlighted in the Project Explorer Window.** Tools/References Select Microsoft VBScript Regular Expressions 5.5 (it could be anywhere in a long list; don't use 1.0) **Insert/Module** Paste code below into the window that opens '============================================== Option Explicit Function REMid(str As String, Pattern As String, _ Optional Index As Variant = 1, _ Optional CaseSensitive As Boolean = True) _ As Variant 'Variant as value may be string or array Dim objRegExp As RegExp Dim objMatch As Match Dim colMatches As MatchCollection Dim i As Long 'counter Dim t() As String 'container for array results ' Create a regular expression object. Set objRegExp = New RegExp 'Set the pattern by using the Pattern property. objRegExp.Pattern = Pattern ' Set Case Insensitivity. objRegExp.IgnoreCase = Not CaseSensitive 'Set global applicability. objRegExp.Global = True 'Test whether the String can be compared. If (objRegExp.Test(str) = True) Then 'Get the matches. Set colMatches = objRegExp.Execute(str) ' Execute search. On Error Resume Next 'return null string if a colmatch index is non-existent If IsArray(Index) Then ReDim t(1 To UBound(Index)) For i = 1 To UBound(Index) t(i) = colMatches(Index(i) - 1) Next i REMid = t() Else REMid = CStr(colMatches(Index - 1)) If IsEmpty(REMid) Then REMid = "" End If On Error GoTo 0 'reset error handler Else REMid = "" End If End Function '======================================== You should then be good to go. --ron |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull a set number of characters from a cell without cutting of
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull a set number of characters from a cell without cutting of
On Tue, 17 Oct 2006 06:30:02 -0700, nmp wrote:
Ron, I'm getting a compile error: User defined-type not defined I would guess the reason is that you did NOT execute the step: Tools/References Select Microsoft VBScript Regular Expressions 5.5 (it could be anywhere in a long list; don't use 1.0) Seeing as how I cannot get either yours or Nick's to work can I just send you a copy of my spreadsheet with a portion of my data? You can e-mail me at . If the problem is not that, go ahead and send me the workbook. --ron Thanks! "Ron Rosenfeld" wrote: On Thu, 12 Oct 2006 06:15:02 -0700, nmp wrote: Ron, I did not post in multiple threads on purpose. When I clicked on POST for the first one it gave me an error so I posted again. I guess the first one actually went through even though it said it didn't. My apologies! Now I need to see if I can get one of these solutions to work! Thanks! I see in another message that there is an issue with downloading add-ins. Well, the same principle can be used through VBA. The formula is similar: A1: Your String B1: =remid($A1,"\b[\s\S]{1,50}\b",COLUMNS($A:A)) Before using the formula: <alt-F11 to open the VB Editor **Ensure your project is highlighted in the Project Explorer Window.** Tools/References Select Microsoft VBScript Regular Expressions 5.5 (it could be anywhere in a long list; don't use 1.0) **Insert/Module** Paste code below into the window that opens '============================================== Option Explicit Function REMid(str As String, Pattern As String, _ Optional Index As Variant = 1, _ Optional CaseSensitive As Boolean = True) _ As Variant 'Variant as value may be string or array Dim objRegExp As RegExp Dim objMatch As Match Dim colMatches As MatchCollection Dim i As Long 'counter Dim t() As String 'container for array results ' Create a regular expression object. Set objRegExp = New RegExp 'Set the pattern by using the Pattern property. objRegExp.Pattern = Pattern ' Set Case Insensitivity. objRegExp.IgnoreCase = Not CaseSensitive 'Set global applicability. objRegExp.Global = True 'Test whether the String can be compared. If (objRegExp.Test(str) = True) Then 'Get the matches. Set colMatches = objRegExp.Execute(str) ' Execute search. On Error Resume Next 'return null string if a colmatch index is non-existent If IsArray(Index) Then ReDim t(1 To UBound(Index)) For i = 1 To UBound(Index) t(i) = colMatches(Index(i) - 1) Next i REMid = t() Else REMid = CStr(colMatches(Index - 1)) If IsEmpty(REMid) Then REMid = "" End If On Error GoTo 0 'reset error handler Else REMid = "" End If End Function '======================================== You should then be good to go. --ron --ron |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull a set number of characters from a cell without cutting of
No, it has to be in a normal Module, which is not available automatically.
You have to add it. NickHK "nmp" wrote in message ... I already did that. When you open the VBE it automatically opens a module. I copied and pasted what you have below and I am still getting the Compile Error Expected: Identifier. "NickHK" wrote: You need to add a module to the workbook. In the VBE, right click on the document tree for your workbook, select InsertModule. Paste the function code below. Then you can call the function from a cell as described and copy down your data, as with Excel's built in functions. NickHK "nmp" wrote in message ... I didn't want to use Ron's because I didn't want to have to install anything. My company is pretty protective about that kind of stuff. Anyway, maybe I should have said that I really don't know much at all about VBA and macros! The extent of my experience is just recording macros. I don't understand half of the words you used below. I took what you have below and pasted it in the VB Editor of my sheet and saved it. Is that all I need to do? I then put the first formula in F2 but it gave me a Compile Error Expected: Identifier. What did I do wrong? "NickHK" wrote: If you don't want to go Ron's RegEx route: For the subsequent fields, you can pass the length of the string returned from the previous call as the value of the StartPos parament. Adjust the value of MaxLength as required. SampleText remans the same for all calls. Public Function TruncateOnWholeWord2(SampleText As Variant, Optional MaxLength As Long = 50, Optional StartPos As Long = 1) As String Dim TempStr As String TempStr = Mid(SampleText, StartPos) 'No problem, just return the input If Len(TempStr) <= MaxLength Then TruncateOnWholeWord2 = TempStr Exit Function End If 'OK, need to do some work 'Find the first [SPACE] before the MaxLength +1 char TruncateOnWholeWord2 = Left(TempStr, InStrRev(TempStr, " ", MaxLength + 1) - 1) End Function As for using it, with you first value in E2 F2: =TruncateOnWholeWord2(E2,50) G2: =TruncateOnWholeWord2(E2,40,len(F2)) H2: =TruncateOnWholeWord2(E2,40,Len(F2)+len(G2)) NickHK "nmp" wrote in message ... OK. I'm not that great at VBA and macros. Any chance you can step me through exactly what I need to do if my data is in E2 through E1587? Here is some more detail on what I have: I have 3 fields available for my description. Description Field 1 needs to be limited to 50 characters. Anything over 50 characters I want to break on the whole word and put in Description Field 2 which needs to be limited to 40 characters. There are a few descriptions that are over 90 characters long, in which case I want to put anything remaining in Description Field 3. Field 3 needs to be limited to 40 characters too, but I do not have any descriptions over 130 characters. Currently the full descriptions are all in column E. Hope this helps. Thanks! "NickHK" wrote: Possibly: Public Function TruncateOnWholeWord(SampleText As Variant, Optional MaxLength As Long = 50) As String 'No problem, just return the input If Len(SampleText) <= MaxLength Then TruncateOnWholeWord = SampleText Exit Function End If 'If the 51st char is a [SPACE], then we can take the first 50 chars OK 'And other tests for what you consider the End-Of-Word char If Mid(SampleText, 51, 1) = " " Then TruncateOnWholeWord = Left(SampleText, 50) Exit Function End If 'OK, need to do some work 'Find the first [SPACE] before the 51st char TruncateOnWholeWord = Left(SampleText, InStrRev(SampleText, " ", 51) - 1) End Function Depending on your requirements, you can add Trim to strip spaces NickHK "nmp" wrote in message ... I need to be able to pull a set number of characters from a cell, but I do not what to cut off a word if that set number ends up in the middle of a word. For example I have: 11 x 8-1/2, 80# Patient Education Shell #501324 CREAM .. By using a LEFT(A2,50) it cuts off the EAM in CREAM. I actually want to cut off the whole word and put it in a different cell. Make sense? Is that possible? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to have a certain number of characters in a cell | Excel Discussion (Misc queries) | |||
cutting characters from a cell | Excel Worksheet Functions | |||
Pull whole words from a cell up to a set number of characters. | Excel Programming | |||
How do I pull just the numeric characters in a cell? | Excel Discussion (Misc queries) | |||
Number of characters in a cell | Excel Discussion (Misc queries) |