Remove Certain Last Words from String

**jindon** · 02-16-2008, 01:07 AM

Edited
You don't seem to be happy with the UDF, so I delete.

**ShredDude** · 02-16-2008, 01:15 AM

Thanks for the reply.

I'm trying to keep the maintenance of the list of lastwords out of the user's hands. I want to keep it in the code. Either way, I have to type the values somewhere.

I was thinking I could somehow populate an array with the values, and then loop through them to see if I have a match with the curent string.

I guess I could hide the worksheet the dynamic range list is on.

Any other ideas?

Thanks,

Shred

**mikerickson** · 02-16-2008, 10:59 AM

An array would work. Since MATCH works with arrays, you could avoid looping. Match is also case insensitive.

Dim suffixArray as Variant
suffixArray = Array("Street", "St.", "St", "Lane, "Ln.")

If IsNumeric (Application.Match(lastWord, suffixArray,0) Then
   Msgbox "on final word list"
Else
   Msgbox "not on list"
End If

**ShredDude** · 02-16-2008, 01:18 PM

Thanks Mikerickson. That's the kind of thing I was envisioning. I had made similar attempts but never quite got my syntax correct I guess.

Thanks for pointing out that MATCH is is not case sensitive. It looks like this will do the trick for me.

Shred

**ShredDude** · 02-16-2008, 01:19 PM

Wasn't unhappy...just didn't click with me. Maybe it was way over my head.

Thanks for replying.

**ShredDude** · 02-16-2008, 02:43 PM

Thanks Mikerickson for pointing me in this direction. I adapted your ideas into some code that eventually worked. I was unable to get the results I desired using ISNUMERIC when there was not a match as an error would be returned by the Match worksheet function; that worked fine if is there was a match between the lastword and the list of choices. So I resorted to ISERROR instead and got the results I needed. It now works whether there is a match or not.

I also got it working with a dynamic range pulled from the workbook ('commented line)

See code below.

Thanks again.

Private Sub CommandButton7_Click()
Dim mystr, mynewstr, lastword As String
Dim lastblank, firstblank As Long
Dim suffixarray()

mystr = Cells(ActiveCell.Rows.Row, 1).Value
firstblank = InStr(mystr, " ")
lastblank = InStrRev(mystr, " ")
suffixarray() = Array("Street", "Drive", "Lane")

lastword = Right(mystr, Len(mystr) - lastblank)

On Error Resume Next
'    If IsError(Application.WorksheetFunction.Match(lastword, Range("suffixlist"), 0)) Then
    If IsError(Application.WorksheetFunction.Match(lastword, suffixarray, 0)) Then
        mynewstr = Right(mystr, Len(mystr) - firstblank)
      Else
        mynewstr = Mid(mystr, firstblank + 1, lastblank - firstblank - 1)
    End If
On Error GoTo 0
  
  MsgBox mystr & vbCrLf & vbCrLf & mynewstr

End Sub

**mikerickson** · 02-16-2008, 03:37 PM

You don't need both the named range and the array. Managing this list would be much easier using only one or the other, but not both. It sounds like you would prefer the array.