# 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. ## 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

3. ## 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. ## 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.

5. ## 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'.

6. ## Re: Changing decimal and thousands from a comma to a period

I tried the find and replace it didnt work.

7. ## 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. ## 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. ## 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. ## 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.

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

#### 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