Isolate text immediately preceding "("

[Tacrier]

I tried unsuccessfully to isolate text immediately preceding the "(" from a
text string into another cell using:

=MID(A1,SEARCH("(",A1) -20)

My text string contains a payee name followed by their date of birth like
this:

Smith, John (DOB: Dec 16/91)

I want to put the name into cell A1 and the DOB into B1, however I want to
do this for about 100 rows and each row has different text string lengths due
to the varying name lengths.

Any suggestions?

Thanking you in advance,
Trina

 

[Ron Rosenfeld]

I tried unsuccessfully to isolate text immediately preceding the "(" from a
text string into another cell using:

=MID(A1,SEARCH("(",A1) -20)

My text string contains a payee name followed by their date of birth like
this:

Smith, John (DOB: Dec 16/91)

I want to put the name into cell A1 and the DOB into B1, however I want to
do this for about 100 rows and each row has different text string lengths due
to the varying name lengths.

Any suggestions?

Thanking you in advance,
Trina

=TRIM(LEFT(A1,FIND("(",A1)-1))

=MID(A1,FIND("(",A1),255)
--ron

 

[Sandy Mann]

For Smith, John
=LEFT(A1,FIND("(",A1)-1)

For (DOB: Dec 16/91)
=MID(A1,SEARCH("(",A1),255)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Bernie Deitrick]

Trina,

With your string in A1, enter this into B1

=LEFT(A1,FIND("(",A1)-2)

and this into C1 (format cell C1 as a date)
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("(",A1)+1,LEN(A1)),")",""),"DOB: ",""),"/",", ")

and then copy down to match your list.

Then copy and paste values to convert the formulas to values, and delete column A.

HTH,
Bernie
MS Excel MVP

 

[Mike H]

Hi,

Name
=TRIM(LEFT(A1,FIND("(",A1,1)-1))

DOB
=TRIM(MID(A1,FIND("(",A1,1),LEN(A1)))

Mike

 

[bpeltzer]

The number of characters left of the paren is just search("(",a1)-1. So use
the LEFT function rather than mid, keeping that many characters. I'd also
apply the TRIM function to remove leading and trailing spaces:
=trim(left(a1,search("(",a1)-1))
The piece beginning with the paren would be similar, but calculate the
number of characters as len(a1)+1-search("(",a1) and use the RIGHT function.
Alternately, check out the Data > Text to Columns, indicate that your data
is delimited by '('

 

[Rick Rothstein \(MVP - VB\)]

Assuming you want to pull out the DOB and convert it into a real Excel
date...

=--SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(A1,10)),")",""),"/",", ")

Rick

 

[Tacrier]

Thank you to everyone that replied! The Left and Mid functions worked great!!