Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Change List Based on Value Chosen in Another List | Excel Worksheet Functions | |||
Can you extract URL values from a list of cells? | Excel Worksheet Functions | |||
Extract each item in the list | Excel Worksheet Functions | |||
List ? - How do I make information in one cell determine list to u | Excel Worksheet Functions | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) |