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