Jan 22 2019 06:56 AM
Hello all
I have a table imported into Excel (see attachment). On some cells I got the message with the known green triangle of how to transform them (number, text etc.). I changed them to numbers blue table). By starting with calculations and formulas, I do in another cell / table (yellow) with referring to the imported information (green table without the CHF). Now I get the massage #wert! in some of the cells. By investigating, this mast be an issue of the format of the cell (blue table). As in some the numbers and calculations are correct and some give the mentioned massage #wert! (blue and yellow tale same characteristic)
I tried to define on all cells the same format, what I can see in the field "cell format".
But now when I change the format from numbers to standard or vis versa (blue table), all the correct calculating once changes. All the others remain unchanged. How can I force all cells to change the format? With the normal selection and format change it is not possible. Also, the cell format copy does not help?
Appreciate any support!
Thanks!
Jan 22 2019 07:20 AM
Hi Richard,
You still have apostrophe inside (thus text string). You may use
=SUBSTITUTE(SUBSTITUTE(C2,"'",""),"CHF","")
to convert (first yellow column)
Jan 23 2019 12:08 AM
Hello Sergei
Sorry I was not able to change it. I get error massages with this formula. How can I see it is still a text cell and is there a other way of getting this changed into numbers?
Regards
Richard
Jan 24 2019 01:30 PM
Hi Richard - could you attach sample file with the formula where it doesn't work? In my file which is in previous post formula works.