How do I insert thousands-separators without affecting significant figures in Excel?

Question

I need to figure out how to add thousands separators to large spreadsheets without affecting the amount of significant figures or the dates that are also in the tables. I can't figure out how to use conditional formatting to do an if/then kind of thing where it would recognize a cell that has a value ending in .?? or the like. I tried using regex in VBA but that created other issues - it's tough to get it to recognize matches because I need to format cells ending in .?? (sometimes up to .??????? and beyond) and the only way I could think of to edit that text would be to parse it out into substrings and then add commas for every three substrings - that seems really overcomplicated. Has anyone ever done something like this before/does anyone have any advice? I've spent a couple days trying to work through this in my spare time and I feel stuck and frustrated.

Answer 1

Create a range of cells you want to clean up, then try something like this.

if instr(rng,".??") > 0 then rng.NumberFormat = "#,##0.00:-#,##0.00"

If you want to provide a little better information about your data structure, my answer would be more specific to your problem.