Extract numbers from column in text format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a column with a mix of numbers and words.
I want to create a column with only the numbers from that column.
What is the Query-command for that? (And what does the abbreviaton stand for
- so that I can find it on Swedish version of Access as well).

Ex
Orig column New column
September 2001 2001
March -02 02
2004 2004
May
1999-05-14 19990514
 
Johanna said:
I have a column with a mix of numbers and words.
I want to create a column with only the numbers from that column.
What is the Query-command for that? (And what does the abbreviaton stand for
- so that I can find it on Swedish version of Access as well).

Ex
Orig column New column
September 2001 2001
March -02 02
2004 2004
May
1999-05-14 19990514
Click to expand...

You may have to define your own function to convert the strings, perhaps
a function like this:

Public Function StrNumbers(Text As String) As String

Dim intCharNum As Integer 'Index into string
Dim strNext As String 'One character from string
Dim strResult As String 'Numeric characters from string

For intCharNum = 1 To Len(Text)

'Get the next character from the string
strNext = Mid$(Text, intCharNum, 1)

'Test it to see if it's a numeric character
If InStr("0123456789", strNext) > 0 Then

'If so, include it in the result
strResult = strResult & strNext

End If
Next intCharNum

StrNumbers = strResult

End Function 'StrNumbers()

In the string "0123456789", if you also want to include other
characters, put them in here as well (any order will work).

Having defined that function, and assuming your original Table looks
like this:

Orig column
--------------
September 2001
March -02
2004
May
1999-05-14

.... you can define a Query like this:


[Q_NumericText]

SELECT Text.[Orig column],
StrNumbers([Text]![Orig column]) AS [New Column]
FROM [Text];

And this Query's output would look like this:

Orig column New Column
-------------- ----------
September 2001 2001
March -02 02
2004 2004
May
1999-05-14 19990514

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Vincent Johns wrote:

[...]
You may have to define your own function to convert the strings, perhaps
a function like this:

Public Function StrNumbers(Text As String) As String
Click to expand...

[...]

I forgot to mention that you do this via Tools --> Macros --> Visual
Basic Editor.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
There is no command that does this. You can write your own proc that strips
off any non-numeric characters.

See the left, right, mid, instr, and len family of string functions.
 
Hi,
what does instr and len stand for?
I couldn´t find them on my Swedish version and I guess they need to be
translated...
/Johanna

"[MVP] S.Clark" skrev:
 
Johanna said:
Hi,
what does instr and len stand for?
I couldn´t find them on my Swedish version and I guess they need to be
translated...
/Johanna
Click to expand...

In the Expression Builder that you can use in Query Design View when you
enter data into a field, open Functions --> Built-In Functions and
select the Text group. That will give you a list of the functions that
are available to you. You can find what they do via Help.

InStr() allows you to determine, in a specified string, where a
specified substring begins, if it appears there at all.

Len() allows you to determine the length of a specified string.

Neither one, by itself, will do what you wanted. See my earlier message
suggesting a "StrNumbers()" function that will come close. If it
doesn't do exactly what you want, you can change it to suit your needs.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Back
Top