Add Dot for thousand seperators

[Belleke]

I have this formula: =IF(LEN(A1)=1,TEXT(A1,"0"),TEXT(A1,"0.00")) in CF
How do I add the thousand separator.
4000 should be 4.000
10000 should be 10.000
4000,25 should be 4.000,25
10000,60 should 10.000,60
and if possible 10000,50 should be 10.000,50, not 10.000,5

Thanks in advance
It can be a formula for office 365

 

[Marc L]

Change the Regional settings or just the Excel settings …​

​

 

[Belleke]

That's not wat I ask,I don't want to change any settings, this applys for one worksheet only

 

[bosco_yip]

Maybe,

B1, copied down :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(FIND(".",A1)),TEXT(A1,"#,000.00"),TEXT(A1,"#,000")),",","@"),".",","),"@",".")

 

[Belleke]

@ Bosco_Yyp I tried to transelate the formula,no luck, kan you post a small example pls?

 

[bosco_yip]

@ Bosco_Yyp I tried to transelate the formula,no luck, kan you post a small example pls?

Please see my attachment

Regards

 

[Hans Knudsen]

I am not sure bosco_yip's solution is "internatioanal-safe"
I use comma as decimal separator and dot as thousand separator and when I open the file it shows something different than what is asked for (see attached file).

 

[Belleke]

@Hans Knudsen I have the same problem.
I didn'react yet because it is verry late here, I wanted to react tomorrow

 

[deciog]

Gentlemen, I also have this problem but whenever there is formatting between quotation marks I change
the decimal point for comma,
the comma for decimal point,
Because Excel cannot change the value between quotation marks thinking it is text

Formula looks like this
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(FIND(",",A1)),TEXT(A1,"#.000,00"),TEXT(A1,"#.000")),".","@"),".",","),"@",".")

Decio

 

[Belleke]

No luck at all.
I also followed the suggestion of Marc L,no luck also.
I tryed the formula of deciog, no luck, I get an error.
@deciog
Can you post a small example pls

 

[shrivallabha]

No luck at all.
I also followed the suggestion of Marc L,no luck also.
I tryed the formula of deciog, no luck, I get an error.
@deciog
Can you post a small example pls

What is the format of source column data? What @Marc L has suggested shall work if data is numeric. All you need to then is to invoke following cell formatting dialog and use 1000 separator:

 

[Marc L]

Yes as it is how Excel was designed, works like this since last century …​

​

 

[deciog]

Using your colleague Bosco's spreadsheet, put it in the Portuguese Brazil format, if you still have an error for your version, which is the country of your Excel

Hope this helps

decio

 

[GraH - Guido]

@Belleke

I think you are on European settings, like myself. this formula below seems to work.
=IF(MOD(VALUE(A1);1)>0;TEXT(A1;"#.##0,00");TEXT(A1;"#.##"))

I took Bosco's file as input.

But I'm not sure what you mean by CF? Conditional format, Custom Format?

 

[Excel Wizard]

Number format number with 0 decimal and use separator

Conditional formatting
=mod(a1,1)
Number format number with 2 decimal and use separator

Formula for EU setting ( dot for thousand separators)

=SUBSTITUTE(FIXED(A1),",00",)

Formula for US, UK setting ( comma for thousand separator )

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FIXED(A1,(MOD(A1,1)>0)*2),".","_"),",","."),"_",",")

Universal formula

=IF(LEFT(RIGHT(FIXED(A1);3))=".";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FIXED(A1;(MOD(A1;1)>0)*2);".";"_");",";".");"_";",");FIXED(A1;(MOD(A1;1)>0)*2))

 

[Belleke]

First of all, thanks to everybody that helped to find a solution for this topic.
The solutions of Decio, Grah-Guido and Excel Wizard are working flawless.
This topic is solved.