How to convert dots to commas decimal notations using excel formula

Question

Let's say the user enters 1.234.567,89 or 1,234,567.89 or 1 234 567,89 in any excel cell one by one and in all the above cases the user should get 1234567,89 in the output cell.

Answer 1

=ARRAYFORMULA(IF(LEN(A1:A), 
 IFERROR(REGEXREPLACE(A1:A, "\s|\.", ), 
 REGEXREPLACE(""&A1:A, "\s|\.", ",")), ))

Answer 2

Excel

Try TEXT() with a custom format:

=TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,",",""),".","")," ",""),"[>=100]#\,#0;#")

Google Spreadsheets

Try using REGEXREPLACE():

=TEXT(REGEXREPLACE(TEXT(A1,"@"),"[ ,.]",""),"[>=100]#\,#0;#")