Good point Steve. I was dealing separately with lat and lon.
Agree it should do the entire string with format XXX.xxx,-WWW.www
Here is revised function and test to deal with 1 complete string and return a string.
2 changes involved
Code:
'---------------------------------------------------------------------------------------
' Procedure : numcommadot
' Author : mellon
' Date : 27/04/2016
' Purpose : Process an incoming string, remove any characters that are not in this set.
' "0123456789-,.". Return a string with only the characters that match those in the set, ignore others.
'---------------------------------------------------------------------------------------
'
Function numcommadot(sIn As String) As String 'String in, string out
Dim c As String, i As Integer
10 On Error GoTo numcommadot_Error
20 sIn = Trim(sIn) 'remove leading/trailing spaces
Dim sHold As String
30 For i = 1 To Len(sIn)
40 c = Mid(sIn, i, 1) 'check each character
50 If InStr("0123456789-,.", c) <> 0 Then 'if acceptable keep it
60 sHold = sHold & c
70 Else ' if unacceptale, skip the character
80 End If
90 Next
100 numcommadot = sHold 'return a string NOT Double
110 On Error GoTo 0
120 Exit Function
numcommadot_Error:
130 MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure numcommadot of Module AWF_Related"
End Function
Revised test routine
Code:
Sub testnumcommadot()
Dim x(3) As String, i As Integer
x(0) = " 23.4333,-133.549"
x(1) = "-45.9" & vbCrLf & "445,56.8888"
x(2) = "45.2466,-113.1088853"
x(3) = "111.23-"
For i = LBound(x) To UBound(x)
Debug.Print x(i) & " results as " & numcommadot(x(i))
Next i
End Sub
test results:
Code:
23.4333,-133.549 results as 23.4333,-133.549
-45.9
445,56.8888 results as -45.9445,56.8888
45.2466,-113.1088853 results as 45.2466,-113.1088853
111.23- results as 111.23-