Extract data from bracket

[Kent]

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

 

[T. Valko]

Try these...

B1:
=LEFT(A1,FIND(" ",A1)-1)

C1:
=--SUBSTITUTE(MID(A1,FIND("(",A1)+1,10),")","")

 

[Kent]

Dear Valko,

B1 works but C1 only returns an #VALUE!


Kent

 

[T. Valko]

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).

 

[Kent]

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]

You're welcome. Thanks for the feedback!

 

[George Slartibartfast]

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/tutorial...-481a-ae64-fef7fa935d71/pragmatic-adonet.aspx

 

[George Slartibartfast]

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/tutorial...-4e21-bd2a-cbcf05d20d5f/access-modifiers.aspx