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
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
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
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
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
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
I recently started a new job 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.
I tried the find and replace it didnt work.
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.
Aardigspook gave you the simplest way by going into options to change it.
thanks for the formula! ( I still can't master that formula). It worked.
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)
Bookmarks