SERVING THE QUANTITATIVE FINANCE COMMUNITY

  • 1
  • 4
  • 5
  • 6
  • 7
  • 8
 
User avatar
tagoma
Posts: 18362
Joined: February 21st, 2010, 12:58 pm

Excel tricks

February 10th, 2014, 3:42 pm

QuoteOriginally posted by: edouardi have fornight data (2 data for each month) to put in a spreadsheet.are there fancy things Excel is able of when it comes to deal with dates such as "1H Jan-14"???OH!
 
User avatar
tagoma
Posts: 18362
Joined: February 21st, 2010, 12:58 pm

Excel tricks

February 10th, 2014, 3:44 pm

Hi all.Is there a built in function or a simple trick to delete the content of all cells (say in a selection) that are NOT formulas?(something less ugly than a macro deleting the content of cells that do no contain the '=' character)
Last edited by tagoma on February 9th, 2014, 11:00 pm, edited 1 time in total.
 
User avatar
bearish
Posts: 5620
Joined: February 3rd, 2011, 2:19 pm

Excel tricks

February 11th, 2014, 2:41 am

The Excel VBA Range object has a property HasFormula, which is either true or false. By looping through each cell in your selection, you should be able to selectively delete the ones that come up false on this test.
 
User avatar
MHill
Posts: 488
Joined: February 26th, 2010, 11:32 pm

Excel tricks

February 11th, 2014, 10:47 am

QuoteOriginally posted by: edouardHi all.Is there a built in function or a simple trick to delete the content of all cells (say in a selection) that are NOT formulas?(something less ugly than a macro deleting the content of cells that do no contain the '=' character)Select your cellsCtrl + G (Go To command)Choose 'Special'Choose 'Formulas''OK'Delete Edit: Doh! re-read the question - choose 'Constants', not 'Formulas'
Last edited by MHill on February 10th, 2014, 11:00 pm, edited 1 time in total.
 
User avatar
bearish
Posts: 5620
Joined: February 3rd, 2011, 2:19 pm

Excel tricks

February 11th, 2014, 11:07 pm

QuoteOriginally posted by: MHillQuoteOriginally posted by: edouardHi all.Is there a built in function or a simple trick to delete the content of all cells (say in a selection) that are NOT formulas?(something less ugly than a macro deleting the content of cells that do no contain the '=' character)Select your cellsCtrl + G (Go To command)Choose 'Special'Choose 'Formulas''OK'Delete Edit: Doh! re-read the question - choose 'Constants', not 'Formulas'Nice! It actually came in handy today in tracking down cells that suffered from conditional formatting (a potential source of many headaches).
ABOUT WILMOTT

PW by JB

Wilmott.com has been "Serving the Quantitative Finance Community" since 2001. Continued...


Twitter LinkedIn Instagram

JOBS BOARD

JOBS BOARD

Looking for a quant job, risk, algo trading,...? Browse jobs here...


GZIP: On