Removing special Characters
Forums
Hi Brian,
Love your site.
I'm writing an ideascript that takes an account name from within the ACCT_NAME field and creates a CSV file with the account name being the name of the file. An error occurs if there are special characters in the account name. How would you go about replacing all special charaters in the name with the underscore charater? Keeping in mind that there may be more than one invalid charater in one account name. A name could be A/P: Client-Data for example. In that case i would like the result to be A_P_Client_Data
I hope my question is clear enough.
Thanks Brian
Marc
Code to replace characters
Hi Marc, this is the code from the custom function. As you are using a script you can easily place it in there and run your file names through it prior to exporting them as a CSV file.
What the function does is it looks at the ASCII value of each letter (for ease I made all the characters uppercase so I only need to test for uppercase letters). The ASCII codes 65 to 90 represent the letters A to Z, so if the character is not alpha then it is changed to an underscore, if it is an alpha it is not changed. I wasn't sure if you wanted numbers also but that can be easily fixed just be expanding the if statement.
Option Explicit
Function replace_special(field As String) As String
Dim iLen As Integer
Dim sChar As String
Dim sString As String
Dim i As Integer
iLen = Len(field)
For i = 1 To iLen
sChar = Mid(field, i, 1)
If Asc(UCase(sChar)) < 65 Or Asc(UCase(sChar)) > 90 Then
sString = sString + "_"
Else
sString = sSTring + sChar
End If
Next i
replace_special = sString
End Function
Hi,
Hi,
For some reason, the code was not working. I was receiving an error message when the character was a space and when a special charater was found, the code would place give me an error message and replace everything with a 0. Not sure why that was happening. So i modified the code to this:
iAcctName = ""
iLen = Len(iAcctName)
For i = 1 to iLen
schar = mid(iAcctName, i, 1)
if sChar = " " then
sString = sString + " "
Elseif sChar = "/" or sChar = "\" or sChar = "*" or etc.....(all 13 charaters not recognized by IDEA) then
sString = sString + "_"
End if
sString = sString +sChar
Next i
NewAcctName = sString
Hey Brian!
Hey Brian!
I was just looking at this thread, how about replacing all special characters not on the field name but on all the content of a field. Lets say I have a field called description with many special characters on different rows and want them to dissapear. Could you help me with that please?
Thanks a lot!
Hi Marc and welcome to the
Hi Marc and welcome to the site.
One option is to create a virtual field to change the name of the field and replace all the special characters with an underscore. I created a custom function that does just that, right now the custom function only allows for apha characters but it can be modified to allow for other characters.
As you are dealing with a script you could also adapt the function in the custom function so that you pass the name of the file through the function to replace all the special characters prior to saving the file.
Anyway, let me know if this is what you are working for. Depending on what you want to do you can add this to your script. So let me know how I can help out.