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
|