I'd use some helper columns and formulas.
Assumes your data is in A1:Axxx.
In B1, put this formula:
=MATCH(TRUE,ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
In C1, put this formula:
=SEARCH("source:",A1)
In D1, put this formula:
=TRIM(LEFT(A1,B1-1))
In E1:
=--MID(A1,B1,C1-B1)
In F1:
=TRIM(MID(A1,C1+LEN("source:"),255))
And select b1:F1 and drag down as far as you need.
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
--
Dave Peterson
|