Extract data from bracket
Dear all,
I have 3 coulumns of data A B C May (17) May 17 Peter (25) Peter 25 Jason (5) Jason 5 By formula, how to extract column B and C from column A? Thank you Kent |
Extract data from bracket
Try these...
B1: =LEFT(A1,FIND(" ",A1)-1) C1: =--SUBSTITUTE(MID(A1,FIND("(",A1)+1,10),")","") -- Biff Microsoft Excel MVP "Kent" wrote in message ... Dear all, I have 3 coulumns of data A B C May (17) May 17 Peter (25) Peter 25 Jason (5) Jason 5 By formula, how to extract column B and C from column A? Thank you Kent |
Extract data from bracket
Dear Valko,
B1 works but C1 only returns an #VALUE! Kent "T. Valko" .gbl... Try these... B1: =LEFT(A1,FIND(" ",A1)-1) C1: =--SUBSTITUTE(MID(A1,FIND("(",A1)+1,10),")","") -- Biff Microsoft Excel MVP "Kent" wrote in message ... Dear all, I have 3 coulumns of data A B C May (17) May 17 Peter (25) Peter 25 Jason (5) Jason 5 By formula, how to extract column B and C from column A? Thank you Kent |
Extract data from bracket
Works OK for me on your posted sample data.
Are there any non-numbers within the ( )? May (x) Peter (10A) There may also be unseen whitespace characters like HTML char 160 at the end of the string. May (17)<char 160 Try one of these in C1: =--SUBSTITUTE(LEFT(A1,FIND(")",A1)-1),B1&" (","") =SUBSTITUTE(LEFT(A1,FIND(")",A1)-1),B1&" (","") If there are non-numbers within the ( ) then the 1st one will still return an error. The 2nd one will return whatever's inside the ( ) as TEXT (even if it's a number). -- Biff Microsoft Excel MVP "Kent" wrote in message ... Dear Valko, B1 works but C1 only returns an #VALUE! Kent "T. Valko" .gbl... Try these... B1: =LEFT(A1,FIND(" ",A1)-1) C1: =--SUBSTITUTE(MID(A1,FIND("(",A1)+1,10),")","") -- Biff Microsoft Excel MVP "Kent" wrote in message ... Dear all, I have 3 coulumns of data A B C May (17) May 17 Peter (25) Peter 25 Jason (5) Jason 5 By formula, how to extract column B and C from column A? Thank you Kent |
Extract data from bracket
Dear Valko,
You're right. Pure alpha-numeric works with your formula but my stuffs inside bracket are Chinese. Thanks very much for your assistance. Kent "T. Valko" bl... Works OK for me on your posted sample data. Are there any non-numbers within the ( )? May (x) Peter (10A) There may also be unseen whitespace characters like HTML char 160 at the end of the string. May (17)<char 160 Try one of these in C1: =--SUBSTITUTE(LEFT(A1,FIND(")",A1)-1),B1&" (","") =SUBSTITUTE(LEFT(A1,FIND(")",A1)-1),B1&" (","") If there are non-numbers within the ( ) then the 1st one will still return an error. The 2nd one will return whatever's inside the ( ) as TEXT (even if it's a number). -- Biff Microsoft Excel MVP "Kent" wrote in message ... Dear Valko, B1 works but C1 only returns an #VALUE! Kent "T. Valko" .gbl... Try these... B1: =LEFT(A1,FIND(" ",A1)-1) C1: =--SUBSTITUTE(MID(A1,FIND("(",A1)+1,10),")","") -- Biff Microsoft Excel MVP "Kent" wrote in message ... Dear all, I have 3 coulumns of data A B C May (17) May 17 Peter (25) Peter 25 Jason (5) Jason 5 By formula, how to extract column B and C from column A? Thank you Kent |
Extract data from bracket
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Kent" wrote in message ... Dear Valko, You're right. Pure alpha-numeric works with your formula but my stuffs inside bracket are Chinese. Thanks very much for your assistance. Kent "T. Valko" bl... Works OK for me on your posted sample data. Are there any non-numbers within the ( )? May (x) Peter (10A) There may also be unseen whitespace characters like HTML char 160 at the end of the string. May (17)<char 160 Try one of these in C1: =--SUBSTITUTE(LEFT(A1,FIND(")",A1)-1),B1&" (","") =SUBSTITUTE(LEFT(A1,FIND(")",A1)-1),B1&" (","") If there are non-numbers within the ( ) then the 1st one will still return an error. The 2nd one will return whatever's inside the ( ) as TEXT (even if it's a number). -- Biff Microsoft Excel MVP "Kent" wrote in message ... Dear Valko, B1 works but C1 only returns an #VALUE! Kent "T. Valko" .gbl... Try these... B1: =LEFT(A1,FIND(" ",A1)-1) C1: =--SUBSTITUTE(MID(A1,FIND("(",A1)+1,10),")","") -- Biff Microsoft Excel MVP "Kent" wrote in message ... Dear all, I have 3 coulumns of data A B C May (17) May 17 Peter (25) Peter 25 Jason (5) Jason 5 By formula, how to extract column B and C from column A? Thank you Kent |
Try this
Try this to get data from brackets. It works any data inside the brackets:
=LEFT((RIGHT(A1;(LEN(A1))-(FIND("(";A1))));F2-1) T. Valko wrote: You're welcome. Thanks for the feedback!--BiffMicrosoft Excel MVP 26-okt.-09 You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Pragmatic ADO.NET http://www.eggheadcafe.com/tutorials...ic-adonet.aspx |
Correction
Sorry. The full script is:
=LEFT((RIGHT(A1;(LEN(A1))-(FIND("(";A1))));(LEN((RIGHT(A1;(LEN(A1))-(FIND("(";A1))))))-1) George Slartibartfast wrote: Try this 14-nov.-09 Try this to get data from brackets. It works any data inside the brackets: =LEFT((RIGHT(A1;(LEN(A1))-(FIND("(";A1))));F2-1) Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Access Modifiers http://www.eggheadcafe.com/tutorials...modifiers.aspx |
All times are GMT +1. The time now is 07:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com