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

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

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

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