+ Reply to Thread
Results 1 to 10 of 10

Changing decimal and thousands from a comma to a period

  1. #1
    Forum Contributor
    Join Date
    06-27-2012
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    164

    Changing decimal and thousands from a comma to a period

    50,000
    2,780
    50,000
    100,000
    16,667

    25,000
    6,900
    50,000
    10,000
    50,000
    50,000
    50,000
    33,333
    25,000
    25,000
    66,667
    100,000

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    7,856

    Re: Changing decimal and thousands from a comma to a period

    If you need it only to be visible as different separators - use custom formatting https://support.office.com/en-au/art...2-09fab54be7f4
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    09-27-2015
    Location
    USA
    MS-Off Ver
    2013/Office 365
    Posts
    51

    Re: Changing decimal and thousands from a comma to a period

    You can use ctrl+h to find and replace Find what: , Replace with: . You can also format the cells as text and type . instead of , for future entries. Hope that is what you're looking for, if not then post a workbook with an example

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.54 for Mac (home)
    Posts
    7,369

    Re: Changing decimal and thousands from a comma to a period

    I tried the find and replace and that didn't work because it is formatting, I finally settled on this formula but it depends on the length of your strings...
    =IF(LEN(A1)=4,LEFT(A1,1)&"."&RIGHT(A1,3),IF(LEN(A1)=5,LEFT(A1,2)&"."&RIGHT(A1,3),IF(LEN(A1)=6,LEFT(A1,3)&"."&RIGHT(A1,3),""))) and you'd have to add appropriate for any other lengths.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,403

    Re: Changing decimal and thousands from a comma to a period

    If you want your thousands and decimal separators always to be a dot instead of a comma, you can do this in Excel options. Click the 'File' tab, then 'Options', then 'Advanced'. In the top 'Editing Options' section, there's an option to un-select 'Use system separators' and put in your own 'Decimal separator' and 'Thousands separator'.
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  6. #6
    Forum Contributor
    Join Date
    06-27-2012
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    164

    Re: Changing decimal and thousands from a comma to a period

    I tried the find and replace it didnt work.

  7. #7
    Forum Contributor
    Join Date
    06-27-2012
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    164

    Re: Changing decimal and thousands from a comma to a period

    thanks for the formula but i need something more simple . As for the office support site it doesn't show on how to change from a comma to a period.

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.54 for Mac (home)
    Posts
    7,369

    Re: Changing decimal and thousands from a comma to a period

    Aardigspook gave you the simplest way by going into options to change it.

  9. #9
    Forum Contributor
    Join Date
    06-27-2012
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    164

    Re: Changing decimal and thousands from a comma to a period

    thanks for the formula! ( I still can't master that formula). It worked.

  10. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.54 for Mac (home)
    Posts
    7,369

    Re: Changing decimal and thousands from a comma to a period

    BTW, with my formula, if you don't want a helper column, after converting them you can copy >> paste special >> values over the original column then get rid of the helper column. That way you don't have to change your options setting.

    EDIT, if you have questions about the formula don't be shy, just ask.

+ 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. Replies: 2
    Last Post: 08-19-2013, 06:17 PM
  2. Syntax for formatting a number with a comma for thousands
    By HangMan in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-30-2013, 06:01 PM
  3. [SOLVED] Need a Macro / VBA to find numbers in a text string and add a thousands comma
    By chicolocal in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-27-2012, 03:12 PM
  4. Replies: 4
    Last Post: 09-06-2012, 07:51 AM
  5. decimal separator is comma, not period
    By lmmohr in forum Excel General
    Replies: 2
    Last Post: 02-13-2012, 02:32 PM
  6. Decimal vs. Thousands Separator
    By nsv in forum Excel General
    Replies: 5
    Last Post: 12-28-2010, 06:05 AM
  7. Replies: 3
    Last Post: 07-05-2006, 01:57 PM
  8. [SOLVED] thousands comma separator in VBA
    By Wendy Francis in forum Excel General
    Replies: 2
    Last Post: 10-24-2005, 09:05 PM

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