Remove Special Characters using VBA

[vad77]

In short, on a form I have a field for MAC addresses. I want to remove the : from the MAC address when pasted into the field. Currently I have an excel spread sheet that use to remove the : using a formula. Then I copy and paste it into the field. I want to eliminate this step and have VBA remove the : if it is present in the MAC address pasted in.


I tried several different code I found but nothing works.

Thanks in advance.

[madpiet]

REPLACE()?
SELECT Replace("My name is Willy Wonka", "i", "a") AS ReplaceString;




you don't need a custom replacer for this, so no need for VBA at all.

[Welshgasman]

I tried several different code I found but nothing works.

Always show what you have used, we are not mind readers.
As mentioned just use Replace() on the AfterUpdate of the control.

[vad77]

Always show what you have used, we are not mind readers.
As mentioned just use Replace() on the AfterUpdate of the control.

Didn't think of including code, this is the last code I tried.

Private Sub MACAddress_AfterUpdate()
Dim text As String
text = ":"
text = Replace(text, ":", "")




End Sub




Thank you.

[Bulzie]

What does the text = Replace(text, ":", "") give you, a space where that: was? You want it to remove that space completely? Does Replace(text, ":", null) work?

I know you can step thru each character and write them out again skipping the : when it is found but easier to use the Replace.

[Micron]

If that doesn't raise an error, then basically you're replacing : with :
Try
Replace([MacAddress],":","")

[Welshgasman]

As @Micron mentions, you need to use the control as the item to be searched. You could use text as the string to find, if that is not a reserved word, but I would just use ":" ad be done with it.

[vad77]

Does nothing. I am coding this in the "After Update" event in the macaddress field.

[vad77]

If that doesn't raise an error, then basically you're replacing : with :
Try
Replace([MacAddress],":","")

Thank you. This worked.

Me.MACAddress = Replace([MACAddress], ":", "")

[Micron]

What I think the original expression does in English is
in the string ':' replace ':' with a zls (aka ""). So you convert the variable to an empty string but do nothing to the textbox value.