Isolate text immediately preceding "("
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 |
Isolate text immediately preceding "("
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 |
Isolate text immediately preceding "("
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 |
Isolate text immediately preceding "("
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 |
Isolate text immediately preceding "("
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 |
Isolate text immediately preceding "("
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 |
Isolate text immediately preceding "("
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 |
Isolate text immediately preceding "("
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 |
All times are GMT +1. The time now is 12:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com