Number Formatting: 1''00

Off the top of my head, I do not think this will be possible using pure number formatting. You can review the different number format codes and see if you can figure something out: https://support.office.com/en-au/art...2-09fab54be7f4

The easiest approach might be:

1) Go into File -> Excel options -> Advanced. Find the checkbox for "use system separators" and uncheck it. Then enter " as your decimal separator.
2) With " as your decimal separator, you can use a number format code like "0.00,,," to get 1E9 to display as 1"00.

Downsides might be that, as a global setting, this will effect all of your spreadsheets. Every cell that uses a decimal point will display inverted commas for the decimal point. Also, if your share this spreadsheet with others, they will need to make the same change in their Excel options in order to get the same display. With those downsides, I do not know if this is a good solution, but it seems like a possible solution.

Any other approach that I can think of will involve =CONCATENATE() to piece together the value as a text string.

Without changing the excel options, you can custom format the cell like this. I believe this will work for you.

Please Login or Register  to view this content.

edit: After playing around some, it only works if you are using three digits after the " in the display. If you try and change it to only two digits after ", it throws it off. Currently looking into this.

edit #2: After exploring around a bit, you would either have to leave the above as is and deal with 3 digits after the " or use the below option.


If you always use " as your placeholder for numbers, then you can always change your options as shown here to display a different character as your decimal point or thousands separator then custom format your cell this way.

Please Login or Register  to view this content.

hello
You can use normal cell format - custom format = #,##0;-#,##0 and then
conditional formatting for cell values equal to or greater than 1000000 and change the format to custom format to #\"###### or what ever you need

on testing should be #\"###,