I am having trouble stripping any numbers from a string. In Excel, I have many string fields that may contain numbers. I only care about the number(s), the rest of the characters are unwanted and will be discarded. The number may be in any position, not a set location.
For example: '2 CATCH BASINS' or 'CATCH BASINS x2'
I based my code on this SO answer, but I can't seem to get it to work. The error message is 'Application-defined or object-defined error'.
Option Explicit
Function onlyDigits(s As String) As String
' Variables needed (remember to use "option explicit"). '
Dim retval As String ' This is the return string. '
Dim i As Integer ' Counter for character position. '
' Initialise return string to empty '
retval = ""
' For every character in input string, copy digits to '
' return string. '
For i = 1 To Len(s)
If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
retval = retval + Mid(s, i, 1)
End If
Next
' Then return the return string. '
onlyDigits = retval
End Function
Public Sub CommandButton1_Click()
Application.ScreenUpdating = False
' -----------------------------------------------------------------------------------------
' Will strip numbers from descriptions for basins, guy wires, water meters/valves & pull box
' -----------------------------------------------------------------------------------------
Dim counter As Integer 'Index for the While Loop
Dim fCode As String 'Variable for column E, feature code
Dim fDesc As String 'Variable for column F, the descriptor
Do While Cells(counter, 1).Value <> "" 'While the first column has any data, keep looping
fCode = Cells(counter, 5).Value 'Populate feature code variable from column E
If (fCode = "XCB") Or (fCode = "XGW") Or (fCode = "XWV") Or (fCode = "XWM") Then
fDesc = Cells(counter, 6).Value
Cells(counter, 6).Value = onlyDigits(fDesc)
Else
'do nothing
End If
counter = counter + 1
Loop 'Finishes checking for numbers within specific descriptors
Can someone point me in the right direction? It would be much appreciated!!
