Tips n Tricks for Microsoft Excel

 

To remove any content or empty place from a column in Excel file.

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

e.g.: =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

Create conditional formulas

 The IF function uses the following arguments.

 

Formula with the IF function:

 logical_test: The condition that you want to check.

 value_if_true: The value to return if the condition is true.

 value_if_false: The value to return if the condition is false.

for reference you can use Microsoft

SQL SERVER – DBCC RESEED to reset Table Identity Value to zero(or any)

DBCC CHECKIDENT is used to reset the identity column of a table in SQL Server database. e.g. If a table "TEST" has Identity column and currently has 10 records. The next Identity column value would be (say ) "11", but we want the Identity column to be (say) 21, for that we need to reset the Identity column value. The Syntax to perform this task in SQL Server is as :

DBCC CHECKIDENT(,RESEED, Value)

so the sql code to reset would be      DBCC CHECKINDENT(TEST, RESEED, 20)

This will make the next row identity column value as 21.

NOTE: If the Identity column is set below the current value in the table it will violate the unique value constraint and will display an error message. And in order to perform this sql command admin rights are required in SQL Server.

How to make a text box take only numeric input or any specific input as per user's requirement using JavaScript

In order to insert only numeric value from textbox this JavaScript code could be applied.
Same code can be modified to restrict the users to enter different keyboard inputs as per requirement.
This can be done by changing the ASCII value mentioned in the JavaScript file.


JAVASCRIPT CODE:

write this function under the JavaScript code.

 function isNumber(event)
   {
            var KeyBoardCode = (event.which) ? event.which : event.keyCode;
            if (KeyBoardCode > 31 && (KeyBoardCode < 48 || KeyBoardCode > 57))
            {
                return false;
            }
            return true;
    }


HTML CODE:

use this code in the textbox field to perform the required operation.

onkeypress="return isNumber(event)"

One can make the text box readonly and nothing can be pasted into that by adding the following script in textbox:

onKeyPress = "javascript: return false;" onPaste = "javascript: return false;"