Clean Numbers/Text

[Rick5k via AccessMonster.com]

I need to clean up numbers/text in access. I have a series of Part Numbers
which contain both numeric and alpha characters and they also contain dots,
dashes and slashes. I need to get rid of the dots dashes and slashes so that
all I have left are the numeric and alpha chearacters. An example of what I
am talking about follows Part Number is AL25-6458 D/28 I need it to
become AL256458D28
I can do this in excel using the Substitute function, but that function is
not available as a function in access. I'm thinking that I will need to use
VBA to accomplish this but have not figured out how yet.

 

[Graham R Seach]

Rick,

See the following website:
http://www.pacificdb.com.au/MVP/Code/StripChars.htm

The call would be as follows:
Dim strResult As String
strResult = StripEx("AL25-6458 D/28",se_NonWord)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Graham R Seach]

Ricks,

Sorry, this is the call for SQL:
SELECT StripEx("AL25-6458 D/28",4) As MyAnswer FROM MyTable

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Klatuu]

replace(replace(replace(replace(pt,"-",""),"/","")," ",""),".","")

 

[Rick5k via AccessMonster.com]

Thank You to all the replied. They all make sense and now have something to
work on.

replace(replace(replace(replace(pt,"-",""),"/","")," ",""),".","")

I need to clean up numbers/text in access. I have a series of Part Numbers
which contain both numeric and alpha characters and they also contain dots,

[quoted text clipped - 5 lines]

not available as a function in access. I'm thinking that I will need to use
VBA to accomplish this but have not figured out how yet.

 

[Rick5k via AccessMonster.com]

Well I failed. I don't have enough knowledge about code to implement this
into my database. One of the responses though mentioned doing it through SQL.
What if I brought into a query just the field I need to clean up and also a
field to allow me to match it back up with the original query. Then my SQL
for that query would look like this:

SELECT PMC.Part, PMC.[Vendor Part #] FROM PMC;

Then I would just need the correct wording to remove all the dots, dashes,
and slashes from the 9000 parts listed under Vender Part #

Rick,

See the following website:
http://www.pacificdb.com.au/MVP/Code/StripChars.htm

The call would be as follows:
Dim strResult As String
strResult = StripEx("AL25-6458 D/28",se_NonWord)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

I need to clean up numbers/text in access. I have a series of Part Numbers
which contain both numeric and alpha characters and they also contain

[quoted text clipped - 9 lines]

use
VBA to accomplish this but have not figured out how yet.

 

[David F Cox]

I would have replied with Klattu's suggestion, and tested it. I pasted his
code into the update to cell in the QBE window.

replace(replace(replace(replace(pt,"-",""),"/","")," ",""),".","")

input:
gi/-ra. ffe

Output:

giraFfe

I note the capital F.I am using Access 2007 Beta, but I doubt that explains
it.

Full stop, followed by space - could it be that the REPLACE function is
capitalizing the first letter of a new "sentence"?

Well I failed. I don't have enough knowledge about code to implement this
into my database. One of the responses though mentioned doing it through
SQL.
What if I brought into a query just the field I need to clean up and also
a
field to allow me to match it back up with the original query. Then my SQL
for that query would look like this:

SELECT PMC.Part, PMC.[Vendor Part #] FROM PMC;

Then I would just need the correct wording to remove all the dots, dashes,
and slashes from the 9000 parts listed under Vender Part #

Rick,

See the following website:
http://www.pacificdb.com.au/MVP/Code/StripChars.htm

The call would be as follows:
Dim strResult As String
strResult = StripEx("AL25-6458 D/28",se_NonWord)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

I need to clean up numbers/text in access. I have a series of Part
Numbers
which contain both numeric and alpha characters and they also contain

[quoted text clipped - 9 lines]

use
VBA to accomplish this but have not figured out how yet.

 

[Rick5k via AccessMonster.com]

I forgot to mention that our system here still uses Access97 which
unfortunately is missing some bells and whistles that more up-to-date
versions have.

I would have replied with Klattu's suggestion, and tested it. I pasted his
code into the update to cell in the QBE window.

replace(replace(replace(replace(pt,"-",""),"/","")," ",""),".","")

input:
gi/-ra. ffe

Output:

giraFfe

I note the capital F.I am using Access 2007 Beta, but I doubt that explains
it.

Full stop, followed by space - could it be that the REPLACE function is
capitalizing the first letter of a new "sentence"?

Well I failed. I don't have enough knowledge about code to implement this
into my database. One of the responses though mentioned doing it through

[quoted text clipped - 29 lines]