String Manipulation

  • Thread starter Thread starter Melinda
  • Start date Start date
M

Melinda

I need to be able to strip off the non numeric characters
from a string. For example, in the string "12345ABC", I
need to strip off the "ABC" and be left with
only "12345". Any suggestions?

Thanks.
 
Melinda,

If your string *always* starts with numbers, you can use the Val() function,
which stops as soon as it hits a non-numeric character; For example:

Val("12345ABC") will return 12345
 
Melinda said:
I need to be able to strip off the non numeric characters
from a string. For example, in the string "12345ABC", I
need to strip off the "ABC" and be left with
only "12345".
Click to expand...

Dim strNum As String
Dim K as Integer
For K = 1 to Len(yourstring)
If Mid(yourstring, K, 1) Like "*[!0-9]*" Then
Exit function
End If
strNum = strNum & Mid(yourstring, K, 1)
Next K
 
Marsh,

Can I use this in a query to update a field on a table?

Thanks.

Melinda
-----Original Message-----
Melinda said:
I need to be able to strip off the non numeric characters
from a string. For example, in the string "12345ABC", I
need to strip off the "ABC" and be left with
only "12345".
Click to expand...

Dim strNum As String
Dim K as Integer
For K = 1 to Len(yourstring)
If Mid(yourstring, K, 1) Like "*[!0-9]*" Then
Exit function
End If
strNum = strNum & Mid(yourstring, K, 1)
Next K
Click to expand...
 
Can I use this in a query to update a field on a table?
Click to expand...


If you make a function out of it, yes. But there are lots
of ways to update records, so tell me more.

OTOH, Cheryl's suggestion may be all you need. Give it a
try using a Select query before trying to make a big time
update. Just in case there are unintended consequences, be
sure to back up the data before doing anything.
--
Marsh
MVP [MS Access]


-----Original Message-----
Melinda said:
I need to be able to strip off the non numeric characters
from a string. For example, in the string "12345ABC", I
need to strip off the "ABC" and be left with
only "12345".
Click to expand...

Dim strNum As String
Dim K as Integer
For K = 1 to Len(yourstring)
If Mid(yourstring, K, 1) Like "*[!0-9]*" Then
Exit function
End If
strNum = strNum & Mid(yourstring, K, 1)
Next K
Click to expand...
Click to expand...
 
Back
Top