I would like to create a formula that removes a specific character if it appears in a cell. In this case, if the text in the name cell starts with * or #, remove it. Otherwise, keep the contents intact. Examples:
Is there a formula that will get me where I want to be?
Please Login or Register to view this content.
Thanks!
Chuck
Last edited by chasfh; 04-23-2010 at 11:08 AM.
Try
=Substitute(Substitute(A1,"*",""),"#","")
you can nest up to 7 of these functions in Xl2003 or earlier
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
If you are not concerned where within the string the characters appear then
=SUBSTITUTE(SUBSTITUTE(A2,"*",""),"#","")
If you are then,
=IF(OR(LEFT(A2,1)="*",LEFT(A2,1)="#"),MID(A2,2,LEN(A2)),A2)
Last edited by Andy Pope; 04-19-2010 at 10:10 AM.
Works perfectly! Thanks!
Please use this Formula for Special Characters: f5 ur column
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F5," ",""),".",""),"%",""),"$",""),"#",""),"*",""),"/",""),")",""),"(","")
What if the specific character is "
CHAR(34) is the double quotes, so you could do something like this:Originally Posted by vraj198
=SUBSTITUTE(A10,CHAR(34),"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks