Text to Columns?

[koolwaters]

Hi!

I have data in a worksheet that I need parsed but the text to columns does not work.

This is the format of my data:

Jane Doe (1)
Company A (SRL) (105)
Company B (37)

I need to split the data into columns, separating the names and the numbers. I cannot use the parenthesis as my delimter because there will be names that contain parentheses. I would like to split the data, so that the names are in one columns and the numbers including the parentheses are in a separate column.

Any suggestions?

Thanks for the help.

 

[koolwaters]

I have been playing around with the formula below but it does not do what I want.

=MID(A1,1,FIND(")",A1,1))

 

[koolwaters]

Think I got it using this function.

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))>1,MID(A1,1,FIND(")",A1,1)+1),MID(A1,1,FIND("(",A1,1)-1))

 

[Haseeb Avarakkan]

Hello, Try

C1, for the number,

=SUBSTITUTE(REPLACE(A1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1,""),")","")

B1, for the name

=SUBSTITUTE(A1,TEXT(C1,"(0)"),"")