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.aspx?scid=kb;en-us;818802&Product=vbb
http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx
--ron
