• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Changing format to number


Hi Excel Ninjas,

I have a csv file and i converted it to excel, somehow the price column (E1) looks weird as i can't apply sum formula unlikely on column D. i have tried several ways: text to column, smart tag, paste special, value function, replace comma but none give a proper result. May i know if there is any proper formula?



Peter Bartholomew

Well-Known Member
I don't know about the OP but I am not sure of the logic.
Why TYPE rather than ISTEXT?
[since , for me, 'price' is a multi-cell range, TYPE(price) gave 64 but TYPE(@price) was OK and gave 2]
Why trim the decimal part? Perhaps to overcome an inappropriate decimal separator?

All I needed was
= --price
but I suppose that depends on locale settings.


You're welcome
FWIW, do you understand how it works ( might be useful later...)?
Hi Pecoflyer,
I actually want to ask that question, i need to understand the logic. Would you be able to breakdown the formula and explained, as you said it might be help me in the future. Thanking you in advance.


Excel Ninja
If even a sample data is so sensitive then it have to be really sensitive.
Many times a sample data has something same looking data which covers Your original so sensitive data.
Was You uploaded files data sensitive?
Seem You want to continue fixing something as manually ...
Do #8 reply = --price give expected result?
Last edited: