Say I have a column of entries where the entries are grades, say for this example 4th grade. I only need to have the numerical value.
Function CleanCode(entry) As Integer
If entry Like "[4]" Then entry = 4
End Function
why doesn't this function do it?
Firstly, to "clean" a string the starts with a numeric character, you use the "Val(" function. I think you are also expecting the "entry" parameter to be changed by the CleanCode function, however you must explicitly return the value.
Function CleanCode(entry) As Integer
entry = Val(entry)
If entry Like "[4]" Then
entry = 4
Else
entry = -1
End If
CleanCode = entry
End Function
We can call this function from another function to see how it operates:
Sub CallCC()
Dim entry As Variant
entry = "9th"
Dim Result As Integer
Result = CleanCode(entry)
MsgBox "Result = " & Result
'Result is -1
entry = "4th grade"
Result = CleanCode(entry)
MsgBox "Result = " & Result
' Result = 4
End Sub
Use a regexp to efficiently clean your string. To clean a string in A1, in B1 you could enter
=CleanString(A1)
Sub Tester()
MsgBox CleanString("3d1fgd4g1dg5d9gdg")
End Sub
Function CleanString(strIn As String) As String
Dim objRegex
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Global = True
.Pattern = "[^\d]+"
CleanString = .Replace(strIn, vbNullString)
End With
End Function