Clean data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I get data in that I have work with that comes in a string in a single field
called Field1 that looks like like this:

203948sdf, 4,3,3, ysdifwier werioweir ** :<<<<<sdlfkwier jsdfj, fjdj

How can I remove all of the characters, the commas the asterisks all of it
except a number or a letter and also make sure there is just one space
between each item so when I'm done
it would look like:

203948sdf 4 3 3 ysdifwier werioweir sdlfkwier jsdfj fjdj
 
For the most part, this is pretty easy. The real problem is knowing what
constitutes an item. I notice that some items seem to be followed by a comma
and others are not.
If you can supply a rule on how to know what an item is, I can show you a
simple for next loop that will this happen.
 
There is a website called datapigtechnology that has posted a large number of
Flash movie screen shots of different Access how tos. One of these works
with using instr in queries to clean up your data. This might be helpful to
you. The author, Micheal Alexander has a pretty good book too.
 
An item is anything that is or begins with any letter or number. All of the
symbols are junk to me. For instance this:

203948sdf, 4,3,3, ysdifwier werioweir ** :<<<<<sdlfkwier jsdfj, fjdj

Is what I get

The items are:
item: 203948sdf
item: 4
item: 3
item: 3
item: ysdifwier
item: werioweir
item: sdlfkwier
item: jsdfj
item: fjdj

It is a chunk of info all in one field in one table that I need cleaned so
there is only one space between each item.
 
Okay, here it is:

Public Function StripString(strJunk As String) As String
Dim lngCtr As Long
Dim lngLength As Long
Dim strTheCharacter As String
Dim intAscii As Integer
Dim strFixed As String
Dim blnNewItem As Boolean

lngLength = Len(strJunk)
blnNewItem = True
For lngCtr = 1 To lngLength
strTheCharacter = Mid(strJunk, lngCtr, 1)
intAscii = Asc(strTheCharacter)
If (intAscii >= 48 And intAscii <= 57) Or (intAscii >= 65 And
intAscii <= 90) _
Or (intAscii >= 97 And intAscii <= 122) Then
If blnNewItem Then
strFixed = IIf(Len(strFixed) = 0, "", strFixed & " ")
blnNewItem = False
End If
strFixed = strFixed & strTheCharacter
Else
blnNewItem = True
End If
Next lngCtr
StripString = strFixed
End Function

I tested it on the values you posted and it returns exactly what you said
you wanted
 
Can you post the URL to the website? I tried to google it but no luck. I
also tried "datapigtechnology.com" and such...

Thanks
 
Back
Top