How to remove special characters and spaces

[darshans]

Hi There, I am quite a fresher in excel, how do i remove special characters and spaces from a cell:

example of my data in a column (B)

ver2.3 aabc.doc

ver2.0 aaebc.xls

ver1.1 adfhadf.doc

and so on there are like 2000 entries please help

 

[vijaySharma]

Hi darshans,


Welcome to Chandorg Forums.


The below formula will get rid of the SPACE and PERIOD.


=SUBSTITUTE(SUBSTITUTE(B1," ",""),".","")


~VijaySharma

 

[darshans]

Thanks Vijay, this helps to an extent.

What if some cells have special characters like ! or -, lets say it should check for any special character and then remove it.

do i add substitute for every character i will be trying that for now but there will defiantly be an alternate way

 

[vijaySharma]

dahshans,


Have a look at the below link...


http://www.mrexcel.com/forum/showthread.php?t=399784


Contains the UDF for search and replace using VBA code..


SUBSTITUTE can be nested only 8 times


~VijaySharma