On Tue, 22 Apr 2008 16:54:49 -0700 (PDT), TC wrote:

Hi Gurus

I would really appreciate if I could have a VBA script/ Macro that
will do the following:-

Extract eg 32MPA , 20MPA , 40MPA fro a text string as per below
sample.

POST TENS 32MPA 20MM 22@3 90 AIR TESTED
GREEN 60 20MPA 20MM
CONCRETE_POLISHED___32MPA_10MM_SHRINKAGE LTD
CONCRETE_DURAMIX___40MPA_20MM_MACFOLSPEC

Thanking you in anticipation.

Cheers
Tony

Here's a routine using Regular Expressions:

======================
Option Explicit
Function ExtrString(Str As String, sPattern As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Pattern = sPattern
If re.test(Str) = True Then
Set mc = re.Execute(Str)
ExtrString = mc(0).Value
End If
End Function
===============================

The pattern to extract several digits, followed by MPA is:

"\d+MPA"

So the function would be:

=ExtrString(A1,"\d+MPA")

Note that the UDF as written is case insensitive. To make the routine case
sensitive, merely delete or comment out the re.ignorecase=True line.
--ron