Finding one word in a string and outputting the word in the same row

[John Br]

Hi

I want to find one word in a cell that may or maynot be in the cell and then put that word in a cell in the same row.

I found the following code which finds the word (in this case "late") so i have the code for this but unfortunately it just dumps the work "late" into row 2 of the selected column and then in each subsequent row.

Does someone have the code to ensure the work is placed in the same row as the cell with the word "Late" in the text string.

The text string can be any legnth and as i said the word "late" may or not be in the string.

Also it is unlikely that the word would be capitilised, but for completeness sake is there code to search for both "late" and "Late"? This is not critical but it would be good to check for both.

I do not want to find later or latest so should I put a space after late to exclude anything else?

Sub Late()
Dim arry As Variant
Dim Str As Variant
Dim RE As New RegExp



arry = Range("F2:F20000").Value

RE.Pattern = "late"
RE.Global = True
Dim matches As MatchCollection
i = 2


For Each Str In arry
Set matches = RE.Execute(Str)
'Debug.Print RE.Test(Str)
If RE.Test(Str) = True Then
Cells(i, 25) = matches(0)
i = i + 1
End If


Next Str

End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Give this a try with a copy of your workbook.

Sub Late_v2()
  Dim RX As Object
  Dim a As Variant, b As Variant
  Dim i As Long
  
  Const mySearchWord As String = "Late" '<- Change as required
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = True  '<- Change to false if you want a case-sensitive search
  RX.Pattern = "\b" & mySearchWord & "\b"
  a = Range("F2", Range("F" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If RX.Test(a(i, 1)) Then b(i, 1) = mySearchWord
  Next i
  Range("Y2").Resize(UBound(b)).Value = b
End Sub

My sample data and results:

John Br.xlsm
	F																			Y
1
2	In the same row as the cell with the word Late in the text string																			Late
3	See you later
4	Do not be late!																			Late
5
6	other text
7	Better late than never																			Late
8	His work was slated by the critics
9	Late LATER latest																			Late
Sheet1

BTW, IF you don't have punctuation beside the word (like row 4 below) then you can also do this by a single formula in the top cell like this.

John Br.xlsm
	F																				Z
1
2	In the same row as the cell with the word Late in the text string																				Late
3	See you later
4	Do not be late!
5
6	other text
7	Better late than never																				Late
8	His work was slated by the critics
9	Late LATER latest																				Late
Sheet1

Cell Formulas
Range	Formula
Z2:Z9	Z2	=IF(ISNUMBER(SEARCH(" late "," "&F2:F9&" ")),"Late","")
Dynamic array formulas.

 

[John Br]

I would like to thank Peter SSs, this has litteraly saved me days of work each month. I did not use the arry formula as sometimes there are punctuation marks after one of the words I want to search for.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.