"Ron Rosenfeld" wrote:

> On Tue, 13 Sep 2005 05:57:13 -0700, "RestlessAde"
> <RestlessAde@discussions.microsoft.com> wrote:
>
> >Hi,
> >
> >I have tried to solve this problem using a mixture of text functions such as
> >RIGHT, LEN, LEFT, FIND etc. However, am still stuck. Would appreciate any
> >suggestions.
> >
> >I'm trying to split out the following data stored in a single column into
> >three columns: Country, Data Value and Source.
> >
> > Angola 13,294,000 Source: ibid.
> > Anguilla 13,000 Source: ibid.
> > Antigua and Barbuda 76,000 Source: ibid.
> > Argentina 37,880,000 Source: ibid.
> > Armenia 3,206,000 Source: ibid.
> > Aruba 94,000 Source: ibid.
> > Australia 20,125,000 Source: ibid.
> >
> >The problem I'm having relates to the fact that some countries contain more
> >than one word, so I can't just search for the first " ". I think the answer
> >is to somehow detect the first instance of a numeric value, but I have no
> >idea how to do this.
> >
> >Thanks,
> >RA
>
> Array formulas will do this. The first two are array formulas; the last is
> not. To enter an array formula, be sure to hold down <ctrl><shift> while
> hitting <enter>. Excel will place braces {...} around the formula.
>
> Assuming data is in A2.
>
> Country (array formula):
>
> =LEFT(A2,-1+MATCH(FALSE,ISERROR(-MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),0))
>
> Data Value (array formula):
>
> =MID(A2,MATCH(FALSE,ISERROR(-MID(A2,ROW(
> INDIRECT("1:"&LEN(A2))),1)),0),-1+FIND("Source",A2)
> -MATCH(FALSE,ISERROR(-MID(A2,ROW(
> INDIRECT("1:"&LEN(A2))),1)),0))
>
> Source (NOT an array formula)
>
> =MID(A3,FIND("Source: ",A3)+8,255)
>
>
> --ron
>


Ron (or anyone else for that matter),

The first array formula cited above has solved a very major headach I've
been working on all day as it can be modified to give the position of the
first number in a mixed string of numbers and letters and for that you
deserve major kudos!

However I hate not knowing how it is achieved! Can you explain in plain
english how this is working?

I guess this may help me get my head around a few other thorny issues I have
pending and it may just help out a few others here too

Thanks in advance

R.Douthwaite