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'

Easy if you start from the back:

=LEFT(RIGHT(A1,11),10)
--
Gary''s Student - gsnu200812

Hi,

This works for your posted example

=MID(A2,FIND("(",A2)+4,10)

Mike

"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'

If it is possible that, in some circumstances, there might be text following
the right parenthesis, the give this a try...

=MID(A1,FIND(")",A1)-10,10)

which will return the "date" as text... if you need it as a real date (which
you can format anyway you want using Format/Cells), then use this...

=--MID(A1,FIND(")",A1)-10,10)

--
Rick (MVP - Excel)


"Seanie" wrote in message
...
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'

Hi,

All the suggestions you have recieved work for your example, here is a more
generic solution:

=MID(A1,FIND("(",A1)+4,FIND(")",A1)-FIND("(",A1)-4)

or if you want the data as a value

=--MID(A1,FIND("(",A1)+4,FIND(")",A1)-FIND("(",A1)-4)

These will work if your dates are entered as 1/1/01 or anything else other
than the 10 digit date format shown in your post. They will also handle
trailing text, after the ) such as a period. However, they won't handle a
string longer or short than "as ".

--
Thanks,
Shane Devenshire


"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'

One more extract Text which I tried to tweak based on formulas above
but couldn't get to work. My text in A2 is

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

I wish to extract just the text where the month is displayed i.e. for
above "November'08". Just to point out that next May my text string
above would chnage length in that 12 Months to May'09 etc, I wish any
formula to be dynamic and adjust for that

Thanks

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