Hello,
I've recently parsed 50k txt files (via cmd line script) then imported into an Access Table. I am attempting to clean the "Size" field, it contains a number stored as a string. There are fields with the following formats:
1234567
1,234,567
1.234.567
1 234 567
I want all fields to be in simple number format (1234567)
I am attempting to clean all of the numbers and convert to a number format. I have used "Replace" to take care of the "," and "." scenarios, leaving only the space delimited numbers to clean. After several hours searching for a solution (and trying Trim, Replace, InStr(w/left,mid,right,etc
), I finally came across a solution using this code:
-Begin Code-
Function GetNumbers(strIn As String) As String
Dim j, numOnly
If strIn = "" Then Exit Function
For j = 1 To Len(strIn)
If IsNumeric(Mid(strIn, j, 1)) Then
numOnly = numOnly & Mid(strIn, j, 1)
End If
Next
GetNumbers = numOnly
End Function
-End Code-
There were approx 9k records with the " " delimiter. After running the code, I then converted the field type to "number." Access let me know that there were just over 6k "errors." I exported this manageable number of files to Excel (my native work environment) in an attempt to further clean these records.
In Excel, I am still having trouble removing these spaces. Excel error checking tells me that these fields are "numbers stored as text", but even after correcting, I am unable to remove the spaces (with Substitute, Trim, etc).
Can anyone advise on what might be causing these errors (in either Access or Excel)? Advise and potential solutions would be greatly appreciated.
Start Free Trial
