Dazzawm
Well-known Member
- Joined
- Jan 24, 2011
- Messages
- 3,786
- Office Version
- 365
- Platform
- Windows
I was given the code below to extract a word among text in a cell and it puts it in the adjacent cell. If I have quite a few words how do I enter them in the code. Also some may be a mixture of number and letters.
Another thing if the destination cell has data in it how do I put what is moved at the end of it rather than what it does now and overwrites what is in there?
Thanks.
Another thing if the destination cell has data in it how do I put what is moved at the end of it rather than what it does now and overwrites what is in there?
Thanks.
Code:
Sub ExtractData()
Dim REX As Object
Dim rexMatch As Object
Dim rexMatchCol As Object
Dim Cell As Range
Dim strText As String
Set REX = CreateObject("VBScript.RegExp")
With REX
.Global = True
.IgnoreCase = True
.Pattern = "Example"
For Each Cell In Range(Cells(2, "J"), Cells(Rows.Count, "J").End(xlUp))
If .test(Cell.Value) Then
Set rexMatchCol = .Execute(Cell.Value)
Cell.Value = .Replace(Cell.Value, vbNullString)
strText = vbNullString
For Each rexMatch In rexMatchCol
strText = strText & Chr(32) & rexMatch.Value
Next
Cell.Offset(, 1).Value = Trim(strText)
End If
Next
End With
End Sub
