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

On Thu, 3 Jul 2008 10:51:02 -0700, Tacrier .
wrote:

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

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


Replace @mailinator.com with @tiscali.co.uk


"Tacrier" . wrote in message
...
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

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("(",A 1)+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


"Tacrier" . wrote in message
...
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

Hi,

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

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

Mike

"Tacrier" wrote:

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

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 '('

"Tacrier" wrote:

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

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

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

Rick


"Tacrier" . wrote in message
...
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

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

:)

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Tacrier" . wrote in message
...
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