SharePoint 2010: Managed Metadata Converts Ampersand and Double Quotes to Unicode

We have been making a lot of use of Managed Metadata lately. One use is to associate people with a team, so we have created a user profile property associated with a Teams term set. We also created a CSV export for people search so it will export this field among many others for all people returned in the search, with the intention that this will be opened in Excel.

This was working fine until we tested a CSV export in Excel where the Team field contained an ampersand (&). It unexpectedly appeared in Excel as a collection of symbols. I had noticed previously that the ampersand looked a little unusual in SharePoint but thought nothing more of it until now.

It transpires that the ampersand (&) is automatically converted to a Unicode/wide character version (＆) when it is added to the Term Store. This is represented using hex code FF06 or integer 65286. The only documentation I have found confirming this so far is in the TaxonomyItem.NormalizeName method which “Normalizes a name of a TaxonomyItem object”, or in other words, converts the given string to the format used in the Term Store.

The remarks section says:

The name will be normalized to trim consecutive spaces into one and replace the & character with the wide character version of the character (\uFF06).

However upon reflecting the method I found the following code:

return CommonValidator.trimSpacesRegex.Replace(name, " ").Replace('&', 0xff06).Replace('"', 0xff02);

This shows that not only does it modify the spaces and ampersand but it also replaces double-quotes (“) with a Unicode version too (＂), using hex code FF02 or integer 65282. This was unexpected and to my knowledge undocumented.

Now that we are armed with this information, to fix the CSV export issues now simply involves replacing these Unicode characters back to the original ASCII characters that we used.

You might also use the TaxonomyItem.NormalizeName method if you want to compare a string with a term name taken from the term store, by using it to ‘normalize’ the string prior to comparing with the term.