Hi KH_GS,

maybe this works:

Public Sub abc()
Dim myString As String
Dim searchString As String
searchString = "abc"

Dim x As Range
For Each x In Selection
myString = CStr(x.Value)
sp = Split(myString, " ")

For Each s In sp
If Right(s, Len(searchString)) = searchString Then
x.Offset(0, 1).Value = myString
End If
Next
Next

End Sub

KH_GS schrieb:

Hi

I have a column containing text phrases in each cell, if there's any
word in the cell ends with "abc", I want that phrase to be in a new
column.

Data in each cell:
appleabc is not red
roadabc is long
green apple
roadblock
apple is fruitabc
sweet fruit

Output(in a new column):
appleabc is not red
roadabc is long
apple is fruitabc


Here's the code that doesn't work

Sub PrintEnd_ING()
Dim Cell As Range
Dim myString As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False

x = ActiveCell.Row
y = ActiveCell.Column

For Each Cell In Range(Selection, Selection.End(xlDown))

myString = Cells(x, y).Value
If myString Like "*abc" Or myString = "*abc? " Then

ActiveSheet.Cells(x, y + 3).Value = myString
ActiveSheet.Cells(x, y + 4).Value = Cell.Offset(0, 1).Value
x = x + 1

End If
Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub


--
KH_GS
------------------------------------------------------------------------
KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920
View this thread: http://www.excelforum.com/showthread...hreadid=529110