| Home |
| Search |
| Today's Posts |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
A Difficult Unconcatinate Problem
Thank so much...this really helped me.
"Dave Peterson" wrote: 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Display Problem | Excel Discussion (Misc queries) | |||
| autofilter problem | Excel Worksheet Functions | |||
| Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) | |||
| Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) | |||
| Difficult Sorting Problem | Excel Discussion (Misc queries) | |||
Threaded Mode
