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.

"julialatte" wrote:

> 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
>
>
>

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.

"julialatte" wrote:

> 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
>
>
>

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.


"Klatuu" wrote:

> 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.
>
> "julialatte" wrote:
>
> > 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
> >
> >
> >

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

"julialatte" wrote:

> 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.
>
>
> "Klatuu" wrote:
>
> > 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.
> >
> > "julialatte" wrote:
> >
> > > 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
> > >
> > >
> > >

Can you post the URL to the website? I tried to google it but no luck. I
also tried "datapigtechnology.com" and such...

Thanks

--
Message posted via http://www.accessmonster.com

Sorry, found it (changed to technologies and found it right away)...

http://www.datapigtechnologies.com/

--
Message posted via http://www.accessmonster.com