Hi, I need to remove certain characters from a cell and have no clue. I've tried to use REPLACE and SUBSTITUTE but none of them made exactly what I need. Let me explain.
I have imported prices from CSV and they are formatted as text an they contain space, EURO symbol and comma (like 13 920,00 €). I can't change this. The problem is, that the when I use SUBSTITUTE, I can remove the last 5 characters and the result is fine. The problem is when there are some cents (like 6 258,30 €) - than the result is the same as original text.
I've tried also REPLACE function to remove exact number of character but here I had problems with different amounts (removing everything from 5 character works for 13 920,00 € but not for 46,20 €). Is it possible to remove character from the end and not from the beginning?
Please check the enclosed file, it will be more clear what I mean.
Thanks for any help!
sample.xls
Bookmarks