+ Reply to Thread
Results 1 to 10 of 10

personalized numbers format

  1. #1
    Registered User
    Join Date
    05-21-2015
    Location
    Germany
    MS-Off Ver
    2010
    Posts
    17

    personalized numbers format

    Hello everyone,

    when I type in my numbers (an amount of money) I usually see it as e.g. € 1.100.100,10 for "one million one hundred thousand one hundred Euros and 10 Cents". The cell is formatted as number with two digits, as this is how I usually need it. However, for my Chinese customer I need to switch the punctuation. He wants it like . € 1,100,100.10 How can excel do this automatically? I don't want to change it by hand every time.

    I really appreciate all your help!
    Thank you!

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: personalized numbers format

    What file format (print, pdf, or the actual spreadsheet) is your client receiving the information? For decimal and thousands separators, Excel by default uses whatever is in your system.

    If your client is receiving this in Excel format, he should be able to control this by making sure his Windows settings are configured to use comma as the thousands separator and period as the decimal separator.

    If you are creating a pdf or text file or hard copy, then you can go into your Excel Options -> Advanced, and uncheck the "use system separators" option and select what you want to use for those before printing or creating the file to send to your client.

    Does that help?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: personalized numbers format

    Select the cells, go to format, and use this string for Custom format:

    [$€-2] #,##0.00
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: personalized numbers format

    Maybe I have misunderstood the question. My answer changes the formatting for everybody. Do you want to keep the formatting the way you want it for you, but let your customer see it a different way? If that is the case, then I believe MrShorty has the answer.

  5. #5
    Registered User
    Join Date
    05-21-2015
    Location
    Germany
    MS-Off Ver
    2010
    Posts
    17

    Re: personalized numbers format

    Thank you guys for your reply - but I can't use either of your options.
    Here is what I do. I am the middleman between two parties that form a contract. Both parties get a hard copy of their contract details. My excel file has three sheets: one is just for input, and the other two are for one party each.
    On the input sheet I just type all the data needed for each particular contract (name, address, amount of money, and so on). The two other sheets use the data from the input sheet and show it in the way, how the contracts are supposed to look like. On one sheet I can use the numbers format the way it is (e.g. 1.111.111,00), but on the other one I need to switch the punctuation (e.g. 1,111,111.00).

    As I have to do this quite often, I do not want to have to change the format manually every time.
    Personally I don't care about the format of the numbers. Either way is fine with me, but both parties want it their way.

    Is that possible?

  6. #6
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: personalized numbers format

    Maybe create a fourth worksheet, duplicating everything of the "other" (third?) worksheet, except change the number format (right click, Format Cells) used in that worksheet to what Jeff has suggested. Then for your Chinese customer, print this new worksheet instead?
    多么想要告诉你 我好喜欢你

  7. #7
    Registered User
    Join Date
    05-21-2015
    Location
    Germany
    MS-Off Ver
    2010
    Posts
    17

    Re: personalized numbers format

    I tried Jeff's suggestion, but it doesn't give me the correct result. I already tried countless customized formats, but I just can't find the right one.
    No matter what I do, either it still looks the same 1.111.111,11 or 1111111,11 or 1111111 but not 1,111,111.11
    BTW, the currency symbol (€) is not needed.

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: personalized numbers format

    Available number format codes: https://support.office.com/en-US/Art...rs=en-US&ad=US

    You may be able to figure something out from the above description of the different format codes. If you can scale your numbers by 100 (so Excel sees them as integers), you can probably do something like #\,###\,##0\.00 for those places where you want comma as thousands separator and period for decimal separator. Then, something like #\.###\.##0\,00 where you want commas as decimal separator and period as thousands separator. Note that, in both cases, the cell value is an integer like 123456789 where the 89 is the cents and the 1234567 is the whole euro.

    I'm sure you could parse some kind of "text" function that would concatenate the desired pieces into a text string, rather than use number formatting.

  9. #9
    Registered User
    Join Date
    05-21-2015
    Location
    Germany
    MS-Off Ver
    2010
    Posts
    17

    Re: personalized numbers format

    Well, we are definitely getting closer - I am still no there yet though.
    I can get the period as decimal separator and also the comma as thousands separator. But as soon as the number has fewer digits than how I formatted it, it still shows me the commas that I don't need at that moment (e.g.: ,,123.45 or ,123,456.78)
    I will keep on trying...
    Thank you for your help!

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: personalized numbers format

    How broad of a range of numbers do you need to this to handle? Under the "colors" section of the help page, it describes how to include "conditions" into the format code, I think you can have up to 3 conditioins. If you do not need to0 many conditions (<1000, <1000000, <1000000000 will be enough), then you can incorporate those conditions into your format code.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dynamic or personalized hyperlink vba excel
    By SERRY in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-16-2015, 08:37 AM
  2. Finding Un - Personalized names
    By shyam sundar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-25-2014, 02:51 AM
  3. Send personalized mass emails with multiple attachments
    By land_land in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2014, 01:45 AM
  4. Creating a personalized "data" form (VBA/Macros) - To add, retrieve, modify & delete info
    By Extricate_Excel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-08-2013, 02:25 PM
  5. [SOLVED] personalized form data show
    By Isaac in forum Excel General
    Replies: 1
    Last Post: 07-06-2006, 06:15 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1