Scribd Logo
Upload

Welcome to Scribd!

  • 59 views

    Lecture 2

    Uploaded by

    Paras Jain
    The TRIM function removes leading, trailing, and extra internal spaces from text. The CONCATENATE function joins multiple text strings into one string. The SUBSTITUTE function replaces part of a text string with different text.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd

    Lecture 2

    Uploaded by

    Paras Jain
    59 views17 pages
    The TRIM function removes leading, trailing, and extra internal spaces from text. The CONCATENATE function joins multiple text strings into one string. The SUBSTITUTE function replaces part of a text string with different text.

    Original Title

    Lecture_2

    Copyright

    © © All Rights Reserved

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The TRIM function removes leading, trailing, and extra internal spaces from text. The CONCATENATE function joins multiple text strings into one string. The SUBSTITUTE function replaces part of a text string with different text.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    59 views17 pages

    Lecture 2

    Uploaded by

    Paras Jain
    The TRIM function removes leading, trailing, and extra internal spaces from text. The CONCATENATE function joins multiple text strings into one string. The SUBSTITUTE function replaces part of a text string with different text.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    You are on page 1of 17

    TRIM

    The function removes spaces all before and following


    the words i.e. leading and trailing spaces - and also
    removes extra spaces between words but does not
    remove the single space between words.

    =TRIM(Text)
    Example

    String Result Formula


    Student 1 Student 1 =TRIM(L16)
    Student 1 Student 1 =TRIM(L17)
    Student 1 Student 1 =TRIM(L18)

    Student 1
    CONCATENATE

    This function joins several (more than 1) text


    string into 1 string

    =CONCATENATE(String1, and so on..)


    OR
    =String1&String2& so on..
    Example

    Students Maths Science


    Student 1 95 83
    Student 2 65 53
    Student 3 85 80
    Student 4 99 99
    Student 5 45 60

    =CONCATENATE()
    Students Maths Science
    Student 1 Student 1 has scored 95 in Maths
    Student 2 Student 2 has scored 65 in Maths
    Student 3 Student 3 has scored 85 in Maths
    Student 4 Student 4 has scored 99 in Maths
    Student 5 Student 5 has scored 45 in Maths
    English
    81
    75
    90
    85
    55

    =&" "&
    English
    Student 1 has scored 81 in English
    Student 2 has scored 75 in English
    Student 3 has scored 90 in English
    Student 4 has scored 85 in English
    Student 5 has scored 55 in English
    SUBSTITUTE

    This function is used to replace a part of text in a


    string with something else. SUBSTITUTE function
    is used to clean data. This function is case-
    sensitive.

    =SUBSTITUTE(Text, Old_Text, New_Text,


    [instance_number])
    Example

    String Result
    Stubent Student
    Student 1 scored 38 marks in Science Student 1 scored 95 marks in Science
    Student 1 scored 85 marks in Science Student 1 scored 85 marks in English
    Formula
    =SUBSTITUTE(K7,"b","d")
    =SUBSTITUTE(K8,"38","95 ")
    =SUBSTITUTE(K9,"Science","English ")
    UPPER and LOWER

    This function converts the entire text string into


    uppercase (lowercase) letters

    =UPPER(Text)
    or
    =LOWER(Text)
    Example

    Students UPPER lower


    Student 1 STUDENT 1 student 1
    Student 2 STUDENT 2 student 2
    Student 3 STUDENT 3 student 3
    Student 4 STUDENT 4 student 4
    Student 5 STUDENT 5 student 5
    LENGTH

    This function returns the number of characters


    in a string (text or numeric value). It also counts
    the spaces and special characters.

    =LEN(Text)
    Example

    Students Length
    Student 1 9
    Student two 11
    Student three 13
    Student four 12
    Student five 12
    LEFT, RIGHT & MID

    This function returns the number of specified


    characters from the start/end of the string.
    "MID" returns the number of specified
    characters from the specified point.

    =LEFT(Text, [num_chars])
    =RIGHT(Text, [num_chars])
    =MID(Text, [starting_position], [num_chars])
    Example

    QR code Value Year of Man Product ID


    2018abcde43 2018 abcde
    2018werty44 2018 werty
    2018cvbnm43 2018 cvbnm
    2017start45 2017 start
    2018techa44 2018 techa
    2016acade43 2016 acade
    Group
    43
    44
    43
    45
    44
    43

    You might also like