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:


MS Excel: Lookup instead of Vlookup or Hlookup

The limitation of vlookup is that you can only lookup in the first column of array and bring back results from it or any column right of it. 
Similar limitation in Hlookup where you can only lookup in first row of array and bring back result from it or any row below it.

To get over with this limitation, use lookup instead of vlookup and Hlookup:

=lookup(Lookup Value, Lookup Vector, Result Vector)

See the examples below:

Use of Lookup for Vlookup




Use of lookup for Hlookup


Thursday, February 27, 2014

MS Excel : IfError function in Excel 2010


Iferror is new function in MS Excel 2010 to make life simple. It works as:

IFERROR(value, value_if_error)

See it's use with vlookup below:

=Iferror(vlookup(A1,Sheet2!A1:C60, 3,False),”Not Found”)


In this formula, if vlookup returns error because value is not found, instead of returning  #N/A, it will return “Not Found”.

MS Excel: Show Alphabetic Row or Column Number

To show row number in alphabetic order, simply right this formula is first location where you wish to start the alphabet i.e. A and then, drag and paste the formula in rows below:

=SUBSTITUTE ((ADDRESS (1, ROW (A1), 4)), 1,"")



Same the case of alphabetical column numbers, at start point, write the following formula and drag and paste on adjacent columns:


=SUBSTITUTE ((ADDRESS (1, Column (A1), 4)), 1,"")