Extracting Data From a Text String

[techgirl10]

I have a bunch of data in a field that I need to extract into 3 different fields named: thickness, width and length.

For example, with this text string: 025PC.080X12.0X12.0

I want to extract .080 = thickness, 12.0 = width, and 12.0 = length and plug that data into their respective fields. The problem is that the measurements aren't always the same number of digits. See below.

025PC.080X12.0X12.0
1420PC.750X2.80X16.45
149PC2.0X5.0X11.5

I tried to create formulas in queries without any luck. I need to create a function to do this, but I don't know where to begin. Any help would be greatly appreciated. Thanks!

 

[JerryKlmns]

Place your function in a module

There you need to use the Split() and InStr() functions

Function myExample(TheText As String, TheElement As Byte) As  Single
Dim myElement As Single
Dim myArray(2) As String

'Cut it in pieces
myArray=Split(TheText , "X")

'Find where PC position in the first and get the rest of it
myArray(0)=Right(myArray(0), InStr(1, "PC", myArray(0))+2)

'Choose the element to return
Select Case TheElement 
   Case 1 'Thickness
      myElement = myArray(0)
   Case 2 'Width
      myElement = myArray(1)
   Case 3 'Length
      myElement = myArray(2)
   Case Else 'wrong input
      myElement = 0
End Select
'Result assigned to the function
myExample = myElement 

End Function

In a query then

SELECT myExample(yourFieldName, 1) As TheThickness,
       myExample(yourFieldName, 2) As TheWidth,
       myExample(yourFieldName, 3) As TheLength
FROM yourTable;

 

[PHV]

Jerry, I'd replace this:
myArray(0)=Right(myArray(0), InStr(1, "PC", myArray(0))+2)
with this:
myArray(0) = Mid(myArray(0), InStr(myArray(0), ".") + 1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JerryKlmns]

PHV

my line is wrong and should be
myArray(0)=Mid(myArray(0), InStr(1, myArray(0), "PC")+2)

but if you search for "." the last example returns 0 instead of 2.0

 

[PHV]

OOps, sorry.