Microsoft Excel : Text Functions Part -3

Hi,

In previous post we have talked about some of the Text functions in detail. We will continue this post from where we left.

If you have not read previous posts, this post might be some confusing to you. I will request you to read these posts before proceeding.

Text Functions in Microsoft Excel Part -1

Text Functions in Microsoft Excel Part –2

Directly moving ahead to function details and their use.

9. FIXED()

Introduction: FIXED function Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.
Using Method: This function takes minimum of one and maximum of 3 parameters. First one is Number, this can be any number value or reference to a cell. Second is the decimals , for rounding. Last one is a Boolean option for keeping period separator or not.

=FIXED(139493.4532,2) or =FIXED(A1,-1,TRUE)

Example:

NOTE: Difference between ROUND function and FIXED function is, FIXED converts number to text after rounding but ROUND keep number to number after rounding.

10. LEFT()

Introduction: LEFT function returns specified number of characters from start based in start number (length) in a string.

Using Method: This function takes two parameters. First one is text to be searched, this can be simple string value or reference to a cell. Second is the number of characters to start.
=LEFT("Hello World",6) or =LEFT(A1,6)

Example:

11. LEN()

Introduction: LEN function returns number of characters in a string. In simple words, it returns the length of a string.

Using Method: This function takes single parameter and that is of type string or reference to a cell.

=LEN(“Hello World”) or =LEN(A1)

Example:

 

12. LOWER()

Introduction: LOWER function converts all characters in a string to lower case.
Using Method: This function takes a single parameter and that is of type string or reference to a cell.

=LOWER(“Hello World”) or =LOWER(A1)

Example:

 

13. MID()

Introduction: MID function returns the middle characters from a string specified by start number and length.
Using Method: This function takes three parameters. One is text which is required, another is Start number to search from and another is number of character to show or length to be picked.
=MID(“John is from Nepal”,9,10) or =MID(A1,9,10)

Example:

 

14. PROPER()

Introduction: PROPER function converts all characters and words to Title case as per English Grammar. Means all the beginning character of all words are in capital letter and other are in small letter regardless of their typography.
Using Method: This function takes a single parameter and that is text string or reference to cell .

=PROPER(“this is going to BE CHANGED in title”) or =PROPER(A1)

Example:

 

15. REPLACE()

Introduction: REPLACE function replaces a part of string with specified string.
Using Method: This function takes four parameters, First one is text to be replaced, then starting number of character where replace to be started, then number of character to replace (length), and final one is new text.
=REPLACE(A1,6,3,”was ”) or =REPLACE(A1,6,3,C1)

Example:

 

16. RIGHT()

Introduction: RIGHT function does the exactly same work as LEFT do but from opposite index. This returns the text from end of an string based on the length passed by user.
Using Method: This function takes two parameters and both are required. First one is string or reference to cell containing text and another is number of character to be shown.
=RIGHT(“The last five words”,5) or =RIGHT(A1,5)

Example:

  

I will be back with remaining text function in another post. Thanks for reading.

You can provide feedback using comments or contact page.

John Bhatt

Read More

Print GridView only in ASP.NET

Hi,

Lets come back to ASP.NET tips and trick series. We are once again going to learn new trick related to GridView. Previously I have posted following articles related to GridView.

• Export GridView data to Excel
• Pagination in GridView
• Export GridView data to Word
• Custom Error Message for Empty GridView
• GridView with scroll bar
• GridView with Fixed Header and Scroll Bar

Requirement

Suppose, you have a page where you are displaying any kind of data to user. You want to add a functionality to print these records. But problem with Browsers Default Print options (Ctrl+P) is that, it will print whole page. All header images, footer, sidebar and colors will printed, which is not required. You have two options, either create a new page for print view and redirect user to that page or just print the required area, in our case it is GridView. 

Most of developer working in a web applications have MasterPage for their web-application. MasterPage are similar to templates in normal HTML having some advanced functionality and customized code for maintaining easier and common look of website or application.

Lets have a look at below screen to make our requirement more clear.

Code for Default.aspx

This is the code for GridView.

   <asp:GridView runat="server" ID="gvProducts" AutoGenerateColumns="false" AllowPaging="false" AlternatingRowStyle-BackColor="Linen" HeaderStyle-BackColor="SkyBlue" Width="100%" OnPageIndexChanging="gvProducts_PageIndexChanging" EmptyDataText="Sorry! No Products to List. First Add from Add Product Link.">
                    <Columns>
                        <asp:TemplateField HeaderText="Product ID">
                            <ItemTemplate>
                                <asp:Label ID="lblProductID" runat="server" Text='<%#Eval("ProductID")%>' ToolTip="ID of Product as stored in Database."></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <Columns>
                        <asp:TemplateField HeaderText="Product Name">
                            <ItemTemplate>
                                <asp:Label ID="lblProductName" runat="server" ToolTip="Name of Product" Text='<%#Eval("ProductName")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <Columns>
                        <asp:TemplateField HeaderText="Brand">
                            <ItemTemplate>
                                <asp:Label ID="lblBrandName" runat="server" ToolTip="Brand of Product" Text='<%#Eval("BrandName")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <Columns>
                        <asp:TemplateField HeaderText="Category">
                            <ItemTemplate>
                                <asp:Label ID="lblProductCat" runat="server" ToolTip="Category of Product" Text='<%#Eval("CategoryName")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <Columns>
                        <asp:TemplateField HeaderText="In Stock">
                            <ItemTemplate>
                                <asp:Label ID="lblProductinStock" runat="server" ToolTip="Quantity available in Stock"
                                    Text='<%#Eval("UnitsInStock")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>

Code for Default.aspx.cs

We do not have much more at backend, we are just going to bind data to GridView.

 protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConToStore"].ConnectionString);
            SqlDataAdapter adp = new SqlDataAdapter("Select * from Products,Brands,Category where Products.BrandID=Brands.BrandID and Products.CategoryID=Category.CategoryID", con);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            gvProducts.DataSource = ds.Tables[0];
            gvProducts.DataBind();
        }
    }

Printing Page.

Lets press Ctrl+P key combination to print page, before printing see the Preview.

Printing GridView Only

We need to make following change to print GridView only. Lets start with HTML.

Wrap the GridView inside a Table or Panel. I am using Table. You can also go with Panel.

  <table width="70%" id="pnlGridView" runat="server" align="center" class="ContentTable">
        <tr>
            <td colspan="2" align="center">
                <h1>All Products in Store</h1>
            </td>
        </tr>
        <tr>
            <td> </td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:GridView runat="server" ID="gvProducts" AutoGenerateColumns="false" AllowPaging="false" 
    AlternatingRowStyle-BackColor="Linen" HeaderStyle-BackColor="SkyBlue" Width="100%" 
    OnPageIndexChanging="gvProducts_PageIndexChanging" 
    EmptyDataText="Sorry! No Products to List. First Add from Add Product Link.">
                    ...............
     .............
                </asp:GridView>
            </td>
        </tr>
        <tr>
            <td align="right">
                <asp:LinkButton ID="lnkPrint" runat="server" ToolTip="Click to Print All Records" Text="Print Data" OnClick="lnkPrint_Click"></asp:LinkButton>     
                <asp:LinkButton ID="lnkExportAll" runat="server" ToolTip="Export this List" Text="Export to Excel" OnClick="lnkExportAll_Click"></asp:LinkButton>
                     
                <asp:LinkButton ID="lnkAddNew" runat="server" ToolTip="Add New Product"
                    Text="Add New" OnClick="lnkAddNew_Click"></asp:LinkButton>
            </td>
        </tr>
    </table>

Now just add following JavaScript function in head section and assign that function to Print linkbutton.

    <script language="javascript" type="text/javascript">
        function PrintPage() {
            var printContent = document.getElementById('<%= pnlGridView.ClientID %>');
            var printWindow = window.open("All Records", "Print Panel", 'left=50000,top=50000,width=0,height=0');
            printWindow.document.write(printContent.innerHTML);
            printWindow.document.close();
            printWindow.focus();
            printWindow.print();
        }
    </script>

Now lets click on Print Link button which we have created. Following will be output for above code.

Bingo! It was the requirement.
Hope you enjoyed reading. If you have any feedback or suggestions, please send us as comment or using contact options. Keep sharing.
John Bhatt

Read More

Text Function : Microsoft Excel Part-2

Hi,

Lets come back to Excel series. We have previously talked about text functions in Excel. Before proceeding to detailed description and use of these functions, I would recommend you all to read previous part of this post at Text Functions in Microsoft Excel - Part 1.

1. BAHTTEXT()

Introduction: BAHT is the currency name of Thailand. This function simply convert the number to BAHT text and adds a sign also. But in Thai Text. In simple word, this function converts number to currency in Thai language.

Using Method: This function simply takes a single parameter. A number and writes BAHT amount in words.

=BAHTTEXT(1000) or =BAHTTEXT(A1)

Example: 

2. CHAR()

Introduction: CHAR function is derived from word character. As simply name suggests, this function returns the character of your input number based in your computer setting. This takes value from 0-255 and returns all the character set based in your value.

Using Method: This function takes single parameter and that is integer from 0-255.

=CHAR(48)

Example: Suppose ASCII value for 0 (number zero) is 48.

3. CLEAN()

Introduction: CLEAN function simply removes the non-printable character from your supplied string. 

Using Method: This function takes single parameter and that is of any kind of value in cell.

=CLEAN(A1) or =CLEAN(CHAR(17)&"This is actual Printable Text."&CHAR(17))

Example:

4. CODE()

Introduction: CODE function works just opposite to CHAR function. This returns the character code of your system as input character. 

Using Method: This function takes single paramater and of type Character as input. 

=CODE("A") or =CODE(A1)

Example:

5. CONCATENATE()

Introduction: CONCATENATE function joins multiple text string into one single String. This works with multiple cells.

Using Method: This function can take multiple inputs of different types separated by comma (,).
=CONCATENATE(A1,A2,A3)

Example:

6. DOLLAR()

Introduction: DOLLAR function converts your number to currency format as per your system locale setting. If you are using United States as your location, then this function will put a dollar sign and comma separator. If you are in India, this will put a Rupees sign as per your system and write as currency.
Using Method: This function takes two parameter, one is value and other is decimal. Then this will convert your number to currency text with decimals as your choice.
=DOLLAR(100,2) or =DOLLAR(A1,2)
Example:

7. EXACT()

Introduction: EXACT function compares two strings and returns Boolean value, True or False. This is case sensitive. If two strings are exactly same, this function returns TRUE and if any of character is different, then FALSE.
Using Method: This function takes two parameters and both in String format from different cells.
=EXACT(A1,B1) or =EXACT("Google","GOOGLE")
Example:

8. FIND()

Introduction: FIND function returns the position of search string in another string. This is case sensitive. 

Using Method: This function takes minimum of two and maximum of 3 parameters. First one is Find text, this can be simple string value or reference to a cell. Second is the Within text, string where to be searched. Last one is Start number, starting position from where to search. This is optional field.

=FIND("Help",A1) or =FIND(A1,B1,2) 

Example: 

Keep reading. I will be writing about remaining function on later posts. Till then keep sharing your knowledge and feedback.

+John Bhatt 

Read More