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

 

[GrowlJones]

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

Ron,

This actually works great for a problem I had awhile back regarding pulling data between some gibberish. The only problem I have is that this doesn't seem to work when I try to pull numbers after symbols. In this instance I need to grab all numbers between a double asterisk and a backslash. Is there some way I can alter the code you posted in order to accomplish that task?