A Difficult Unconcatinate Problem

"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

On Tue, 24 Jan 2006 11:27:51 -0800, R.Douthwaite
<RDouthwaite@discussions.microsoft.com> wrote:

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



=LEFT(A2,-1+MATCH(FALSE,ISERROR(-MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),0))

If you separate out the various nestings:

MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)

This segment returns an array of each character in A2. (The ROW(INDIRECT(...)
returns an array of "1:n" where n is the number of characters in A2.

The next step is to put a minus sign <-> in front of each character:

-MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)

If the character is NOT a number, the array will return an error; otherwise it
will return a number.

We then Test each character position to see if there is an error:

ISERROR(-MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))

This returns an array of {TRUE,TRUE,FALSE...} depending on whether there is a
digit.

The MATCH function with the 0 match_type argument then finds the first FALSE
which would be the location of the first digit.

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

Since September, however, I've become enamored of "regular expressions". One
way of using these effectively in Excel is to download and install Longre's
morefunc.xll add-in from http://xcell05.free.fr/

Then, to find the position of the first number in the above, one could use the
much simpler formula:

=REGEX.FIND(A1,"\d")

To extract the first number, where it might contain commas:

=REGEX.MID(A1,"(\d+|,)+")

Depending on your data, these can be much more flexible.


--ron

--
R.Douthwaite.
Information Coordinator
www.swift-research.co.uk


"Ron Rosenfeld" wrote:

> On Tue, 24 Jan 2006 11:27:51 -0800, R.Douthwaite
> <RDouthwaite@discussions.microsoft.com> wrote:
>
> >"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
>
>
>
> =LEFT(A2,-1+MATCH(FALSE,ISERROR(-MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),0))
>
> If you separate out the various nestings:
>
> MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)
>
> This segment returns an array of each character in A2. (The ROW(INDIRECT(...)
> returns an array of "1:n" where n is the number of characters in A2.
>
> The next step is to put a minus sign <-> in front of each character:
>
> -MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)
>
> If the character is NOT a number, the array will return an error; otherwise it
> will return a number.
>
> We then Test each character position to see if there is an error:
>
> ISERROR(-MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))
>
> This returns an array of {TRUE,TRUE,FALSE...} depending on whether there is a
> digit.
>
> The MATCH function with the 0 match_type argument then finds the first FALSE
> which would be the location of the first digit.
>
> ================================
>
> Since September, however, I've become enamored of "regular expressions". One
> way of using these effectively in Excel is to download and install Longre's
> morefunc.xll add-in from http://xcell05.free.fr/
>
> Then, to find the position of the first number in the above, one could use the
> much simpler formula:
>
> =REGEX.FIND(A1,"\d")
>
> To extract the first number, where it might contain commas:
>
> =REGEX.MID(A1,"(\d+|,)+")
>
> Depending on your data, these can be much more flexible.
>
>
> --ron
>

Ron,

Thank you for the excellent explaination. I think you just opened up a whole
new world to me... Not sure that's necessarily a good thing :-)

R.Douthwaite

On Tue, 24 Jan 2006 15:39:02 -0800, R.Douthwaite
<RDouthwaite@discussions.microsoft.com> wrote:

>Ron,
>
>Thank you for the excellent explaination. I think you just opened up a whole
>new world to me... Not sure that's necessarily a good thing :-)
>
>R.Douthwaite

You're very welcome.
--ron