• 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.

If I happen to learn new trick, how do I share it here?

R

rumshar

Member
Hi All,
Was just curious to know if I happen to learn any new trick, can I share it here? If yes how?

With Regards
Rudra
 
Here's one trick:

If you have alphanumeric strings like abc1, cdefg223 etc. then to find out first digit position one might implement:
=MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B2,1),99))
but this needs to be array entered.

To avoid array entry you've to eliminate the error which you can do by writing formula like below:
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&"0123456789",1))
This is normally entered formula.
 
Was trying something while coding and got this function. WorksheetFunction.SumSq

Code: 
MsgBox Application.WorksheetFunction.SumSq(2, 3, 4)

Code: 
MsgBox Application.WorksheetFunction.SumSq(Range("a1:a3"))

This function will give sum of Squre of the arguments. For example I have taken numbers, 2,3,4.
Squre is 4,9,16, total is 4+9+16 so the answer will be 29.

I am sure most of members here know this, but for me this is new.
 
Ever needed to replace ALT+ENTER (Chr(10)) through FIND / REPLACE or Text to Columns and wondered how to type {ENTER} in FIND box.

Then in FIND / REPLACE dialog goto FIND box and press CTRL+J simultaneously and in REPLACE box type in replacement character and press "Replace All".

In Text to columns you have use the same key combination in Other category field.

-- I first read this in one of Rick Rothstein's posts.
 
You must log in or register to reply here.
Back
Top