Text Formulas
I was wondering if someone could hel me with the following problem. I have
several names and email addresses that are in Excel and that I needto seperate the email addresses from the string of characters. The list is in column A. ) What I want to end up with is Thanks for any help -- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida |
Text Formulas
Hi Lowell,
A1: ) B1: =IF(A1="","",LEFT(A1,FIND("(",A1)-1)) C1: =IF(A1="","",MID(A1,FIND("(",A1)+1, LEN(A1)-LEN(B1)-2)) See my page on strints for some OTHER help with strings: http://www.mvps.org/dmcritchie/excel/stings.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Lowell" wrote in message ... I was wondering if someone could hel me with the following problem. I have several names and email addresses that are in Excel and that I needto seperate the email addresses from the string of characters. The list is in column A. ) What I want to end up with is Thanks for any help -- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida |
Text Formulas
Let me try that again to correct reference on my site:
A1: ) B1: =IF(A1="","",LEFT(A1,FIND("(",A1)-1)) C1: =IF(A1="","",MID(A1,FIND("(",A1)+1, LEN(A1)-LEN(B1)-2)) See my page on strings for some OTHER help with strings: http://www.mvps.org/dmcritchie/excel/strings.htm |
Text Formulas
Is the email address always enclosed by parenthesis ending with one?
if so it's pretty easy =SUBSTITUTE(MID(A1,FIND("(",A1)+1,1024),")","") -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Lowell" wrote in message ... I was wondering if someone could hel me with the following problem. I have several names and email addresses that are in Excel and that I needto seperate the email addresses from the string of characters. The list is in column A. ) What I want to end up with is Thanks for any help -- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida |
Text Formulas
One more technique.
Say your list is in column A. Copy it to column B (Now you have two identical columns) Select column A edit|replace what: (* (open parenthesis followed by asterisk) with: (leave blank) replace all Select column B edit|replace what: *( (asterisk followed by open parenthesis) with: (leave blank) replace all and one more time for the closing ). Edit|replace what: ) with: (leave blank) replace all Lowell wrote: I was wondering if someone could hel me with the following problem. I have several names and email addresses that are in Excel and that I needto seperate the email addresses from the string of characters. The list is in column A. ) What I want to end up with is Thanks for any help -- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida -- Dave Peterson |
Text Formulas
Thanks for the help. Your solutions worked great. If you could explain what
the formula means it would help me understand. Or if you could recommend a source to study text formulas I would appreciate the information. Regards, Lowell Shoaf -- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida "Peo Sjoblom" wrote: Is the email address always enclosed by parenthesis ending with one? if so it's pretty easy =SUBSTITUTE(MID(A1,FIND("(",A1)+1,1024),")","") -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Lowell" wrote in message ... I was wondering if someone could hel me with the following problem. I have several names and email addresses that are in Excel and that I needto seperate the email addresses from the string of characters. The list is in column A. ) What I want to end up with is Thanks for any help -- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida |
Text Formulas
Hi Lowell,
A1: C1: =SUBSTITUTE(MID(A1,FIND("(",A1)+1,1024),")","") A good place to start would be HELP, starting with\ the innermost function and working your way out FIND Worksheet Function find the position of "(" within A1 MID Worksheet Function extract the string after that position through the end as 1024 is a resonably high number it would include normally everything through the end. SUBSTITUTE Worksheet Function remove any and all ")" which in this case would be the last character You might want to modify the formula so that you will get a null string instead of a #VALUE! error if A1 is empty C1: =IF(A!="","",SUBSTITUTE(MID(A1,FIND("(",A1)+1,1024 ),")","") ) If you have trouble understanding the formulas, you might start with Chip Pearson's page on Nesting Functions and of course looking in HELP is always a good idea. http://www.cpearson.com/Excel/nested.htm A page that might be of help is my page on strings http://www.mvps.org/dmcritchie/excel/strings.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Lowell" wrote in message ... Thanks for the help [Peo's solution] . Your solutions worked great. If you could explain what the formula means it would help me understand. Or if you could recommend a source to study text formulas I would appreciate the information. |
All times are GMT +1. The time now is 03:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com