Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
=SUBSTITUTE(AD!H35,"&","")

The formula above replaces an ampersand in a cell where I have the text

Handy Person / Driver & Car

giving

Handy Person / Driver Car

How do I also remove the forward slash '/' ?

I've seen it done with nested substitute formulas, but as I probably need to remove even more characters in the future. I'd rather use a more elegant solution. Perhaps even replacing an entire class of non-alphanumeric characters is another solution?

share|improve this question
    
thanks guys. I went for Prakash's solution, even though it's back tracking on what I said, of not wanting loads of nested substitutes. It was just the quickest way. – conspirisi Mar 18 '11 at 8:21

I don't know of any worksheet functions that are more elegant than nested SUBSTITUTES, unfortunately. If you want to use a VBA user defined function, this one might get you started.

Public Function CleanAlpha(Target As Range) As String

    Dim rCell As Range
    Dim sReturn As String
    Dim i As Long

    'Only act on first cell
    Set rCell = Target.Cells(1)

    'loop through each character
    For i = 1 To Len(rCell.Value)
        Select Case Asc(Mid$(rCell.Value, i, 1))
            Case 65 To 90, 97 To 122 'letters
                sReturn = sReturn & Mid$(rCell.Value, i, 1)
            Case 32 'spaces
                sReturn = sReturn & Mid$(rCell.Value, i, 1)
        End Select
    Next i

    CleanAlpha = Trim(sReturn)

End Function

Use in a worksheet like

=TRIM(cleanalpha(A1))
share|improve this answer
    
@conspirisi: dkusleika has the right idea. However, if you still want to go the nested function route, char(47) (as a second argument) will be your friend to search out the '/' character (or any other character for that matter). – JDB Mar 9 '11 at 19:07
=SUBSTITUTE(SUBSTITUTE(AD!H35,"&","")," /","")

Try with this.

share|improve this answer
    
thanks prakesh, I went with your solution. I'm not going to mark it with a tick though as I didn't want to use nested substitions in the original question – conspirisi Mar 18 '11 at 8:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .