Copy string of characters

[BRB]

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

 

[Joel]

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

 

[Teethless mama]

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

 

[BRB]

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.

 

[Ron Rosenfeld]

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