Deleting Characters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.




Cathy said:
A friend of mine wrote me a formula that appears in Column D. The formula
Click to expand...
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
Click to expand...
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 -
 
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.
 
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.
 
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
 
Back
Top