PDA

View Full Version : [SOLVED] Need a UDF to Identify and remove parts of Cell Data from a Range



New_Here
04-20-2017, 06:43 PM
Hi Good Morning from Sri Lanka,

I have sets of words in cell A1,A2,A3 in Sheet 1

I have a Range which I look to remove from above Cells (plus I will continue to update that Colomn), They are in Sheet 2, Colomn 4 (ex for words are - Trusted User, Call Now, Sale)

Now I need the following to happen:

A1 = ABC Company Trusted User

Result in B1 = ABC Company

A2 = ABC Company Sale

Result in B2 = ABC Company

A3 = JnH Users and Distributors Call Now

Result in B3 = JnH Users and Distributors

Is this possible?

Paul_Hossler
04-21-2017, 06:44 AM
Try this. Attachment has some sample data




Option Explicit
Function RemoveWords(s As String, r As Range) As String
Dim r1 As Range
Dim s1 As String

s1 = s

For Each r1 In r.Cells
If Len(r1.Text) = 0 Then Exit For
s1 = Trim(Replace(s1, r1.Text, vbNullString))
Next

RemoveWords = s1

End Function

New_Here
04-21-2017, 07:51 AM
Try this. Attachment has some sample data




Option Explicit
Function RemoveWords(s As String, r As Range) As String
Dim r1 As Range
Dim s1 As String

s1 = s

For Each r1 In r.Cells
If Len(r1.Text) = 0 Then Exit For
s1 = Trim(Replace(s1, r1.Text, vbNullString))
Next

RemoveWords = s1

End Function

Awesome Chief, Thanks alot. May Lord Mighty of All Words Bless you. Ameen.