I have a column that is basically a description field. It may or may not contain both invalid characters, and hidden, or non-printable characters.
I want to use regular expression to remove the invalid characters and replace them with " " (a space).
so for example a-z, A-Z 0-9 are acceptable all other characters, and hidden characters need to be replaced with a space.
I have already enabled Microsoft VBScript Regular Expressions 5.5 in VBA project.
How can I use regular expression to solve this?
Here is an example of what my data looks like. This happens to be in Column B
[TABLE="width: 603"]
<tbody>[TR]
[TD]3Com, 3C1206-0 - SuperStack II Transceiver Module - Female AUI[/TD]
[/TR]
[TR]
[TD]3Com, 3C1206-3 - SuperStack II Transceiver Module - 10Base-T (UTP)[/TD]
[/TR]
[TR]
[TD]3Com, 3C1206-4 - SuperStack II Transceiver Module - Fan Out (Male AUI)[/TD]
[/TR]
[TR]
[TD]3Com, 3C1206-5 - SuperStack II Transceiver Module - Fibre Optic (ST)[/TD]
[/TR]
[TR]
[TD]3Com, 3C1206-7 - SuperStack II Transceiver Module - 10Base-FB[/TD]
[/TR]
[TR]
[TD]3Com, 3C16074 - SuperStack II Advanced RPS, Type 2 100W Power Module[/TD]
[/TR]
[TR]
[TD]3Com, 3C16411 - SuperStack III Baseline Dual Speed Hub, 24-Port[/TD]
[/TR]
[TR]
[TD]3Com, 3C16593A - SuperStack II Baseline Dual Speed Hub, 24 Port RJ45[/TD]
[/TR]
[TR]
[TD]3Com, 3C16610 - SuperStack II Dual Speed Hub 500 12x RJ45[/TD]
[/TR]
[TR]
[TD]3Com, 3C16683 - SuperStack II 100Base-TX Distance Extender Module[/TD]
[/TR]
[TR]
[TD]3Com, 3C16684 - SuperStack II 100Base-FX Distance Extender Module[/TD]
[/TR]
[TR]
[TD]3Com, 3C16685 - SuperStack II Dual Speed Hub 500 Manag.-M. for3C16610, 3C16611[/TD]
[/TR]
[TR]
[TD]3Com, 3C16700A - OfficeConnect Ethernet Hub 8/TPO, 8x 10BASE-T[/TD]
[/TR]
[TR]
[TD]3Com, 3C16701A - OfficeConnect Ethernet Hub 8C, 8x 10BASE-T[/TD]
[/TR]
[TR]
[TD]3Com, 3C16942A - SuperStack II Switch 3000 Ethernet, 12 x RJ45[/TD]
[/TR]
[TR]
[TD]3Com, 3C17111 - SuperStack III Switch 4300 Series, 100Base-FX Module[/TD]
[/TR]
[TR]
[TD]3Com, 3C17121 - SuperStack III Switch 4300 Series, 10/100/1000Base-T Module[/TD]
[/TR]
[TR]
[TD]3Com, 3C17131 - SuperStack III Switch 4300 Series, 1000Base-SX Module[/TD]
[/TR]
[TR]
[TD]3Com, 3C17203 - SuperStack III Switch 4400, 24 port 10BASE-T/100BASE-TX[/TD]
[/TR]
</tbody>[/TABLE]
Thank you,
Rocco
I want to use regular expression to remove the invalid characters and replace them with " " (a space).
so for example a-z, A-Z 0-9 are acceptable all other characters, and hidden characters need to be replaced with a space.
I have already enabled Microsoft VBScript Regular Expressions 5.5 in VBA project.
How can I use regular expression to solve this?
Here is an example of what my data looks like. This happens to be in Column B
[TABLE="width: 603"]
<tbody>[TR]
[TD]3Com, 3C1206-0 - SuperStack II Transceiver Module - Female AUI[/TD]
[/TR]
[TR]
[TD]3Com, 3C1206-3 - SuperStack II Transceiver Module - 10Base-T (UTP)[/TD]
[/TR]
[TR]
[TD]3Com, 3C1206-4 - SuperStack II Transceiver Module - Fan Out (Male AUI)[/TD]
[/TR]
[TR]
[TD]3Com, 3C1206-5 - SuperStack II Transceiver Module - Fibre Optic (ST)[/TD]
[/TR]
[TR]
[TD]3Com, 3C1206-7 - SuperStack II Transceiver Module - 10Base-FB[/TD]
[/TR]
[TR]
[TD]3Com, 3C16074 - SuperStack II Advanced RPS, Type 2 100W Power Module[/TD]
[/TR]
[TR]
[TD]3Com, 3C16411 - SuperStack III Baseline Dual Speed Hub, 24-Port[/TD]
[/TR]
[TR]
[TD]3Com, 3C16593A - SuperStack II Baseline Dual Speed Hub, 24 Port RJ45[/TD]
[/TR]
[TR]
[TD]3Com, 3C16610 - SuperStack II Dual Speed Hub 500 12x RJ45[/TD]
[/TR]
[TR]
[TD]3Com, 3C16683 - SuperStack II 100Base-TX Distance Extender Module[/TD]
[/TR]
[TR]
[TD]3Com, 3C16684 - SuperStack II 100Base-FX Distance Extender Module[/TD]
[/TR]
[TR]
[TD]3Com, 3C16685 - SuperStack II Dual Speed Hub 500 Manag.-M. for3C16610, 3C16611[/TD]
[/TR]
[TR]
[TD]3Com, 3C16700A - OfficeConnect Ethernet Hub 8/TPO, 8x 10BASE-T[/TD]
[/TR]
[TR]
[TD]3Com, 3C16701A - OfficeConnect Ethernet Hub 8C, 8x 10BASE-T[/TD]
[/TR]
[TR]
[TD]3Com, 3C16942A - SuperStack II Switch 3000 Ethernet, 12 x RJ45[/TD]
[/TR]
[TR]
[TD]3Com, 3C17111 - SuperStack III Switch 4300 Series, 100Base-FX Module[/TD]
[/TR]
[TR]
[TD]3Com, 3C17121 - SuperStack III Switch 4300 Series, 10/100/1000Base-T Module[/TD]
[/TR]
[TR]
[TD]3Com, 3C17131 - SuperStack III Switch 4300 Series, 1000Base-SX Module[/TD]
[/TR]
[TR]
[TD]3Com, 3C17203 - SuperStack III Switch 4400, 24 port 10BASE-T/100BASE-TX[/TD]
[/TR]
</tbody>[/TABLE]
Thank you,
Rocco