On Wed, 02 Jul 2008 22:25:20 +0100, Hankjam ........
wrote:

Hi

I've tried variations on MID, FIND, LEN, LEFT and Right and still not
able to do this.

I have strings that can be 89, 90, 91 or 92 characters long and
sometimes more.

I want to find the name of an enzyme in this string that can vary to
between 5 and 12 characters.

Something like this:

Plate Plan for Daughterboard 286338 (05_jul08_0036) for XXX(h) at 12.0
ATP Concentration
Plate Plan for Daughterboard 286338 (05_jul08_0036) for XXXX(h) at
100.0 ATP Concentration
Plate Plan for Daughterboard 286338 (05_jul08_0036) for XXXX(h) at
45.0 ATP Concentration

I want to list in separate cells:
1) the number of the Daughterboard (286338)
2) the name of the kinase (XXX(h))
3) The ATP concentration

and I don't want to column pharse, if that is the right term.

Many thanks for your time

Andrew j

Depending on how representative your data above is, the following VBA Macro
might do what you want.

To enter the macro, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, select some range that has the data to be parsed. Then <alt-F8
opens the macro dialog box. Select the ParseEnzymeString macro and <RUN. The
data you requested will be entered into the three adjacent columns.

If this is not what you want, you will need to be more specific as to your data
formats.

=========================================
Option Explicit
Sub ParseEnzymeString()
Dim c As Range
Dim re As Object, mc As Object, m As Object
Dim i As Long
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Pattern = "^.*?(\d+).*?for\s+(\S+).*?(\d\S+)"
For Each c In Selection
Range(c.Offset(0, 1), c.Offset(0, 3)).ClearContents
If re.test(c.Value) Then
Set mc = re.Execute(c.Value)
For i = 1 To 3
c.Offset(0, i).Value = mc(0).submatches(i - 1)
Next i
End If
Next c
End Sub
==============================
--ron