Function to Remove Special Characters

[bcarey1]

I am trying to build a spreadsheet that will convert MAC addresses into different formats. I have figured out how to take the 12 character address and format into the groupings I need with a specified delimiter. What I am stuck on is, trying to convert a MAC address with special characters, primarily : . -, into just numbers and letter. Find and Replace works, but is a clunky solution. Is there a function to remove just special characters?

 

[Rick Rothstein]

Assuming your MAC address is in cell A1, does this formula do what you want..

=TEXTSPLIT(A1,{":","-","."})

 

[Sanjeev1976]

I think like this :

=TEXTJOIN(,,TEXTSPLIT(A1,{":","-","."}))

 

[bcarey1]

I think like this :

=TEXTJOIN(,,TEXTSPLIT(A1,{":","-","."}))

That worked perfectly. With just TEXTSPLIT there were spaces left over, but with the TEXTJOIN added I got just what I was looking for. Thanks!