Extracting numbers from string

  • Thread starter Thread starter Jochen
  • Start date Start date
J

Jochen

I have a textfield which holds a string of text and numbers mixed together
I want to create a new field and paste only the numbers in it

Example:

Field1: 5DE4G5SE6SR9C78
Field2: 5456978

How would I go about it?
 
Jochen said:
I have a textfield which holds a string of text and numbers mixed together
I want to create a new field and paste only the numbers in it

Example:

Field1: 5DE4G5SE6SR9C78
Field2: 5456978

How would I go about it?
Click to expand...


The following VBA function will return only the digits in the string ....

Public Function DigitsOnly(ByVal strInput) As String

Dim lngChar As Long
Dim strWork As String

For lngChar = 1 To Len(strInput)
If InStr(1, "0123456789", Mid$(strInput, lngChar, 1)) > 0 Then
strWork = strWork & Mid$(strInput, lngChar, 1)
End If
Next lngChar

DigitsOnly = strWork

End Function

Add the function to a standard module (not a form, report, or other class
module) and you could use it in an update query something like so ....

UPDATE YourTableName SET YourNewFieldName = DigitsOnly([YourOldFieldName])
WHERE YourOldFieldName IS NOT NULL
 
Hi

Create a new module with this

Public Function GetTheNumbersOut(strIn As String) As Long
Dim intY As Integer
Dim intX As Integer
For intY = 1 To Len(strIn)
intX = Asc(Mid(strIn, intY))
If intX >= 48 And intX <= 58 Then
GetTheNumbersOut = GetTheNumbersOut & Mid(strIn, intY, 1)
End If
Next intY
End Function


Create a new query with this

SELECT GetTheNUmbersOut([TableName]![TextField]) AS SomeName
FROM TableName;

Change TableName & TextField to what they really are
 
Pressed send to quick.

That code was taken from a post by Fred.


--
Wayne
Trentino, Italia.



Wayne-I-M said:
Hi

Create a new module with this

Public Function GetTheNumbersOut(strIn As String) As Long
Dim intY As Integer
Dim intX As Integer
For intY = 1 To Len(strIn)
intX = Asc(Mid(strIn, intY))
If intX >= 48 And intX <= 58 Then
GetTheNumbersOut = GetTheNumbersOut & Mid(strIn, intY, 1)
End If
Next intY
End Function


Create a new query with this

SELECT GetTheNUmbersOut([TableName]![TextField]) AS SomeName
FROM TableName;

Change TableName & TextField to what they really are


--
Wayne
Trentino, Italia.



Jochen said:
I have a textfield which holds a string of text and numbers mixed together
I want to create a new field and paste only the numbers in it

Example:

Field1: 5DE4G5SE6SR9C78
Field2: 5456978

How would I go about it?
Click to expand...
Click to expand...
 
Thank you both for your very helpful responses.
It is very much appreciated.

I got it working

Cheerz
 
Back
Top