Removing Non Printable Hex Characters

[Leane Messervey]

Is there a formula that can be used to remove printable hexadecimal characters from a cell? It appears that CLEAN only removes non printable characters. I am trying to load data into a business system from an excel template and it is erroring on Hex characters so I was hoping there was an easy way to remove them in excel so we don't have to touch the code in the ERP system.

Thanks in advance for an assistance on this one!

 

[gravanoc]

Hi Leane,

So you are trying to remove hexadecimal characters from your cell because they won't print?
Could you load a screenshot or specify which are the offending characters? The way I'm envisioning it now, is as 00 - FF style, which could be converted to binary or decimal with the HEX2BIN or HEX2DEC formulas.

Another method of removing unwanted characters is to select them, press Ctrl + H, and paste the item into the first cell, then press Alt + A to remove all from the document.

I may be misunderstanding you though.

 

[Leane Messervey]

Hi gravanoc,

I have an excel file that has a printable hex character in it that i want to strip out of the column. We are using a excel template to load a vendor price list into Dynamics NAV and the load is failing because some of the description fields have characters like a degree symbol in them. I don't really want them doing any coding to Dynamics to resolve this so i was hoping there was a formula in excel that we could execute to clean the data before we load it. CLEAN works but that is only for non printable characters it seems and something like a degree symbol is printable. I just want to get rid of any hex character en masse if possible.

Hope that makes sense!

Thanks,

Leanne