If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Extract information from the list
Hi. I think it's going to be difficult to satisfy my requests. Anyway, I have a list of symbols in column A like the following: 00001 = xxx (it refers to...) 00305 = ysu (it refers to...) 01203 = ghst (it refers to...) 04506 = bkse (it refers to...) 12340 = fist (it refers to...) .... Does anyone know if there're any function which can do the following: 1) I would like to take the (number) symbol out to column B. Any redundant "0" has to be removed (eg 00001 -- 1; 01203 -- 1203). 2) I would like to take the text symbol out to column C (eg xxx, ysu, ghst) 3) Finally, the description to column D. Remove the brackets. Remove the phrase "it refers to". As a note, it's best if it can be done by a function as the list is frequently updated. Thank you! ============= Windows XP Office XP |
#2
|
|||
|
|||
Extract information from the list
Assume the list starts in A2, in B2
=--LEFT(A2,FIND(" ",A2)-1) copy down in C2 =TRIM(MID(LEFT(A2,FIND("(",A2)-1),FIND("=",A2)+1,255)) copy down in D2 =TRIM(SUBSTITUTE(MID(A2,FIND("(",A2)+13,255),")"," ")) copy down -- Regards, Peo Sjoblom http://nwexcelsolutions.com "0-0 Wai Wai ^-^" wrote in message ... Hi. I think it's going to be difficult to satisfy my requests. Anyway, I have a list of symbols in column A like the following: 00001 = xxx (it refers to...) 00305 = ysu (it refers to...) 01203 = ghst (it refers to...) 04506 = bkse (it refers to...) 12340 = fist (it refers to...) ... Does anyone know if there're any function which can do the following: 1) I would like to take the (number) symbol out to column B. Any redundant "0" has to be removed (eg 00001 -- 1; 01203 -- 1203). 2) I would like to take the text symbol out to column C (eg xxx, ysu, ghst) 3) Finally, the description to column D. Remove the brackets. Remove the phrase "it refers to". As a note, it's best if it can be done by a function as the list is frequently updated. Thank you! ============= Windows XP Office XP |
#3
|
|||
|
|||
Extract information from the list
Assume the list starts in A2, in B2 =--LEFT(A2,FIND(" ",A2)-1) copy down WoW! It works like a charm. However I couldn't figure out why it works. I understand you use "FIND(" ",A2)-1" to locate the position of space. But why does it work to remove extra "0"? It seems to do with the 2 magic "minus". (I still figuring out the rest of your codes! I'll have another reply soon.) Best |
#4
|
|||
|
|||
Extract information from the list
Assume the list starts in A2, in B2 =--LEFT(A2,FIND(" ",A2)-1) copy down WoW! It works like a charm. However I couldn't figure out why it works. I understand you use "FIND(" ",A2)-1" to locate the position of space. But why does it work to remove extra "0"? It seems to do with the 2 magic "minus". (I still figuring out the rest of your codes! I'll have another reply soon.) Best |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Count in a query | Ben | Running & Setting Up Queries | 21 | April 4th, 2006 10:12 PM |
Extract each item in the list | 0-0 Wai Wai ^-^ | Worksheet Functions | 2 | September 8th, 2005 12:50 PM |
List ? - How do I make information in one cell determine list to u | Brad_A | Worksheet Functions | 1 | January 18th, 2005 04:10 PM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
Filtering information in a list box | Deb Smith | Using Forms | 5 | June 5th, 2004 04:04 AM |