Dealing with HTML Non-Breaking Spaces in Excel

We are having issues is Excel. We are receiving a report in Excel that has been exported from some 3rd party datasource. We have no input into how this file is created. What we have deduced is that some how it has HTML non-breaking spaces in critical places:

Example part number 'ABC 1234' and because the space is not encoded as a normal space that you get by hitting the space bar VLOOKUPS in Excel and SQL queries are failing to find the data. Even CTRL+F does not find it.

I could hack a script to correct this, but I was hoping you guys might have come accross this in the past and found something you could suggest.

Find more posts tagged with

Free for TechExams community: Cybersecurity salary guide

Compare cert salaries and plan your next career move

Button

Comments

RobertKaucher

Here is what it looks like in Notepad++ with the encoding changed.

nonbrspace.jpg

TheShadow

There are several high end user interface grid products that can create Excel office type displays. It could be one with a slight formatting bug. A couple of products that come to mind are ...

Infragistics NetAdvantage Ultimate
ComponentOne Studio for ASP.NET AJAX
Telerik RadControls for ASP.NET AJAX (it has a sharepoint wrapper)
FarPoint Spread for ASP.NET

These are the most popular ones but generally they have a graphical or non-graphical file component like Aspose.Cells with verbiage similar to.

Aspose.Cells is a non-graphical Excel® spreadsheet reporting component that enables either .NET or Java applications to read and write Excel® spreadsheets without utilizing Microsoft Excel®. Aspose.Cells is a very feature rich component that offers much more than just basic exporting of data. With Aspose.Cells developers can export data, format spreadsheets in every detail and at every level, import images, import charts, create charts, stream Excel® data, save in various formats and much, much more.

Any one of similar products can be your problem if pulling from a web or win forms grid. Unfortunately Robert you are going to need to do more digging. Hope I am wrong but I don't think that you have an easy task. Sorry that I could not help more.

RobertKaucher

Well, anything I speculate on would be worthless... So I suppose we can develop some things in C# and PoSh to clean these out. I'm just lazy.