UDF to remove words from a text string

[stodge]

I am trying to write a UDF to remove certain words from an imported text string.

For example, an imported text string might be air-conditioning systems or aircon system

I want to be able to remove the "-" "systems" and "system". I also want to easily add other words to exclusion list in due course.

I can do this easily using a macro but would like to create a UDF to achieve the same result.

Thanks in advance for your help.

 

[RoryA]

There shouldn't be a huge difference between a macro and a UDF in terms of code, so where are you getting stuck?

 

[stodge]

Sorry, I am new to UDFs.

The macro I am using is:

Sub Remove_certain_words()
'
' Remove_certain_words Macro
' Macro recorded 19/01/2010 by Stodge
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Cells.Select
Selection.Replace What:="systems", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="system", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

What I can't work out is how to create a UDF to look at cell A1 and remove the words I don't want. In addition, I want to be able to remove plurals from words.

Hope this makes sense.

 

[RoryA]

Plurals would be very difficult as it's not just a question of removing an 's' from the end of a word (and not all words ending in 's' are plurals!)
For a UDF:

Function CleanWords(rngIn as Range) As String
'
' Remove_certain_words Macro
    Dim strTemp as String
    strTemp = rngIn.Value
    strTemp = Replace(strTemp,"systems", "")
strTemp = Replace(strTemp,"system", "")
strTemp = Replace(strTemp,"-", "")
    CleanWords = strTemp
End Function

 

[pgc01]

Hi

If you have more words you can use an array as an exclusion list:

Function Remove_certain_words(ByVal s As String)
Dim vArray As Variant, vWord As Variant
 
vArray = Array("systems", "system", "-")
    
For Each vWord In vArray
    s = Replace(s, vWord, "")
Next vWord
Remove_certain_words = s
End Function

Remark: When you initialise the array place the plurals before the singulars and the signs at the end.