Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
=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

2 Answers 2

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.