COMM 391 Notes - Substitute Function PDF

Preview

Summary

Notes taken in class...

Description

TRIM FUNCTION • •

TRIM function removes all spaces from text strings, EXCEPT for single spaces between words. TRIM function syntax:

=TRIM(text) • • • • • •

The text can be an actual text you type in the formula with quotation marks or referred to from another cell. You can also use (nest) LEFT, RIGHT, or MID function to refer to the text you want to trim. In cell D2, start your formula by typing =TRIM( Because the text we want to refer to is from column A, we can refer to each cell in column A. So, your formula should look like this: =TRIM(A2) Drag the formula down to cell D6, and compare your results will Figure 3 below.

SUBSTITUE FUNCTION • •

SUBSTITUTE function substitute a new text string for an old text string. This function is used to substitute a specific text in a text string. SUBSTITUTE function syntax:

=SUBSTITUTE(text,old_text,new_text,[instance_num]) •

• • • • • • • •

The text can be an actual text you type in the formula with quotation marks or referred to from another cell. You can also use (nest) LEFT, RIGHT, or MID function. This rule is also true with old_text and new_text. Explanation of instance_num follows in later slides. It is optional. We now convert COMM course codes into BUSI. In cell E2, start your formula by typing =SUBSTITUTE( Because the text we want to refer to is from column D (the trimmed course code), we refer to cell D2. The old_text is “COMM” while the new_text is “BUSI”. In this scenario, we don’t need to use instance_num. So, your formula should look like this: =SUBSTITUTE(D2,"COMM","BUSI") Drag the formula down to cell D6, and compare your results will Figure 4 below.

• • •

• •

Instance_num is optional. If you use instance number (1, 2, 3, etc.), Excel will only substitute the new_text for the old_text in that specific instance. Consider the course code COMM 335. Suppose the Dean wants to change COMM 335 to COMM 235. If you decide to use SUBSTITUTE function to change the course number, instance_number is required because you only want to change the number 3 the first time Excel finds the number 3. The instance number is, therefore, 1.

If you change the instance_num to 2, Excel will change the second time it finds the number 3. In this scenario, if you change the instance_num to 2, the course code will be COMM 325. If you don’t specify the instance number, Excel will always change the number 3 to 2 every time it finds the number 3. Without instance number, the course code will become COMM 225....