Extracting numbers from string

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

 

[Brendan Reynolds]

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?

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

 

[Wayne-I-M]

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-I-M]

Pressed send to quick.

That code was taken from a post by Fred.


--
Wayne
Trentino, Italia.

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.

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]

Thank you both for your very helpful responses.
It is very much appreciated.

I got it working

Cheerz