Paste link data from CSV file Problems

[mastbobby1]

Hello,

Thanks for all who will help me out from this situation.

Scenario
I have a "cost.csv" file that contains the cost of the toys. The cost is show in $ with two decimal places i.e. $0.75
I created a new MS Excel 2010 workbook file named as "customers.xlsx" that has four columns i.e. Item, Cost, Order, [Total Amount] columns. I used the paste link command and the Cost column data is linked with the cost column in "cost.csv" file.

Problem
When I write order quantity in the order column and then try to use the following expression in [Total Amount] column i.e. "Cost * Order" then I received error due to following reason i.e. "Cost is a text value and Order is a numeric value and excel is unable to evaluate the expression." I have done it by first remove the $ sign from cost using RIGHT and LEN FUNCTIONS.

Now I want to change the format of "Cost" column i.e. I want to show the cost in Euro but not in $. Here I am stuck as I am trying to change the format of the Cost linked cell but unable to change it either I applied the new format, the text remains shown $0.75?

Is this the problem as data is coming from .csv file? If it is then how I can accomplish the task? Waiting for your quick response....

Thanks to all who will help me to solve this issue.................

Thanks,
Bobby.

 

[Firefly2012]

Hi

Because you are using a csv file as the source data, links won't persist in your Excel file when the csv file is closed. You would be best advised to save the csv file as an Excel format file (e.g. xlsx).

Onto the next problem - this Cost column, try selecting the entire column (in the csv source file) and going Data>TextToColumns>Finish. This is a common trick to convert text values to numerics.

 

[mastbobby1]

Hi

Because you are using a csv file as the source data, links won't persist in your Excel file when the csv file is closed. You would be best advised to save the csv file as an Excel format file (e.g. xlsx).

Onto the next problem - this Cost column, try selecting the entire column (in the csv source file) and going Data>TextToColumns>Finish. This is a common trick to convert text values to numerics.

Dear Firefly,

Thanks for the quick response.

As far as the links persistance is concerned, its working in my scenario. But you have to open the source .csv first before opening the Excel file containing the link data.

Secondly, the problem is solved with your provided solution i.e. by using convertTextToColumn. Thanks once again.

Have a nice time.

Bobby.