How to Remove Printable Characters

[KishorKK]

Hi,


As we have formula to clean non-printable characters but do we have any formula to remove printable characters. Please let me know?

Thanks
Kishor

 

[Khalid NGO]

Hello Kishor, Good Day...

Why you need this?

I think there is no built in function in excel to remove printable characters.

Regards,

 

[KishorKK]

Actually i received a test to remove the characters like @,#,$ before names like
#Kishor
@chandoo

So, is there any formula to remove those ones before name

 

[narunfca]

Dear Kishor

Can be done through user defined function.

Press Alt+f11 for VBA, right click on VBA project and insert a module.

Type the following code.

Function REMPC(MyText As String) As String

ResText = ""

For I = 1 To Len(MyText)

If Mid(MyText, I, 1) = " " Or (Mid(MyText, I, 1) >= "0" And Mid(MyText, I, 1) <= "9") Or (Mid(MyText, I, 1) >= "A" And Mid(MyText, I, 1) <= "Z") Or (Mid(MyText, I, 1) >= "a" And Mid(MyText, I, 1) <= "z") Then ResText = ResText + Mid(MyText, I, 1)

Next I

REMPC = ResText
End Function

Now use the function =REMPC(Text) to remove the printable characters.

with best regards
Arun N

 

[Khalid NGO]

Hi,

If these characters are always at starting position, you can try the following:

=RIGHT(A1,LEN(A1)-1)
=REPLACE(A1,1,1,)

Regards,

 

[Chihiro]

If it's short list of special characters and if string could start with either special character or regular... then I'd do something like below.
=RIGHT(A1,LEN(A1)-OR(LEFT(A1,1)={"?","!","#"}))

Array {} should contain list of starting special character that should be removed.

 

[KishorKK]

Thanks guys, i will try this steps. really awesome people here

 

[bobhc]

Find and replace