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

 

[Vep Real]

Hi George

On my quest to find a solution to my problem I came accross this old post of yours and seemed almost perfect.

The only problem I still have with the formula is, that if after the bracket there's more text it returns that as well. (along with the closing bracket).

So basically if the string of text in a cell looked like this:

Santa Reyes (40X) years old

The result with given formula shows: 40X) years old

How can I single out 40X?

PS: I hope you're still following this forums. : )

Regards,
Vep