Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Removing special characters VBA Excel

I'm using VBA to read some TITLES and then copy that information to a powerpoint presentation.

My Problem is, that the TITLES have special characters, but Image files that I am also coping over do not.

The TITLE forms part of a path to load a JPEG into a picture container. E.g. "P k.jpg", but the title is called "p.k".

I want to be able to ignore the special characters in the TITLE and just get it to see a space instead so it picks up the right JPG file.

Is that possible?

Thank you!

like image 583
pixie Avatar asked Jun 23 '14 00:06

pixie


People also ask

How do I get rid of special characters in Excel?

Excel does not provide any functions to remove all special characters from strings at once. If you want to remove only one special character, you can use the SUBSTITUTE function (see more in this article Remove Unwanted Characters).

How do I remove punctuation from a string in VBA?

Select a blank cell you will output result, enter the formula =RemovePunctuation(A2) (A2 is the cell you will remove all punctuation marks from) into it, and then drag the Fill Handle to the range as you need.

How do you get rid of special characteristics?

Using 'str. replace() , we can replace a specific character. If we want to remove that specific character, replace that character with an empty string. The str. replace() method will replace all occurrences of the specific character mentioned.

How do I trim text in VBA?

Step 1: Create a Macro name and declare a variable as String. Step 2: Now, assign a value using the TRIM for this declared variable. Step 3: For this String type, the word in double quotes is “Welcome to VBA.” Step 4: Now, show the result of this variable in the message box.


2 Answers

What do you consider "special" characters, just simple punctuation? You should be able to use the Replace function: Replace("p.k","."," ").

Sub Test()
Dim myString as String
Dim newString as String

myString = "p.k"

newString = replace(myString, ".", " ")

MsgBox newString

End Sub

If you have several characters, you can do this in a custom function or a simple chained series of Replace functions, etc.

  Sub Test()
Dim myString as String
Dim newString as String

myString = "!p.k"

newString = Replace(Replace(myString, ".", " "), "!", " ")

'## OR, if it is easier for you to interpret, you can do two sequential statements:
'newString = replace(myString, ".", " ")
'newString = replace(newString, "!", " ")

MsgBox newString

End Sub

If you have a lot of potential special characters (non-English accented ascii for example?) you can do a custom function or iteration over an array.

Const SpecialCharacters As String = "!,@,#,$,%,^,&,*,(,),{,[,],},?"  'modify as needed
Sub test()
Dim myString as String
Dim newString as String
Dim char as Variant
myString = "!p#*@)k{kdfhouef3829J"
newString = myString
For each char in Split(SpecialCharacters, ",")
    newString = Replace(newString, char, " ")
Next
End Sub
like image 178
David Zemens Avatar answered Sep 30 '22 18:09

David Zemens


In the case that you not only want to exclude a list of special characters, but to exclude all characters that are not letters or numbers, I would suggest that you use a char type comparison approach.

For each character in the String, I would check if the unicode character is between "A" and "Z", between "a" and "z" or between "0" and "9". This is the vba code:

Function cleanString(text As String) As String
    Dim output As String
    Dim c 'since char type does not exist in vba, we have to use variant type.
    For i = 1 To Len(text)
        c = Mid(text, i, 1) 'Select the character at the i position
        If (c >= "a" And c <= "z") Or (c >= "0" And c <= "9") Or (c >= "A" And c <= "Z") Then
            output = output & c 'add the character to your output.
        Else
            output = output & " " 'add the replacement character (space) to your output
        End If
    Next
    cleanString = output
End Function

The Wikipedia list of Unicode characers is a good quick-start if you want to customize this function a little more.

This solution has the advantage to be functionnal even if the user finds a way to introduce new special characters. It also faster than comparing two lists together.

like image 22
V. Brunelle Avatar answered Sep 30 '22 16:09

V. Brunelle