KB442375: ‘Replace Text’ option in thresholds will cause exported cells in Microsoft Excel appear formatted as text even when the used value is a number in MicroStrategy Developer and Web

SYMPTOM:
When using the 'Replace Text' option in thresholds when exporting to Microsoft Excel, cells that were replaced will be formatted as Text even when the replaced text is a number in Strategy Developer and Web 10.x as shown in the images below.
 

Number Stored as Text

1. Create a report with Category and a Cost.
2. Add a threshold to the metric.

1. Double click on 1234.567 to open the Format editor. Select "Replace text" and replace the text with any number.

1. Watch the threshold get applied in the report.

1. Export the report to Microsoft Excel.
2. Click on the affected cell.
3. Notice a popup icon next to it saying that the number is stored as text.

 
 
CAUSE
The behavior is working as designed; the Replace Text threshold feature was designed to change numbers into words like 'high', 'medium', or 'low'. If users click 'Ctrl+1', they will see what formatting setting the cell really has, and it is the exact same one that the rest of the metrics have, which means that if the cell were to be used in a formula, the replaced text number would still work.
 
ACTION
Follow the steps below to get rid of the pop-up:

1. In an empty cell, enter the number 1.
2. Select the cell created in step 1 and on the Edit menu, click Copy.
3. Select the range of cells stored as text to convert back to number.
4. Right click the cells and on the Edit menu, click Paste Special.

1. Under Operation, click Multiply (if you want to keep the format of the original metric, select "Values" under the Paste option, as shown below):

1. Click OK.
2. Delete the content of the cell entered in the first step.