none
How to format a legacy text form field with thousands separator RRS feed

  • Question

  • Hi,

    I know this isn't a real developer question, but I know thar yopu often get the answers here :-).

    In Sweden we use a space as thousands separator. I can't figure out how to set the numbering format in a legacy text form field (with the type set to Numbers) so that it groups the digits with a space as the thousands separator. Like this 1 865 250.

    Any help is highly appreciated.

    Peeter



    • Edited by pemok Tuesday, January 22, 2013 4:09 PM
    Tuesday, January 22, 2013 4:08 PM

All replies

  • # ### ### should work. Or # ### ###,## if you want decimals as well.

    Stefan Blom, Microsoft Word MVP

    Tuesday, January 22, 2013 7:19 PM
  • Thanks for your quick reply,

    but entering

    12345

    in a legacy text form field with the setting (se below)

    # ### ###

    produces this result

    1 234 5

    We have one space character as the thousands separator in the control panel.

    Screenshot

    Tuesday, January 22, 2013 7:43 PM
  • You are right. This isn't working properly. :-(

    Stefan Blom, Microsoft Word MVP

    Tuesday, January 22, 2013 7:55 PM
  • I think it has something to do with having a space character as the separator when the space character also is used as a delimiter in separating different parts of a format switch. I have tried difernet approaches, like encloding the space within quotes ("") but to no avail.

    Any ideas?

    P

    Tuesday, January 22, 2013 7:59 PM
  • Perhaps using and OnExit macro:

    Sub RunMeOnExit()
    ActiveDocument.Bookmarks("Text1").Range.FormFields(1).Result = Trim(Format(ActiveDocument.Bookmarks("Text1").Range.Text, "### ### ###.00"))
    End Sub

    Greg Maxey Please visit my website at: http://gregmaxey.mvps.org/word_tips.htm

    Tuesday, January 22, 2013 8:31 PM
  • Thanks,

    but unfortunately this document/template is going to be used by unknown users and we don't want to handle problemsand questions with macros and security.

    So I have to stick to a non macro solution. Any ideas :-)?

    P

    Tuesday, January 22, 2013 9:32 PM
  • Try using Shift+spacebar to use a non-breaking space as the separator.

    Greg Maxey Please visit my website at: http://gregmaxey.mvps.org/word_tips.htm

    Tuesday, January 22, 2013 10:35 PM
  • I'm afraid that didn't make any difference. Is it to be considered a bug then?

    Thank you all for your interest in this matter!

    Peeter

    Wednesday, January 23, 2013 8:22 PM
  • I'm afraid that didn't make any difference. Is it to be considered a bug then?

    Thank you all for your interest in this matter!

    Peeter




    For what it's worth, I have seen the same. Using nonbreaking spaces doesn't work either.

    Stefan Blom, Microsoft Word MVP

    Wednesday, January 23, 2013 8:28 PM
  • Well, it worked here (or seemed to).  However, the more I tried to refine it, it became more and more inconsistent :-(.

    Sorry.


    Greg Maxey Please visit my website at: http://gregmaxey.mvps.org/word_tips.htm

    Wednesday, January 23, 2013 10:00 PM
  • Well, it worked here (or seemed to).  However, the more I tried to refine it, it became more and more inconsistent :-(.

    Sorry.


    Greg Maxey Please visit my website at: http://gregmaxey.mvps.org/word_tips.htm




    Yes, it appears to be a bug. :-(

    Stefan Blom, Microsoft Word MVP

    Thursday, January 24, 2013 9:56 AM
  • Hello Peeter,

    How is the Digit grouping symbol setup in the Region and Language in the Control Panel (Control Panel | Region and Language | Additional settings button | Numbers)?

    I changed the format to Swedish (Sweden) in the Region and Language settings.  Now when I open Word and add a text form field and set the type as Number, I now have an option in the Number format dropdown for # ##0.  I typed in 12345 and hit tab, the number converts to 12 345.

    Thanks,

    Aaron

    Thursday, January 31, 2013 10:37 PM
    Moderator
  • Hello Peeter,

    How is the Digit grouping symbol setup in the Region and Language in the Control Panel (Control Panel | Region and Language | Additional settings button | Numbers)?

    I changed the format to Swedish (Sweden) in the Region and Language settings.  Now when I open Word and add a text form field and set the type as Number, I now have an option in the Number format dropdown for # ##0.  I typed in 12345 and hit tab, the number converts to 12 345.

    Thanks,

    Aaron




    I always make use of Swedish regional settings, because Swedish is my native language. :-)  I hadn't tried that particular format (# ##0), but it seems to work better. Also, ### ### ##0 seems to work properly.

    Thank you.


    Stefan Blom, Microsoft Word MVP


    Friday, February 1, 2013 9:03 AM
  • It seems as the functionality is unstable. I can't get it to work as you can. I thought I had it working for a short period of time (before I started this thread), but that is history now :-).

    BTW, a similar problem, do you know what could cause the formula

    in a table i Word to produce the correct result

    3 000

    in Word on a PC but produces

    3

    in Word on a Mac?

    (I am awaiting response regarding the regional settings on the Mac, but if you have something on this now, so please let me know)

    /Peeter

    Friday, February 1, 2013 12:37 PM
  • Frankly, my testing was limited, although it did seem promising.

    Sorry, I don't know anything about MacWord. Maybe Greg Maxey does?


    Stefan Blom, Microsoft Word MVP


    Friday, February 1, 2013 1:31 PM
  • It may be significant that the # ##0 format that you select from the dropdown actually has a nonbreaking space (160) as the 2nd character, as per Greg's suggestions a few messages back. 

    space as a thousands separator has always caused problems in Word formatting, and I suspect it's only partly because it is regarded as "special" in other ways. It may also be that both space and 160 are stripped by Word if they appear at beginning of a format string, whereas a "," might not be.

    The other thing I notice is that the format strings in form fields do not work in quite the same way as the format strings in "output-only" fields such as { REF } and { = }. On a typical US English system, for example, it's common to suggest "$,0.00" as a format for dollar values. You don't need "$0,000,000.00" because the "," is recognised as the thousands separator/grouping character and is applied appropriately. But "$,0.00" doesn't work for a formfield here. Word seems to want to see a "#" in the format as well. So, it may be that "S 0,00" (where S is the Swedish currency symbol) or "S<nonbreakingspace>0,00" work OK for an "output-only field" but not for a form field. But, e.g. "$#,0" works OK in a form field and maybe the Swedish equivalent does too.


    Peter Jamieson

    Friday, February 1, 2013 3:20 PM
  • The only way I could get this behaviour was as follows (summing the content of a single cell (i.e.
    { =sum(A1:A1) }):
     a. Put 3<nonbreakingspace>000 in cell A1 on a Windows/Word 2010 system with the default Swedish (Sweden) regional settings. THat is recognised as 3000
     b. Use UK (say) regional settings on the Mac. In this case I am using Mac Word 2011 SP3 on a Mountain Lion system. However, I don't think there is anything special about the Mac in that respect - I think you would see "3" on a Windows system with UK settings in that case.

    With both sets of regional settings,  3<ordinaryspace>000 is treated by Word as a list of numbers and summed. In other Words, with both UK and Swedish settings, 3<ordinaryspace>001 would be evaluated as "4". But on the system with UK regional settings, the <nonbreakingspace> is treated the same as an <ordinaryspace>, whereas on the Swedish one, it's treated as a separator (I think).

    I suspect that does not answer your question, but if you can provide more detail I may be able to replicate what you have. For general info. on what Word recognises in tables, I wrote up some research a while back - unfortunately, it's in the Mac group on the MS Answers site, which is down for an upgrade at the moment, but I'll try to remember to post the link.


    Peter Jamieson

    Friday, February 1, 2013 3:39 PM
  • For general info. on what Word recognises in tables

    ...you may find more info. in my post in the following conversation:
    http://answers.microsoft.com/en-us/mac/forum/macoffice2008-macword/autosum-says-unexpected-end-of-formula-when-i/57398214-3cc3-4696-8c43-d4de69c41077


    Peter Jamieson

    Friday, February 1, 2013 11:10 PM
  • Thanks Peter For the information, and thank you all again for your kind interest.

    The best I have come up with is

    * that all calculations can be formatted with the thousands separator (using {=sum(A1:C3)} kind of mechanism

    * all the input form fields have to be "unformatted"

    /Peeter

    Saturday, February 2, 2013 6:42 PM