On Sun, 9 Nov 2008 11:49:23 -0800 (PST), Seanie wrote:

I saw yesterday a post on extracting art of a cells contents, I have
tried to twaek it to fit my requirements but am lost as to how to.

The formula that I tried to tweak is:

="Accounts for Period - "&LEFT(TRIM(MID(A2,FIND("-",A2)+1,99))&"
",FIND(" ",TRIM(MID(A2,FIND("-",A2)+1,99))&" ")-1) - The will return
"Accounts for Period - October'08"

The Text in A2 is

^Accounts Reconciliation - October'08 (at 08/11/2008)

What I want to extract is only the text 08/11/2008 - i.e. everything
within the brackets, but not the brackets themselves or the word 'at'

Looking at all your examples and results, it's a little tough to tell what you
want in the first instance.

For example, using your formula above:

A2: ^Accounts Reconciliation - October'08 (at 08/11/2008)
your formula-- Accounts for Period - October'08

A2: Management Accounts Reconciliation to Trial Balance - 6 Months to
November'08 (at xx/12/2008)

your formula-- Accounts for Period - 6

I would have thought that the latter should have returned either
Accounts for Period - November'08

or
Accounts for Period - 6 Months to November'08

==============================

With the variety of data you want to extract, you might be better off with a
regular expression UDF and use various expressions to parse out exactly what
you want.

For example:

To make use of this UDF, you need to enter it in a module.

1. <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.

Then it is just a matter of using the correct regular expression and
substitution string to create your results.

If you want results such as:

Accounts for Period - October'08
Accounts for Period - November'08

Then you would use:

=RegexSub(A2,"^[\s\S]*?(\S+)(?=\s*\().*","Accounts for Period - $1")

where
"^[\s\S]*?(\S+)(?=\s*\().*"
is the regular expression that allows you to extract the "string" that is just
prior to the "("

If you wanted results like:

Accounts for Period - October'08
Accounts for Period - 6 Months to November'08

Then you just need to make a small change in the regular expression resulting
in:

=RegexSub(A2,"[\s\S]*?-\s+([^-]+)(?=\s*\().*","Accounts for Period - $1")

to extract the string that is between the hyphen and the "(".
=========================

To extract the date that is in the parentheses, again, a change in the regular
expression:

=RegexSub(A2,"[^(]+\D+([\d/]+).*","$1")

=========================

and to extract the Month'YR string, you use the first formula, but change the
"replace" string:

=RegexSub(A2,"[\s\S]*?(\S+)(?=\s*\().*","$1")

---------------------------
Here is the code to be pasted in VBA:

==================================
Option Explicit
Function RegexSub(Str As String, SrchFor As String, _
ReplWith As String) As String
Dim objRegExp As Object

Set objRegExp = CreateObject("vbscript.regexp")
objRegExp.Pattern = SrchFor
objRegExp.IgnoreCase = True
objRegExp.Global = True

RegexSub = objRegExp.Replace(Str, ReplWith)

End Function
=================================

Some references on regular expressions:

http://www.regular-expressions.info/reference.html
http://support.microsoft.com/default...02&Product=vbb
http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx

--ron