Matching Number formats based on number format in another cell

You could use a formula; e.g., for three significant digits,

=--TEXT(A1, "0.00E+0")

Im trying to match significant figures when i dump the data from D10.

I don't know what that means.

You could do Options > Advanced > Set Precision as Displayed, but I'd be very cautious; it will irreversibly change the value of constants to their formatted appearance.

It seems like the first step in something like this is how to determine how many significant figure D10 has. How do you determine the number of significant figures in D10? Part of determining the number of significant figures is probably going to involve knowing exactly what is in D10. Is D10 the result of a formula? Are the number of digits displayed impacted by number formatting? Will the values in D10 always be "mixed" (integer plus fraction) values, or can they be integers or fraction only values? Is the end goal to change the actual value in column G, or to merely apply number formatting to column G, but retain values in column G to their full precision?

How will you know how many sig figs are in D10? Or, perhaps more importantly, what should Excel look at to determine how many sig figs are in D10 (hopefully, by knowing what you would look at to tell you how many sig figs, that will help us know what Excel can look at)?

So, it needs to look at the number of digits you enter. That sounds like a LEN() function. The main difficulty will be how to interpret trailing zeroes. How many significant figures in 230? LEN(230) is three, but that could only be 2 sig figs. What about 2.30? Is that three sig figs, or two? LEN(2.30) returns 3 (minus the decimal character will see 2 sig figs).

There could be some value in entering values as text rather than number. Where LEN(2.30) is 3, LEN("2.30") is 4, which could help with detecting trailing zeros that are significant to the right of the decimal point, but those numbers are then text strings, not real numbers, which can create other issues.

I'm not certain I understand the concern. Yes, you will need to apply the formula to all 500 lines, but once you get the formula figured out, you can enter it once and copy it down 500 times.

We could do this with conditional formatting, but it seems like we would still have the same issue -- what formula to use to "count" the sig figs in column D. To my knowledge, Excel just does not have a built in, preprogrammed function to count sig figs.

You might consider posting a workbook with a clear explanation of what you're trying to do.