On Tue, 26 Aug 2008 07:01:00 -0700, BRB wrote:

Have a variation to a previous question:
Need to copy a string of characters from one cell to another. Here is what
the cell looks like:

Cells:
A1 = Some text, HW012345, more text, HW034567, more text
A2 = Some text
A3 = Some text, HW056783, more text, HW035791, more text

In column B I need the following result:
B1 = HW012345 HW034567
B2 = (blank)
B3 = HW056783 HW035791

Thanks for any help



The following will return a space-separated string of all of the substrings in
your original string that start with HW0 and are followed by a sequence of
digits.

If this is not exactly what you want, it can be easily modified.

To enter this User Defined Function, <alt-F11 opens the VB Editor. Ensure
your project is highlighted in the Project Explorer window, then Insert/Module
and paste the code below into the window that opens.

To use this, enter =ReExtr(cell_ref) into some cell containing the string to be
tested.

================================
Option Explicit
Function ReExtr(str As String) As String
Dim sArr() As String
Dim i As Long
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\bHW0\d+\b"
If re.test(str) = True Then
Set mc = re.Execute(str)
ReDim sArr(mc.Count - 1)
For i = 0 To mc.Count - 1
sArr(i) = mc(i)
Next i
ReExtr = Join(sArr, " ")
End If
End Function
=========================================
--ron