This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Other Open Source Projects and the fly likes Excel formula problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "Excel formula problem" Watch "Excel formula problem" New topic
Author

Excel formula problem

Prashant Langade
Ranch Hand

Joined: Jul 01, 2009
Posts: 71
Hi

I have excel sheet in which i have a first column(A1) having value as '1000 and i would like to use the value of column A1 in the formula for another cell as

Add(A1,5000) but as the value in A1 is '1000,i am not getting the formula for the cell calculated as '1000+5000 gives problem as ' is appened to 1000.
So i would like to know if we can only use 1000 leaving behind the ' sign. so that the formula gives me 6000 as a answer.

Basically i am looking for a formula in which i will check if the first column(A1) value contains/starts with ' ,then i will remove that sign from the value and use the rest of the value in the calculation.

i.e A1 contains '1000
so i want only 1000 so that formula in another cellAdd(A1,5000) will calculate value as "1000+5000" which is 6000

Can any one please help me?

Prashant
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41034
    
  43
Excel has string functions that could be used to remove all characters that can't occur in numbers.

Are you trying to do this in Java code, or using an Excel Macro or VBA script?


Ping & DNS - my free Android networking tools app
 
wood burning stoves
 
subject: Excel formula problem
 
Similar Threads
Formula not rendered with POI 3.6
POI 3.7 : Formulas refering to other formulas giving #VALUE! error
JExcelApi - read a cell that contains a formula, but want to read the calculated value of the formul
Problem in reading Numeric value as String fom Excel file using Apache POI
Read MS Excel Sheet Formula Execution Value Without Opening it