imported data format in Excel different and not adjustable

Highlighted
Occasional Contributor

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!

3 Replies
Highlighted

Hi Richard,

 

You still have apostrophe inside (thus text string). You may use 

=SUBSTITUTE(SUBSTITUTE(C2,"'",""),"CHF","")

to convert (first yellow column)

 

 

Highlighted

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

Highlighted

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.