Chapter 11

WORKING WITH FORMULAS AND FUNCTIONS CHAPTER - 11: USING FORMULAS TO MANIPULATE TEXT Structure 1.0 Learning Objectives 1.1 Introduction 1.2 How text is handled? 1.3 Compare two text strings in excel 1.4 Separate/Split text in excel 1.5 Summary 1.6 References 1.0 Learning Objectives After studying this unit, you will be able to:  Explain how text is handled in MS Excel?  Explain how to combine text in MS Excel  Explain Separate/ Split text in MS Excel 1.1 Introductions Excel can perform an array of basic math functions, and the articles listed below will show you how to create the necessary formulas to add, subtract, multiply, or divide numbers. You can subtract, divide, multiply, and add in Excel within the cells of a spreadsheet. You can also do exponents, change the order of operations, and do various mathematical functions in Excel. These features rely on cell references to other cells to make calculations. Page 17 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.2 How text is handled? TEXT is simply the group of characters and strings of characters which convey the information about the different data and numbers in Excel. Every character is connected with a code [ANSI]. The text comprises the individual entity character which is the smallest bit that would be found in Excel. We can perform the operations on the strings [Text] or the characters Characters are not limited to A to Z or a to z but many symbols are also included. Text is an inactive number type[format] in excel. Anything stored as text [number or date] won’t respond to any standard formulas or functions but specially designed text functions. [exceptions do occur in the case of numbers] If we need to make anything inactive, such as the Date to be non- responding to the calculation, we put it as a text. Similarly, if we want to avoid any calculations for a number it needs to be put as a text. 1.3 COMPARE TWO TEXT STRINGS IN EXCEL This situation can occur when we want to create some logical condition and check whether the two text strings are the same or not. Now the same can have two meanings. 1. Just the content is the same. For example “TEXT” and “text”. 2. Exactly same [ Content is same and the case is same]. For example, “teXT” and “text.  COMPARING THE TEXT FOR THE CONTENT  We are going to compare just the content and not the case.  This can be used simply in Excel. Page 18 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS  We can simply compare the cells by using the formula =Cell 1 containing text=cell 2 containing text.  This formula will return TRUE if the content is the same [ignoring the case] and false if the content is not the same.  COMPARING THE TEXT FOR THE EXACT CONTENT [CONTENT AND CASE]:  We are going to compare the two text strings exactly for the content and the case.  This is also not difficult.  We will make use of the function called EXACT (text 1, text 2). It compares the text to be exactly the same and returns true if the texts have the same content and same case. [EXACT TAKES ONLY TWO TEXTS AT A TIME].  We can simply compare the cells by using the formula = EXACT (Cell 1 containing text, cell 2 containing text)  The formula will return ‘TRUE’ if both the text strings are the same otherwise ‘FALSE’. Page 19 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS  COMBINE TEXT IN EXCEL  Combining text is excel is very useful technique.  Let us understand what we mean by combining the text. Combining means to stick the text to one another. For example, if one cell is having “ABCD” and the other is having the value of “EFGH”. If we combine them in the third cell the result will be “ABCDEFGH”. 1.5 SEPARATE/SPLIT TEXT IN EXCEL  SPLITTING/SEPARATING the text is also required many times in Excel.  This can be stated as removing any portion of the text or string from the cell and placing it into the different cells.  Splitting and separating the text can be done in many ways.  TEXT TO COLUMN We can split the text in a cell into different columns. The basis of the separation can be any delimiter like a comma, colon, etc. Page 20 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS  USING FLASH FILL  Separating a single word into many portions.  Separating Text on the basis of any character or pattern 1.6 Summary  Excel can perform an array of basic math functions, and the articles listed below will show you how to create the necessary formulas to add, subtract, multiply, or divide numbers.  TEXT is simply the group of characters and strings of characters that convey the information about the different data and numbers in Excel.  Separate/ Split can be stated as removing any portion of the text or string from the cell and placing it into the different cells. 1.7 References https://gyankosh.net/exceltricks/manipulating-text-in-excel-part-i/ https://www.vertex42.com/blog/excel-formulas/text-formulas-in- excel.html https://www.universalclass.com/articles/computers/excel/excel-2013- how-to-manipulate-text.htm https://www.oreilly.com/library/view/excel-2016- bible/9781119067511/c11.xhtml Page 21 of 63 All Rights Reserved. Vol. TLE001/03-2022