Deleting Characters

[Cathy]

A friend of mine wrote me a formula that appears in Column D. The formula deletes a beginning space and ending space for what is in Column B. Here is the formula

=IF(RIGHT(B13,1)=CHAR(160),LEFT(B13,LEN(B13)-1),B13

Unfortunately my friend is out of the country at the moment. I desperatly need a formula the deletes "(" at the beginning of what is in Column A and ")" which is at the end in column A. Column A contains e-mail address that currently look like this

([email protected]

I need it to read this

(e-mail address removed)

If anyone can help ASAP, I would greatly appreciate it

Sincerely
Cathy

 

[Dave R.]

As long as this isn't going towards spam...

try

=SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")

By the way, your friend's formula only removes the space at the end, not any
beginning space.

A friend of mine wrote me a formula that appears in Column D. The formula

deletes a beginning space and ending space for what is in Column B. Here is
the formula

=IF(RIGHT(B13,1)=CHAR(160),LEFT(B13,LEN(B13)-1),B13)

Unfortunately my friend is out of the country at the moment. I desperatly

need a formula the deletes "(" at the beginning of what is in Column A and
")" which is at the end in column A. Column A contains e-mail address that
currently look like this -

 

[Norman Harker]

Hi Cathy

Try:
=MID(SUBSTITUTE(B13,CHAR(160),""),2,LEN(SUBSTITUTE(B13,CHAR(160),""))-2)

I think that you'll find that =SUBSTITUTE(B13,CHAR(160),"") is more
efficient for stripping out the leading CHAR(160).


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Cathy]

Hi Dave

Thank you. By the way, no it is not going for spam

Cathy

 

[Norman Harker]

Hi DaveR!

That won't handle leading and ending character CHAR(160)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi
in addition to the answers you already recéived you may have a look at
the following macro to clean your values:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[Debra Dalgleish]

You can remove the characters by using the Replace command:

Select the column of email addresses
Choose Edit>Replace
In the Find What box, press the space bar and type an open bracket
Leave the With box empty
Click the Replace All button
Click OK to confirm

In the Find What box, type a close bracket, press the space bar
Leave the With box empty
Click the Replace All button
Click OK to confirm

 

[Dave R.]

Ah..
I didn't see a CHAR(160) in her example text "([email protected])"