Argus to Excel: Fixing “Data Format” errors when importing valuation reports.

Exporting valuation data, rent rolls, and cash flow forecasts from Argus Enterprise into Microsoft Excel often produces severe data formatting errors. Numbers paste as non-numeric text strings, empty line items import with invisible unicode spaces, and header rows break downstream lookups with merged layouts. Because the underlying data is stored as text, standard formulas like SUM, VLOOKUP, or INDEX/MATCH return #VALUE!, #N/A, or evaluate to zero.

Quick Answer

Fix numbers that import from Argus as uncalculable text by running a batch coercion formula:
=IFERROR(--TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), ""))), A2)
The SUBSTITUTE removes non-breaking spaces, CLEAN strips system characters, and double-unary (--) coerces numeric strings back into true numbers that Excel can calculate.

---

Why Argus Exports Break Excel Worksheets

Argus Enterprise generates reports designed for human review rather than spreadsheet automation. When exporting standard cash flow schedules or rent rolls to .xlsx or .csv, several systematic formatting issues occur:

[Argus Report Export]
        │
        ├── Non-Breaking Spaces (CHAR 160) ──► SUM() returns $0.00
        ├── Merged Account Header Rows     ──► VLOOKUP() returns #N/A
        ├── Accounting Negatives "(1,000)" ──► Math produces #VALUE!
        └── Mixed Date Strings ("Jan-26")  ──► Date functions break

1. Non-Breaking Spaces (ASCII 160): Argus formats indentation levels in charts of accounts using unicode character 160 ( ). Standard Excel TRIM() does not remove this character. As a result, Excel treats numbers with these spaces as text, causing =SUM(A1:A10) to return $0.00.
2. Text-Formatted Accounting Parentheses: Negative numbers exported with parentheses, such as (25,000), often import as raw text strings rather than numeric values containing negative signs (25000).
3. Stacked Headers and Spacer Rows: Argus exports multi-tiered reporting headers spanning several merged rows. This breaks automated ingestion systems like Power Query or standard matrix formulas.

---

Step-by-Step Data Remediation Workflow

Follow these steps to normalize an unformatted Argus export into an analysis-ready dataset.

Step 1: Strip Invisible Characters and Coerce Values

If an exported column contains values that look like numbers but refuse to sum, place this formula in an adjacent cleanup column:

=IFERROR(--TRIM(CLEAN(SUBSTITUTE(C2, CHAR(160), ""))), TRIM(C2))

• SUBSTITUTE(C2, CHAR(160), ""): Replaces hidden non-breaking spaces with standard empty strings.
• CLEAN(...): Strips out low-level system code characters (ASCII 0 to 31).
• (Double Unary): Forces Excel to convert the resulting clean text string into an actual numeric value.
• IFERROR(..., TRIM(C2)): If the target cell is a genuine text label (such as “Base Rental Revenue”), the double-unary conversion fails safely and returns the clean text string.

Step 2: The “Paste Special: Multiply” Fast Fix

If you need to fix entire sheets without creating secondary helper columns:

1. Click into an unused cell, type 1, and press Ctrl + C to copy it.
2. Highlight the imported Argus numeric columns that are failing to calculate.
3. Press Ctrl + Alt + V to open the Paste Special dialog.
4. Select Multiply under the Operation category and click OK.

Multiplying text-formatted numbers by 1 forces Excel’s calculation engine to re-evaluate their underlying data type, instantly converting text strings into calculable numbers.

       UNFORMATTED ARGUS IMPORT                     CLEANED TABULAR FORMAT
   |         A         |      B     |           |         A         |      B     |
---|-------------------|------------|       ---|-------------------|------------|
 1 |  Total Revenues   |            |        1 | Account Name      | Amount     |
 2 |    Base Rent      |  $ 45,000  |  ───►  2 | Base Rent         | 45000.00   |
 3 |    Expense Reimb  |  $  8,200  |        3 | Expense Reimb     | 8200.00    |
 4 |  Total Expenses   | (12,500)   |        4 | Total Expenses    | -12500.00  |

---

Step 3: Automating Clean Imports via Power Query

If you import recurring monthly Argus valuation runs, avoid manual formula corrections by processing the files through Excel’s native Power Query engine.

1. Go to Data > Get Data > From File > From Excel Workbook and select your Argus export.
2. In the Power Query editor, select the balance and rate columns.
3. Click Transform > Replace Values. In Value to Find, hold down Alt and type 0160 on your number pad, leaving Replace With blank.
4. Right-click the column header and choose Change Type > Using Locale….
5. Set the data type to Currency or Decimal Number, and set the Locale to English (United States). This correctly interprets wrapped accounting parentheses like (5,000) as negative numbers (5000).
6. Remove structural spacer rows by applying a Filter to drop null or empty category lines.
7. Click Close & Load to output a normalized table.

---

Diagnostic Next Steps

• If tenant recovery lines show zero revenue after import: Argus reports often place tenant expense reimbursements into separate sub-tables that export below the main rent schedule. If formulas miss these items, see CAM Reconciliation: Handling “Pro-rata” share calculation errors in mixed-use properties. to verify that expense pool recovery logic remains intact.
• If imported lease dates fail in forecast models: Argus often exports lease commencement dates in short year-month string formats (e.g., "2026-M09"). These will break Excel date logic. Use =DATE(LEFT(A2,4), MID(A2,7,2), 1) to reconstruct valid serial date headers before building dynamic lease timelines.