Extract Numbers from a text field

F

Falcon88

Registered User.
Local time
Today, 03:10
Joined
Nov 4, 2014
Messages
309
all dears
hiiiii
i have a text field contains myresult filed (a text type).

how to extract only numbers ?
the spaces is vary (unknown), between texts and numbers.
 

Attachments

Could you post the examples of the text? I cannot download.
 
Ranman256 said:
Could you post the examples of the text? I cannot download.
Click to expand...

REACTIVE 1/160
> 100
REACTIVE >650
Positive 482.5
NON REACTIVE 0.620
REACTIVE >500
Negative 1/80
NON REACTIVE 0.240
REACTIVE > 600
NON REACTIVE 0.130
Negative 1/40



​
 
Last edited:
Falcon88 said:


REACTIVE 1/160
> 100
REACTIVE >650
Positive 482.5
NON REACTIVE 0.620
REACTIVE >500
Negative 1/80
NON REACTIVE 0.240
REACTIVE > 600
NON REACTIVE 0.130
Negative 1/40



​
Click to expand...


i can't make spaces between texts and numbers in this post.

take in consideration there are unknown spaces between texts and numbers.
 
what number do you want for this one

Negative 1/40

40? or 0.025?

and this one

NON REACTIVE 0.620

0.620? or 620?

i.e. decimal numbers, not integer?

Also, are your numbers always on the right or can they also be on the left or in the middle?

@ranman, looking at the file there are multiple spaces e.g. substitute a space for each dot in this example

Positive..............482.5
Click to expand...
 
CJ_London said:
what number do you want for this one

Negative 1/40

40? or 0.025?
Click to expand...
40
CJ_London said:
and this one

NON REACTIVE 0.620

0.620? or 620?
Click to expand...
0.620

CJ_London said:
i.e. decimal numbers, not integer?

Also, are your numbers always on the right or can they also be on the left or in the middle?



@ranman, looking at the file there are multiple spaces e.g. substitute a space for each dot in this example
Click to expand...

in the right and sometimes in the middle
 
can you provide some examples of numbers 'in the middle'

do they include things like 0.620 and 1/40?

in order to extract the numbers, you need to be able to determine a pattern - e.g. if always on the right, then inspect the last few characters.

With what you have said so far there is no pattern, further complicated by the numbers themselves not having a pattern either.

Is there any way you can tidy the input up before loading it into access?

best I can suggest at the moment is something like

first step - convert spaces to >
second step convert / to >
third step 'val' everything after the last > which will convert any consecutive number characters.

something like

val(mid(replace(replace(trim(myfield)," ",">"),"/",">"),1+instrrev(replace(replace(trim(myfield)," ",">"),"/",">"),">"))

but this won't work if the characters after a 'middle' number has a space, > or / character - because instrrev looks for the last iteration of > in the string.
 
CJ_London said:
can you provide some examples of numbers 'in the middle'

do they include things like 0.620 and 1/40?

in order to extract the numbers, you need to be able to determine a pattern - e.g. if always on the right, then inspect the last few characters.

With what you have said so far there is no pattern, further complicated by the numbers themselves not having a pattern either.

Is there any way you can tidy the input up before loading it into access?

best I can suggest at the moment is something like

first step - convert spaces to >
second step convert / to >
third step 'val' everything after the last > which will convert any consecutive number characters.

something like

val(mid(replace(replace(trim(myfield)," ",">"),"/",">"),1+instrrev(replace(replace(trim(myfield)," ",">"),"/",">"),">"))

but this won't work if the characters after a 'middle' number has a space, > or / character - because instrrev looks for the last iteration of > in the string.
Click to expand...
very thanks
this works good .

but i try to make a num. axactly after a text without space , returns 0

what's the problem?
 
as I said before, you need a pattern. You are suffering from 'definition creep' i.e. the goalposts have moved. You example does not fit the pattern.

your easiest solution would be to create a query to find all those strings which don't have a space and insert a space (or >) just in front of the number.

With respect I do not have the time to guess at what other combinations you might have. Either list them all, or do as I suggest above, ensure there is a space, > or / before every number
 
Last edited:
what's about this code
how we make in case of / like : Positive 1/160 :
Code: 
Function Seperate_Digits(T)

    'T = Text From Query

    For i = 1 To Len(T)
    
        'the ascii number of each number
        C = Asc(Mid(T, i, 1))
        
        'ascii numbers we want
        '46= .
        '47= /
        '48= 0
        '49= 1
        '50= 2
        '51= 3
        '52= 4
        '53= 5
        '54= 6
        '55= 7
        '56= 8
        '57= 9
        
        Select Case C
  
            Case 46 To 57
                Which_Letter = Which_Letter & Mid(T, i, 1)
                
        End Select
        
    Next i
    
    Seperate_Digits = Which_Letter
    
End Function

and
how about null values?
 
This function may be useful. I adjusted it to accept .-+>
Code: 
'---------------------------------------------------------------------------------------
' Procedure : fExtractStrNums
' Author    : user
' Date      : 2/27/2009
' Purpose   :   To extract only numerics from a string of mixed alpha,
'numerics and special chars.
'---------------------------------------------------------------------------------------
'
Function fExtractStrNums(ByVal strInString As String) As String
      ' From Dev Ashish
      '(Q) How do I extract only characters from a string which has both numeric and alphanumeric characters?

      '(A) Use the following function. Note that the If loop can be modified to extract either both Lower and Upper case character or either
      'Lower or Upper case characters.

      '************ Code Start **********

      Dim lngLen As Long, strOut As String
      Dim i As Long, strTmp As String

10       On Error GoTo fExtractStrNums_Error

20        lngLen = Len(strInString)
30        strOut = ""
40        For i = 1 To lngLen
50            strTmp = Left$(strInString, 1)
60            strInString = Right$(strInString, lngLen - i)
              
         'ADJUSTED CODE TO GET ONLY NUMERICS and ./>- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
         
70            If (Asc(strTmp) >= 48 And Asc(strTmp) <= 57) _
                    Or strTmp = ">" Or strTmp = "." Or strTmp = "-" Or strTmp = "/" Then
80                strOut = strOut & strTmp
90            End If
100       Next i
110       fExtractStrNums = strOut
120         On Error GoTo 0
130      Exit Function

fExtractStrNums_Error:
140       MsgBox "Error " & err.number & " (" & err.Description & ") in procedure fExtractStrNums of Module Module1"
End Function

Here are a few examples using the function

?fextractstrnums("NON REACTIVE 0.620")
0.620

?fextractstrnums("hello >1 /98")
>1/98

?fextractstrnums("text -4.62 0")
-4.620

?fextractstrnums("Positive4.5678 ")
4.5678
 
that (or something similar) was going to be my next stop:D

Will need some more work tho' because this

?fextractstrnums("hello >1 /98")
>1/98


should return 98
 
Falcon,

Please explain fully what makes "hello >1 /98" ===> 98???

and "Negative 1/40" ====>40???

It seems to me that your post's title is incorrect.

However, you can use the function I provided and add necessary logic to deal with these "fractions".
 
jdraw said:
Falcon,

Please explain fully what makes "hello >1 /98" ===> 98???

and "Negative 1/40" ====>40???

It seems to me that your post's title is incorrect.

However, you can use the function I provided and add necessary logic to deal with these "fractions".
Click to expand...

oh sir
this is not my main problem.

my main problem was how to extract numbers from a text type field.
 
Did you try the function I provided?
 
jdraw said:
Did you try the function I provided?
Click to expand...

no i found this function as the best solution to achieve what I want to do.
Code: 
Function Seperate_Digits(T As String) As String
    Dim i As Integer
    Dim C As String
    Dim Which_Letter As String

    'T = Text From Query
        If Len(T & "") = 0 Then
             Seperate_Digits = ""
             Exit Function
        End If
    For i = 1 To Len(T)
        'the ascii number of each number
        C = Asc(Mid(T, i, 1))
        'ascii numbers we want
        '46= .
        '47= /
        '48= 0
        '49= 1
        '50= 2
        '51= 3
        '52= 4
        '53= 5
        '54= 6
        '55= 7
        '56= 8
        '57= 9
        Select Case C
            Case 46, 48 To 57
                Which_Letter = Which_Letter & Mid(T, i, 1)   
            Case 47
                Which_Letter = ""        
        End Select
    Next i
    Seperate_Digits = Which_Letter
End Function
 
You must log in or register to reply here.

Similar threads

H
Extract a variable number of parts of text from a string
2
Replies
26
Views
586
AHeyne
A
P
Solved Left or Mid?
Replies
8
Views
230
prasadgov
P
T
Update Field with Exp date and batch after barcode scan
Replies
5
Views
254
arnelgp
arnelgp
essaytee
[SOLVED] VBA - Excel - how to extract the default font color
Replies
11
Views
808
Isaac
Isaac
J
Open PDF files in Nitro instead of Adobe reader
Replies
13
Views
759
Isaac
Isaac

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom