Determine maximum number of decimals displayed in column, and apply that formatting to the entire column.
When I query values from a database into Excel, the numbers come in exactly like they are entered or with the trailing zeroes truncated. For example, 2.00 may come in as 2, or 1.90 may come in as 1.9 with 1.98 coming in as 1.98. But there is certain data that may come in this same column that the numbers are all whole numbers.
Is there code that can be applied that will detect the formatting of the contents of each cell and determine the most decimal places used by any one cell in the column, and then format the entire column to display the same number of decimals.
Attached is a file with two examples of how data may come in, and how I would like it displayed.
Formatting-Example.xlsx
Is there code that can be applied that will detect the formatting of the contents of each cell and determine the most decimal places used by any one cell in the column, and then format the entire column to display the same number of decimals.
Attached is a file with two examples of how data may come in, and how I would like it displayed.
Formatting-Example.xlsx
Owen Rubin
The only way I can think to do this is to convert the number into a string of characters, and them count the characters. You can use Excel’s TEXT function to do that. Then you can use Excel’s LEN function to get the length of the string. There is a FIND character function as well, so check for “.” and subtract one if found. Not sure if it adds a decimal point and counts it, but you can experiment and subtract one if so. I’m mobile right now so can’t try it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Gustav,
This is perfect. So cool. Excellent work.
Thanks for your help.
Steve
This is perfect. So cool. Excellent work.
Thanks for your help.
Steve
You are welcome!
Impressive solution