Formula: Substituting several characters

[mr305]

Hello everyone,

I'm working on a school project in which I need delete and substitute a few characters. I know that I can use the SUBSTITUTE function to accomplish that. However, since I have to do it with a few characters, I would like to use just one formula. My problem is that I don't know how to combine several functions in one formula.

Here are the characters I need to delete or replace:
Delete
- Spaces at the beginning or the end of the text, for example " word "
- "+" symbol
- "[" and "]" symbols
- quotes symbol (")
Substitute:
- replace Spanish special characters for English characters (á,é,í,ó,ú,ñ for a,e,i,o,u,n).

By the way, once I get that, I also need to replace spaces by dash "-" symbol.

Any help would be greatly appreciated.

Thanks for sharing your knowledge!

 

[CWatts]

You're looking at nesting a ton of SUBSTITUTE commands like this:

=SUBSTITUTE(SUBSTITUTE(TRIM(A1),"[",""),"]","")

or a user defined function in VBA.

Enter this code into your vba editor

Function Cleanup(rng As Range) As String
If rng.Rows.Count > 1 Or rng.Columns.Count > 1 Then
    MsgBox "Too many cells selected, your formula wont work." & vbNewLine & _
        "Please correct the formula you just entered."
Else
    Cleanup = rng.Value
    Cleanup = Trim(Cleanup)
    Cleanup = WorksheetFunction.Substitute(Cleanup, "+", "")
    Cleanup = WorksheetFunction.Substitute(Cleanup, "[", "")
    Cleanup = WorksheetFunction.Substitute(Cleanup, "]", "")
    Cleanup = WorksheetFunction.Substitute(Cleanup, """", "")
    Cleanup = WorksheetFunction.Substitute(Cleanup, "á", "a")
    Cleanup = WorksheetFunction.Substitute(Cleanup, "é", "e")
    Cleanup = WorksheetFunction.Substitute(Cleanup, "í", "i")
    Cleanup = WorksheetFunction.Substitute(Cleanup, "ó", "o")
    Cleanup = WorksheetFunction.Substitute(Cleanup, "ú", "u")
    Cleanup = WorksheetFunction.Substitute(Cleanup, "ñ", "n")
    Cleanup = WorksheetFunction.Substitute(Cleanup, " ", "-")
End If
End Function

and then you can just use the =Cleanup(A1) function on the workbook.

 

[mr305]

Thank you, CWatts!

I finally did it like this:
=LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1),"á", "a"),"é", "e"),"í", "i"),"ó", "o"),"ú", "u"),"ñ", "n"),"[",""),"]",""),"+", ""),"-", ""),CHAR(34),"")," ", "-"))