Text Formulas

L

Lowell

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.

BernardRussell([email protected])

What I want to end up with is [email protected]

Thanks for any help
 
P

Peo Sjoblom

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
 
D

Dave Peterson

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
 
L

Lowell

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
 
D

David McRitchie

Hi Lowell,

A1: BernardRussell([email protected]
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
 
Top