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

You need a macro. Are you looking to extact all numeric strings starting
with space followed by "HW" (including the one at the beginning of the line
with no space)?

call with
=GetHW(A1)

Function GetHW(Target)
GetHW = ""
charcount = 1
Do While charcount <= Len(Target)
If Mid(Target, charcount, 2) = "HW" Then
If GetHW < "" Then
'add blank between strings
GetHW = GetHW & " HW"
Else
GetHW = "HW"
End If
'extract number
charcount = charcount + 2
Do While IsNumeric(Mid(Target, charcount, 1))
GetHW = GetHW & Mid(Target, charcount, 1)
charcount = charcount + 1
Loop
Else
charcount = charcount + 1
End If
Loop
End Function



"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

=TRIM(IF(ISNUMBER(FIND("HW",A1)),MID(A1,FIND("HW", A1),8)&"
"&IF(ISNUMBER(FIND("HW",A1,FIND("HW",A1)+1)),MID(A 1,FIND("HW",A1,FIND("HW",A1)+1),8),""),""))


"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

Thanks Teethless mama, almost there.

My description was probably not complete.
The cell may contain several instances of HW0xxxxx, not just two. I used
your command statement and it worked well for two occurrances of the string,
but any more than that it did not find.

Thanks for any further help.

"Teethless mama" wrote:

=TRIM(IF(ISNUMBER(FIND("HW",A1)),MID(A1,FIND("HW", A1),8)&"
"&IF(ISNUMBER(FIND("HW",A1,FIND("HW",A1)+1)),MID(A 1,FIND("HW",A1,FIND("HW",A1)+1),8),""),""))


"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

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