Remove certain characters from a cell

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

Hard to tell what results you're expecting from your description + the posted book.

Perhaps

=LEFT(A2,LEN(A2)-1)

If that's not it

In addition to the incorrect results you've tried, can you repost the book with a column containing the results you're trying to achieve?

Thank you very much FDibbins! It works perfectly... :-)

Hi abousetta, your script works also and the result for me is the same...

Dear M8R-l3mfmi,

try this

Please Login or Register  to view this content.

Regards,
Nandkumar S.