Option Explicit 
Sub Trimmer() 
    Dim rSel As Range 
    Dim c As Range 
    Dim strV 
    Dim intConv As Integer 
    Dim arr As Variant 
    Dim sDay As String 
    Dim sMonth As String 
    Dim sYear As String 
     
     
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
     
    Set rSel = Selection 
     
    If Application.CountA(rSel) = 0 Then 
        MsgBox "No values selected", , "Trimmer" 
        Exit Sub 
    End If 
     
    intConv = Application.InputBox("What would you like to convert to? Please enter a number" & Chr(13) & _ 
    "1. Date     " & vbTab & "4. long (whole number)" & Chr(13) & _ 
    "2. Currency " & vbTab & "5. Don't convert just trim values" & Chr(13) & _ 
    "3. Decimal  " & vbTab & "6. Convert ISO (YYYYMMDD) dates to normal dates", , , , , , , 1) 
     
    arr = Array(127, 129, 141, 143, 144, 157, 160) 
     
    If rSel.Cells.Count > 5000 Then 
        If MsgBox("You have selected a large number of cells, this may take some time, do you want to continue?", vbOKCancel) = vbCancel Then 
            Application.Calculation = xlCalculationAutomatic 
            Application.ScreenUpdating = True 
            Exit Sub 
        End If 
    End If 
    Select Case intConv 
    Case 1 
        For Each c In rSel.Cells 
            If c.Value <> "" Then 
                c.Value = CDate(Trim(c.Value)) 
            End If 
        Next c 
    Case 2 
        For Each c In rSel.Cells 
            If c.Value <> "" Then 
                c.Value = CCur(Trim(c.Value)) 
            End If 
        Next c 
    Case 3 
        For Each c In rSel.Cells 
            If c.Value <> "" Then 
                c.Value = CDec(Trim(c.Value)) 
            End If 
        Next c 
    Case 4 
        For Each c In rSel.Cells 
            If c.Value <> "" Then 
                c.Value = CLng(Trim(c.Value)) 
            End If 
        Next c 
    Case 5 
        For Each c In rSel.Cells 
            If Trim(c.Value) = "" Then c.Value = "" 
            If c.Value <> "" Then 
                strV = Trim(c.Value) 
                For Each a In arr 
                    strV = Application.Substitute(strV, Chr(a), "") 
                Next 
                c.Value = strV 
            End If 
        Next c 
    Case 6 
        For Each c In rSel.Cells 
            c.NumberFormat = "General" 
            If c.Value <> "" Then 
                sDay = Right(c.Value, 2) 
                sMonth = Mid(c.Value, 5, 2) 
                sYear = Left(c.Value, 4) 
                Select Case Application.International(xlDateOrder) 
                Case 0 
                    c.Value = DateValue(month & "/" & day & "/" & year) 
                Case 1 
                    c.Value = DateValue(day & "/" & month & "/" & year) 
                Case 2 
                    c.Value = DateValue(year & "/" & month & "/" & day) 
                End Select 
            End If 
            c.NumberFormat = "dd-mmm-yyyy" 
        Next c 
    Case False 
        MsgBox ("You did not select a conversion type") 
    End Select 
errhandler: 
    Application.Calculation = xlCalculationAutomatic 
    Application.ScreenUpdating = True 
    If Err <> 0 Then MsgBox Err.Number & ", " & Err.Description 
End Sub