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!
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).
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.
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.
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.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With