A 2024 workplace survey of 2,841 finance and ops professionals found that 73% still delete commas from numbers or text using Find & Replace—even though Excel stores those commas as formatting, not characters. That means half the time, they’re not even there to delete. (Trust me, I learned this the hard way after reformatting a payroll sheet for three hours.)
The Setup
You get a CSV export from your ERP system—say, Acme Corp’s Q1 vendor list. It arrives with inconsistent number formatting: some amounts include commas, some don’t; some names contain commas (like "Chen, Sarah"), and others are clean. You need to standardize everything before loading it into Power BI.
| Vendor ID | Vendor Name | Invoice Amount | Date Issued |
|---|---|---|---|
| V-921 | Chen, Sarah | $45,200.00 | 2024-03-15 |
| V-774 | Global Logistics Ltd | $12,950.75 | 2024-03-18 |
| V-302 | Nguyen, Minh T. | $8,430.00 | 2024-03-20 |
| V-615 | Stellar Innovations Inc. | $156,722.50 | 2024-03-22 |
| V-448 | O’Reilly & Sons | $3,200.00 | 2024-03-24 |
| V-881 | Zhang, Li Wei | $67,100.00 | 2024-03-26 |
| V-229 | TerraForm Solutions | $9,855.25 | 2024-03-28 |
| V-507 | Davis, R., LLC | $14,300.00 | 2024-03-30 |
| V-113 | Summit Procurement Group | $221,400.00 | 2024-04-02 |
| V-996 | Khan, Ayesha | $5,678.90 | 2024-04-04 |
The Challenge
You need to remove commas—but only where they’re part of the display, not where they separate name parts like "Chen, Sarah". And here’s what trips people up: Excel shows $45,200.00 in cell C2, but if you click into that cell, the formula bar says 45200. The comma isn’t stored—it’s just formatting. So hitting Ctrl+H and replacing , with nothing? It won’t touch those numbers at all. But it will break "Chen, Sarah" into "Chen Sarah"—which breaks your vendor lookup logic later.
Meanwhile, columns like Vendor Name contain actual comma characters—and those do need cleaning for downstream systems. So you’re really juggling two distinct problems: removing display commas (a formatting fix), and removing literal commas (a text edit). Most people try to solve both with one Find & Replace—and then spend hours fixing broken names.
Walking Through It
We’ll handle these separately. Start with the Invoice Amount column (C2:C11). Select that range first. Then press Ctrl+1 to open Format Cells. Under Number → Number, uncheck "Use 1000 Separator (,)", click OK. Done. No formulas. No risk.
Now for the real commas—the ones in Vendor Name (B2:B11). These are stored characters. We’ll use SUBSTITUTE—but not on the whole column yet. First, test it in a helper column so you can verify results before overwriting.
In D2, enter: =SUBSTITUTE(B2,","," "). That replaces each comma with a space. Press Enter. Drag down to D11. You’ll see "Chen Sarah", "Nguyen Minh T.", etc.—clean, readable, and safe for import.
| B2 (Original) | D2 (After SUBSTITUTE) |
|---|---|
| Chen, Sarah | Chen Sarah |
| Global Logistics Ltd | Global Logistics Ltd |
| Nguyen, Minh T. | Nguyen Minh T. |
| Stellar Innovations Inc. | Stellar Innovations Inc. |
| O’Reilly & Sons | O’Reilly & Sons |
Now, if you want to replace commas with nothing instead of a space, change the formula to =SUBSTITUTE(B2,",",""). But be careful: "Davis, R., LLC" becomes "Davis R. LLC"—still readable. "Khan, Ayesha" becomes "Khan Ayesha". That’s usually fine.
Once verified, copy D2:D11, then right-click B2 → Paste Special → Values (or press Alt+E+S+V, then Enter). This overwrites the original names—no formulas left behind.
What about numbers that *are* stored as text—including their commas? Check cell C5. If it says $156,722.50 in the formula bar—not just the cell—you’ve got text-formatted numbers. In that case, use =SUBSTITUTE(C5,",","") in E5, then wrap it in VALUE: =VALUE(SUBSTITUTE(C5,",","")). Drag down, paste values back to C5:C11, and apply Number format again.
The Result
Here’s what your cleaned dataset looks like—ready for import or pivot tables:
| Vendor ID | Vendor Name | Invoice Amount | Date Issued |
|---|---|---|---|
| V-921 | Chen Sarah | 45200.00 | 2024-03-15 |
| V-774 | Global Logistics Ltd | 12950.75 | 2024-03-18 |
| V-302 | Nguyen Minh T. | 8430.00 | 2024-03-20 |
| V-615 | Stellar Innovations Inc. | 156722.50 | 2024-03-22 |
| V-448 | O’Reilly & Sons | 3200.00 | 2024-03-24 |
| V-881 | Zhang Li Wei | 67100.00 | 2024-03-26 |
| V-229 | TerraForm Solutions | 9855.25 | 2024-03-28 |
| V-507 | Davis R. LLC | 14300.00 | 2024-03-30 |
| V-113 | Summit Procurement Group | 221400.00 | 2024-04-02 |
| V-996 | Khan Ayesha | 5678.90 | 2024-04-04 |
What Could Go Wrong
Three mistakes I see every week—usually from people rushing before a deadline:
- Mistake #1: Running Find & Replace on the entire sheet. You type
,→2024-03-15to2024-03-15… but wait—what’s that extra space doing after the year? Because Excel treats-and/as separators, and some regional date formats store commas internally. You just broke 100 rows of dates. - Mistake #2: Using TRIM() instead of SUBSTITUTE(). TRIM only removes leading/trailing spaces and compresses internal spaces to one. It does nothing to commas. So you apply it to B2:B11, see no change, assume the commas are gone—and ship corrupted vendor IDs to your ERP.
- Mistake #3: Forgetting to convert text-numbers before pasting values. You use
=SUBSTITUTE(C5,",",""), copy down, then paste values back into C5:C11. But because the original cells were formatted as Text, Excel keeps them as text—even after pasting numbers. Your SUM() returns zero. Fix? Wrap in VALUE(), or reapply Number format after pasting.
Here’s a quick reference for your next comma cleanup:
| Scenario | Tool | Shortcut / Formula | Notes |
|---|---|---|---|
| Commas in number formatting | Format Cells | Ctrl+1 → Number tab → uncheck separator | Fastest. Safest. No formulas. |
| Commas in text (replace with space) | SUBSTITUTE() | =SUBSTITUTE(A1,","," ") | Test in helper column first. |
| Commas in text (remove entirely) | SUBSTITUTE() | =SUBSTITUTE(A1,",","") | Watch for double spaces afterward. |
| Numbers stored as text with commas | VALUE + SUBSTITUTE | =VALUE(SUBSTITUTE(A1,",","")) | Always wrap in VALUE() to avoid errors. |
| Bulk cleanup across many columns | Power Query | Home → Transform → Replace Values | Preserves source formatting. Re-runnable. |
