none
Custom Format - Single Quote as thousand seperator RRS feed

  • Question

  • Hi,

    I just want to show thousand separator value using single quote ('). 

    I tried with following custom format in Excel., 

    #'##0.00 ->  It works on 1'000.00 , 10'000.00 , 100'000.00   But not on this 1000'000.00

    Any ideas how to done this.,?

    Thanks

    Saturday, May 7, 2016 7:17 AM

Answers

  • The best you can do without using VBA is the custom format

    [>=1000000]#'###'##0;[>=1000]#'##0;0

    This will work correctly for numbers from 0 up to 999,999,999. It is not possible to add another section to the custom format.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Thirisangu.R Saturday, May 7, 2016 10:07 AM
    Saturday, May 7, 2016 9:38 AM

All replies

  • Normally, Excel doesn't recognize ' as thousands separator. You'd have to use

    #'##0 for numbers up to 999,999
    #'###'##0 for numbers from 1,000,000 to 999,999,999
    #'###'###'##0 for numbers from 1,000,000,000 to 999,999,999,999.

    Alternatively, you can set the thousands separator to ' in the Region control panel. This would apply to all applications.

    Or set it in Excel, in File > Options > Advanced. This would apply to all Excel workbooks.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, May 7, 2016 8:21 AM
  • Hi

    Thanks for your reply.,

    I tried with #'###'###'##0 and it works fine for value between 1,000,000,000 to 999,999,999,999.,

    But in the same if i give value as 100.. The result is leading with three single quotes like '''100

    I tried in another way in Custom Format like [>1000]#'###'###'##0 ; 0

    But this result is leading single quotes for the value ''1'000

    Is there any way to restrict this or condition to limit this value.,

    Yes., Advance settings can do this., But i develop this application which will run on client machine., 

    Saturday, May 7, 2016 9:26 AM
  • The best you can do without using VBA is the custom format

    [>=1000000]#'###'##0;[>=1000]#'##0;0

    This will work correctly for numbers from 0 up to 999,999,999. It is not possible to add another section to the custom format.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Thirisangu.R Saturday, May 7, 2016 10:07 AM
    Saturday, May 7, 2016 9:38 AM