Tuesday, September 15, 2015

Find Cells which have Strikethrough Text or numbers in MS Excel

To Find Cells which have Strikethrough Text or numbers in MS Excel:

  • -          Select a cell in the worksheet
  • -          Go to the "Find and Select" button and choose "Find"
  • -          Click "Options",  and then click the "Format" button”.
  • -          Click the "Font" tab.
  • -          Select the box Strikethrough" to place a check in it, and then click "OK."
  • -          Click "Find All"
  • -          A list of every cell that has strikethrough text or number in it will show up
  • -          Click on the name of a cell in the list to go to particular cell.


Monday, August 24, 2015

The INDIRECT function in MS Excel

The INDIRECT function is very useful in case you have derived or calculated in a cell the cell reference from where you want the data from.

For Example, if cell A3 has a value C5 (May Be calculated based on some formula or logic) and you want to refer C5 from A3 to get the value of cell C5:

=Indirect(A3)


Thursday, August 20, 2015

Remove Leading Spaces in MS Excel

Many times =Trim(Text) and =Trim(Clean(Text)) does not work in MS Excel to remove and clean leading spaces created due to non-breaking space (Char(160)) specially if you are copying data from web or bullet list in MS Word into an Excel Worksheet.

This problem can be resolved by :

=TRIM(CLEAN(SUBSTITUTE(Old Text,CHAR(160)," ")))


Thursday, June 25, 2015

Create Bullet in MS Excel

To create the bullet character, press ALT+0149 (Press Alt and type 0149 on the numeric keypad and release Alt).


Friday, February 28, 2014

MS Excel: Get preceding Zero of fixed width text field

When you open csv file in MS Excel, for any text field as number with preceding Zero, excel treats them as number and remove the preceding Zero.

Let us say, that field is 5 characters long:

00542

Excel will open it as (Assume in cell A1):

542

You can use the following formula to get back the Zero:

=Text(A1,”00000”)


See the example below: