Macro to trim and convert in excel

[BrianMH]

If your anything like me your constantly putting data into excel for analysis. Sometime it pastes as text instead of a currency or a date. Sometimes it has extra spaces. This code allows you to select a range and convert it or simply trim it. Hope someone finds it helpful.

[VBA]
Sub trimmer()
Dim rSel As Range
Set rSel = Selection
Dim c As Range
Dim strV
Dim intConv As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
intConv = Application.InputBox("What would you like to convert to? Please enter a number" & Chr(13) & _
"1. Date" & Chr(13) & _
"2. Currency" & Chr(13) & _
"3. Decimal" & Chr(13) & _
"4. long (whole number)" & Chr(13) & _
"5. Don't convert just trim values" & Chr(13) & _
"6. Convert IGT dates to normal dates", , , , , , , 1)


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
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)
While Asc(Left(strV, 1)) = 127 Or Asc(Left(strV, 1)) = 129 Or Asc(Left(strV, 1)) = 141 Or Asc(Left(strV, 1)) = 143 Or Asc(Left(strV, 1)) = 144 Or Asc(Left(strV, 1)) = 157 Or Asc(Left(strV, 1)) = 160
strV = Right(strV, Len(strV) - 1)
If Not strV <> "" Then GoTo skip
Wend
While Asc(Right(strV, 1)) = 127 Or Asc(Right(strV, 1)) = 129 Or Asc(Right(strV, 1)) = 141 Or Asc(Right(strV, 1)) = 143 Or Asc(Right(strV, 1)) = 144 Or Asc(Right(strV, 1)) = 157 Or Asc(Right(strV, 1)) = 160
strV = Left(strV, Len(strV) - 1)
If Not strV <> "" Then GoTo skip
Wend
skip:
c.Value = strV
End If
Next c
Case 6
'20110131'
For Each c In rSel.Cells
c.NumberFormat = "General"
If c.Value <> "" Then
c.Value = DateValue(Right(c.Value, 2) & "/" & Mid(c.Value, 5, 2) & "/" & Left(c.Value, 4))
End If
c.NumberFormat = "dd-mmm-yyyy"
Next c
Case False
MsgBox ("you did not select a conversion type")
End Select
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
[/VBA]

[nepotist]

Try to submit as KBarticle

[mdmackillop]

or a KB item

[RonMcK]

BrianMH,

Do youi need an error trap for Case 6 when the date string is not null but also not equal to 8 numeric digits?

Otherwise a wonderful program, thanks for sharing it with us.

Thanks,

[BrianMH]

Adding some error checking would probably be a good idea. Glad you like it though .