• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Removing all charcters from cell but numbers

Thanks Narayan.

I would encourage everyone to try other values for rate to see what you get and share your results.

For example, a rate of 0 will SUM an array. (Remember that the rate is plugged into the formula TermN*1(/(1+rate)^n.)

-Sajan.
 
Sajan, appreciate this formula being brought to light and good idea to get people to try different values for rate to get to grips with the NPV function. Other useful values for rate are 0, 1 and -2, a few examples with explanations are in the links below:

http://dailydoseofexcel.com/archives/2009/09/30/adding-every-other-cell/
http://dailydoseofexcel.com/archives/2010/11/24/ranking-countries/#comment-54908
http://newtonexcelbach.wordpress.com/2011/12/01/linest-npv-irr-and-solving-polynomials/
http://newtonexcelbach.wordpress.com/2012/01/27/the-switch-function-vba-and-udf/#comment-3540

(Note: these all make use of NPV in the solution but some are admittedly more for the math-inclined )
 
Thanks Lori for posting those links. They were all new to me, and I am loving it!! (I have to admit that I haven't been reading many of the other Excel forums out there. I should make more of an effort to do so.)

I suspect that there are quite a few folks here that are math-inclined, and will enjoy studying those formulas.

Regards,
Sajan.
 
Hi Venkata,
Nice!
You can shorten it slightly as follows:
=MID(A1,LEN(A1)-SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")))+1,LEN(A1))
Give some thought to how you would extract numbers from the following strings also:
1234ABC --> result should be 1234
ABC1234DEF --> result should be 1234
AB12CD34EFG --> result should be 1234
-Sajan.
Click to expand...


Sir Sajan, i know this thread already got answer, but recently i learn about vbscript.regexp, i want share with you guys.for question you asked above we can use below VBA code also.
Code: 
Sub myTest()
   Dim myCel As Range
   With CreateObject("VBScript.Regexp")
         .Global = True
         .Pattern = "\D+"
  For Each myCel In Selection
           myCel.Value = .Replace(myCel.Value, "")
  Next
  End With
End Sub
 
Sajan,

I am using the formula below:
=NPV(-0.9,,IFERROR(MID(B11,1+LEN(B11)-ROW(OFFSET(B$4,,,LEN(B11))),1)%,""))&""

and it works fine (i even understand portions of it) until I reach cell b25...then it suddenly starts cutting off the last 3 digits of the return number. Can anyone help me understand why it is failing at this cell/row

Attaching the file:
 

Attachments

  • More coffee.xlsx
    25.3 KB · Views: 12
Excel-ant said:
Sajan,

I am using the formula below:
=NPV(-0.9,,IFERROR(MID(B11,1+LEN(B11)-ROW(OFFSET(B$4,,,LEN(B11))),1)%,""))&""

and it works fine (i even understand portions of it) until I reach cell b25...then it suddenly starts cutting off the last 3 digits of the return number. Can anyone help me understand why it is failing at this cell/row

Attaching the file:
Click to expand...
Try this in C4 with revised in highlighted red and Ctrl+Shift+Enter copied down,

=NPV(-0.9,,IFERROR(MID(B4,1+LEN(B4)-ROW(OFFSET(A$1,,,LEN(B4))),1)%,""))&""

Regards
Bosco
 
So Bosco, this works perfectly. Can you explain how that fixed it? How does referencing the Blank Cell A1 make it not fail on line 25(ish)?
 
Last edited by a moderator:
Hi all

Can someone tell me why this works if I copy both the cell and the cell it is referencing but if I try to manually input the formula in a different worksheet, it doesn't work? Similarly, if I click into the formula box and then click enter without making any edit, it returns #VALUE. If I then to try to re-enter the { and } at the ends of the formula, it returns the formula as text.

Thanks
 
OriginalOfKxG said:
Can someone tell me why this works if I copy both the cell and the cell it is referencing but if I try to manually input the formula in a different worksheet, it doesn't work?
Click to expand...

Hi and Welcome to the forum :awesome:

It is an {array} formula, you need to press Ctrl+Shift+Enter (not just enter) upon edit.

Regards,
 
Clearly a dearly loved old chestnut ;)

Really nothing new about this solution except that it draws upon recently available functions and uses named formulas throughout

chars: = MID( string, SEQUENCE( LEN( string ) ), 1 )
digits: = FILTER( chars, ISNUMBER( VALUE( chars ) ) )

The worksheet formula for the number:
= CONCAT( digits )
 
You must log in or register to reply here.
Back
Top