Delete thousand separator

[gerard1234]

I want to automatically delete the thousand separator in this value. Can someone pls. help me with a Excel formula?


1.500.00

 

[Peter_SSs]

Welcome to the MrExcel board!

One example doesn't give us much to go on. Could we have some more examples to see if the data varies (eg like 2.356.659.23 or just 569.658 etc)
Giving the expected results also makes the task clearer for potential helpers.

 

[gerard1234]

Thank you.

Original Expected
1.500.00 1500.00
10.500.00 10500.00
500.00 500.00

 

[Peter_SSs]

Thanks. See if this does what you want. If not give more details about formatting etc.

Excel Workbook
	A	B
1	Original	Expected
2	1.500.00	1500.00
3	10.500.00	10500.00
4	500.00	500.00
Remove Dots

 

[gerard1234]

Hi. thank you very much. However it is not working. Apparently I forgot to tell that the original value is a text value. Sorry that I didn't say this earlier. Is there also a solution if the original value is a text value?

 

[Peter_SSs]

Hi. thank you very much. However it is not working. Apparently I forgot to tell that the original value is a text value. Sorry that I didn't say this earlier. Is there also a solution if the original value is a text value?

I had already assumed that they were text values and the formula works for me with column A being text - as evidenced by my screen shot above.

Just saying "it is not working" gives nothing to go on.

Does it return an error? (details)
Does it return a blank result?
Does it return wrong values? (What results did it return for those 3 sample values for you?)

 

[gerard1234]

Thank you. It gives a message like this formula is not accepted.


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]'1.500.00[/TD]
[TD]=SUBSTITUEREN(LINKS(A1,LENGTE(A1)-3),".","")&RECHTS(A1,3)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]






I've translated the formula into Dutch language.

 

[Peter_SSs]

I think the column you are putting the formula in is also formatted as Text. Change the format of that formula column to General and then enter the formula again & copy it down.

 

[gerard1234]

I think the column you are putting the formula in is also formatted as Text. Change the format of that formula column to General and then enter the formula again & copy it down.

Thank you. Unfortunately it is not working. Are there more options?

 

[Peter_SSs]

After changing the formula column to General formatting, did you actually re-enter the formula?

Unfortunately it is not working.

Also remember my comments in post 6.