Pull a set number of characters from a cell without cutting off a

[Guest]

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!

 

[Trevor Shuttleworth]

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

 

[Guest]

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)))))))

 

[NickHK]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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!

 

[Guest]

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!

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

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!

 

[NickHK]

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

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!

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

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!

 

[Guest]

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?

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

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!

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

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!

 

[NickHK]

You need to add a module to the workbook.
In the VBE, right click on the document tree for your workbook, select
Insert>Module. 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

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?

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

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!

:

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

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!

 

[Guest]

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.

You need to add a module to the workbook.
In the VBE, right click on the document tree for your workbook, select
Insert>Module. 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

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?

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

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!

:

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

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!

 

[Ron Rosenfeld]

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

 

[Guest]

Ron,

I'm getting a compile error: User defined-type not defined

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
(e-mail address removed).

Thanks!

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 Rosenfeld]

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
(e-mail address removed).

If the problem is not that, go ahead and send me the workbook.

--ron

Thanks!

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

 

[NickHK]

No, it has to be in a normal Module, which is not available automatically.
You have to add it.

NickHK

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.

You need to add a module to the workbook.
In the VBE, right click on the document tree for your workbook, select
Insert>Module. 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

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?


:

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

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!

:

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

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!