Delete thousand separator

gerard1234

New Member
Joined
Oct 11, 2018
Messages
5
I want to automatically delete the thousand separator in this value. Can someone pls. help me with a Excel formula?


1.500.00
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,197
Office Version
  1. 365
Platform
  1. Windows
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,197
Office Version
  1. 365
Platform
  1. Windows
Thanks. See if this does what you want. If not give more details about formatting etc.

Excel Workbook
AB
1OriginalExpected
21.500.001500.00
310.500.0010500.00
4500.00500.00
Remove Dots
 

gerard1234

New Member
Joined
Oct 11, 2018
Messages
5

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,197
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 11, 2018
Messages
5

ADVERTISEMENT

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


'1.500.00=SUBSTITUEREN(LINKS(A1,LENGTE(A1)-3),".","")&RECHTS(A1,3)

<tbody>
</tbody>






I've translated the formula into Dutch language.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,197
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 11, 2018
Messages
5
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,197
Office Version
  1. 365
Platform
  1. Windows
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. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,752
Messages
5,524,631
Members
409,597
Latest member
Dannydev

This Week's Hot Topics

Top