I am adding TEXT in the formula but when I am using this answer in another calculation it does not take the number value of it.I want the Number and TEXT to be shown in the same cell but when I use that cell for calculation it should use only NUMBER value.
What is the syntex for doing that?
Example:In cell R15 I have answer as 475.82 KN.When I use this cell for calculation it should take only number value i.e 475.82 and not "KN".
It would be best to use a Custom Format to apply the "KN" and leave the underlying value as just a number
Custom Format being say: #,##0.00" KN";-#,##0.00" KN";0.00;@
You can then enter 475.82 into R15 and the KN mask will appear but you can perform arithmetic operations on the cell as normal.
(of course if the text being applied is inconsistent across cells then you will need to use a formula based approach in your summary calcs)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Hi,
You have to isolate the number, and then convert to a value. In your example you could use
=VALUE(SUBSTITUTE(R15,"KN",""))
Last edited by sweep; 08-18-2010 at 03:46 AM. Reason: wrong cell ref in example
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks