Finding numbers in a text string

[billheath]

I have a text field which is imported from an excel spreadsheet. The text string contains data which I need to extract into 4 separate number fields.

Ezample1: "Overall vibration of .67 at posistion 2 Vertical with a PDF of .44 @ 1800 cps."
Example2: "Overall - .54 @ position 4; PDF - .53 @ 1260 cps"

I need to put the 4 numbers into 4 distinct fields.

1. OVib = .67; Pos = 2; PDF = .44; Freq = 1800
2. OVib = ..54; Pos = 4; PDF = .53; Freq = 1260

Unfortunately, the wording changes from one technician to the next. So, I can't use the common "instr" or Left$ methods. Any ideas would be greatly appreciated.
Bill

 

[Elysium]

Will the technicians use different words for the following?:

Vibration
Position
PDF
cps

For instance, could 'vibration' be written as 'v', 'vib', 'vibr' and so on? (Same goes for the other words)

If you can get them to stay within a predefined list of variations, I may have a solution for you.

Randy

 

[billheath]

Randy, Thanks for answering. Yes, the technicians will use different words. I am currently trying to test each character for a number (using If isNumeric). I still am having trouble putting it all together.
Bill

 

[Blorf]

To follow up with Randy, if the words are different, will the numbers at least always be in the same order?

Regarding isnumeric, I would suggest using something like

(M_Chr >="0" and M_Chr <= "9") or M_Chr = "."

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[EBGreen]

Is it possible that they would be in a different order? Could Vib be the second piece of data instead of the first?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[Elysium]

One more point to consider:

Will the values for Vibration and PDF always be less than one and greater than or equal to zero? Will they always be stated in hundredths? How many positions are possible? Will Frequency always follow '@' or be at least 4 digits?

Randy

 

[EBGreen]

If the data will always be in the same order, then I would use a regular expression and read the submatches into the fields.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[billheath]

Thanks to all. I finally got it to work by examining each character (From x = 1 to len(Text String) with isNumeric. Since the numbers are always in the same order and same length, I was able to then strip the numbers with the left and mid functions. I don't know if it is the best way but it works. Bill

 

[willir]

Here a modified function I wrote for another problem. It a "sledge hammer" approach, but it should work...

Function MakeNumber(strOriginal As String) As Single
Dim strFinal As String, intX As Integer, strChar As String

strFinal = ""

For intX = 1 To Len(strOriginal)
    strChar = Mid(strOriginal, intX, 1)
    
    Select Case Asc(strChar)
    
        Case 48 To 57
            '0 to 9
            strFinal = strFinal & strChar
        
        Case 46
            'decimal
            strFinal = strFinal & strChar
    
    End Select
    
Next intX

If Len(strFinal) = 0 Then
    MsgBox "Zero length string", vbOKOnly
    MakeNumber = 0
Else
    MakeNumber = CSng(strFinal)
End If

End Function

By using a function, you can call it from just about anywere, even in an SQL statement...

SELECT MakeNumber(pOVib), MakeNumber(Pos) From YourDataTable

...or a form
=MakeNumber([pOVib])
...assuming your field / column names are pOVib and Pos or the text box / control name on your form.

If you do not know how to create a module, in your database window, select "Insert" -> "Module" and cut and paste the code.

I tested the code with an alphanumeric, numeric only and date.

Richard