How to remove space special characters within cells ?

[Pradeep Pradhan]

Hello,


Can anyone help on this? Data may extend to 1000 cells


http://www.fileconvoy.com/dfl.php?id=g4a11ebfd720bbd2c999242718585202ff4493f8b6


best wishes

pradeep

 

[nazmul_muneer]

Dear Pradeep,

Plz try this according to your sample data,

=SUBSTITUTE(SUBSTITUTE(B4," ",""),"-","")


Regards,


Muneer

 

[oldchippy]

Hi Pradeep,


Try this one


=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B4)," ",""),"-",""),"$","")

 

[Pradeep Pradhan]

Hi,


But special characters are not restricted to "-" ,"$" only. It may be anything.


What will happen in that case?Can i use the same formula to get the results?


best wishes


pradeep

 

[oldchippy]

Hi,


Is it numbers, then characters, then a single letter?

 

[Pradeep Pradhan]

Hello,


Pls check it.


http://www.fileconvoy.com/dfl.php?id=ga1569b55f9e55ac599924281560c53d0938f63b2c


cheers

pradeep

 

[sgmpatnaik]

@Pradeep


Hi


for my self create on UDF code in your file that is

[pre]

Function removeSpecial(sInput As String) As String
Dim sSpecialChars As String
Dim i As Long
sSpecialChars = "#,$-/:*?""<>| "
For i = 1 To Len(sSpecialChars)
sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "")
Next
removeSpecial = sInput
End Function

[/pre]

then try in D4 as


=SUBSTITUTE(removeSpecial(B4),"","")


Hope it will clear


Note: - If you have more special characters then you can add in the array if you wish


Thanks


SP

 

[Marc L]

Hi Pradeep Pradhan !   Maybe it will be easier if you tell us what characters you want to keep …

Like only alphabetics & numbers ones, any punctuation marks ? …

 

[Pradeep Pradhan]

Hi Marc,


Want to keep only alphabets & numbers.


thanks

pradeep

 

[sgmpatnaik]

@Pradeep


Hi


Is that my solution is not solve your problem if not then please inform


Thanks


SP

 

[Pradeep Pradhan]

solved.

thanks


pradeep