A Difficult Unconcatinate Problem
"Ron Rosenfeld" wrote:
On Tue, 13 Sep 2005 05:57:13 -0700, "RestlessAde"
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
|