1

Computer Science & Engineering 2111

Computer Science & Engineering 2111

Text Functions

CSE 2111 Lecture-Text Functions

2

LEFT(text, [num_chars]))Returns the number of characters specified starting from the beginning of the text stringSyntax• Text: The text that contains the characters you want to extract• num_chars: Specifies the number of characters you want to

extract starting from the leftmost character.– Default: 1

CSE 2111 Lecture-Text Functions

3

RIGHT(text, [num_chars])Returns the number of characters specified starting from the end of the text stringSyntax• Text: The text that contains the characters you want to extract• num_chars: Specifies the number of characters you want to

extract starting from the rightmost character.– Default: 1

CSE 2111 Lecture-Text Functions

4

MID(text, start_num, num_chars)Returns a specific number of characters from a text string, starting at the position you specify, based on the numbers you specify

Syntax• Text: The text that contains the characters you want to extract• Start_num: The position of the first character you want to

extract in the text.• Num_chars: Specifies the number of characters you want mid

to display

CSE 2111 Lecture-Text Functions

5

CONCATENATE(text1,[text2],…)Joins up to 255 text strings into one text string

Syntax• Text1: The first text item to be concatenated• Text2: The second text item to be concatenated--optional

CSE 2111 Lecture-Text Functions

6

TRIM(text)Removes all spaces from text except for single spaces between words

CSE 2111 Lecture-Text Functions

7

LEN(text)Returns the number of characters in a text string.

CSE 2111 Lecture-Text Functions

8

UPPER(text), LOWER(text)Converts text to uppercase/lowercase

CSE 2111 Lecture-Text Functions

9

PROPER(text)Capitalizes the first letter of each word

CSE 2111 Lecture-Text Functions

10

EXACT(text1, text2)Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences.

CSE 2111 Lecture-Text Functions

11

REPLACE(old_text,start_num,num_chars,new_text)Replaces part of a text string, based on the number of characters you specify with a different text string.

Syntax• Old_text: Text which you want to replace some characters• Start_num: The position of the character in old_text you want to replace• Num_chars: The number of characters in the old_text that you want to

replace with the new_text• New_text: The text that will replace the characters in the old_text

CSE 2111 Lecture-Text Functions

12

SUBSTITUTE(text, old_text, new_text, [instance_num])Substitutes new_text for old_text in a text string.

Syntax• text: Text or reference to a cell containing text for which you want to

substitute characters• Old_text: The text you want to replace• New_text: The text you want to replace old_text with• Instance_num: Specifies which occurrence of old_text you want to

replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text

CSE 2111 Lecture-Text Functions

13

FIND(find_text, within_text, [start_num])Locates one text string within a second text string and displays the starting position of the first text string from the first character of the second text string. (Case sensitive)

Syntax• Find_text: The text you want to find• Within_text: The text containing the text you want to find• Start_num: Specifies the character at which to start the search

– Default 1

CSE 2111 Lecture-Text Functions

14

Data Validation• Controls the type of data or the values input in a cell• Reduces input errors• Multiple criterion can be applied to a cell

CSE 2111 Lecture-Data Validation and Macros

Display a message dialog box Restrict input to a whole number within limits

Restrict input to values in a drop down list Restrict input to a decimal number within limits

Restrict input to a certain time frame. (either date, time, or both)

Restrict text to a specified length

Calculate what is allowed based on the value in another cell

Use a formula to calculate what is valid

15

MacroA series of commands and functions that are stored in a Microsoft Visual Basic module

• Automates your keystrokes and actions in Excel• Use these when you complete the same tasks repetitively• When you create a macro VBA program code is automatically

generated

CSE 2111 Lecture-Data Validation and Macros

16

Record Macro

CSE 2111 Lecture-Text Functions

Click on DeveloperClick on Record Macro

17

Macro Walk Through

CSE 2111 Lecture-Text Functions

Step 1: Delete the information in cells B1 and B2

Step 2: In Cell B1, Input the following Function

=CONCATENATE(LEFT(A1,3),”-”, MID(A1,4,3),”-”,MID(A1.7,4))

Step 3: In Cell B2, Input the following Function

=LEFT(A1,3)

Step 3: See next Slide

18

Macro Walk Through

CSE 2111 Lecture-Text Functions

Step 4: Display Message: File has been Updated!

Click on View

Click on Project Explorer

Double click on Module1

19CSE 2111 Lecture-Text Functions

Type this line at the beginning of the VBA Code

Type these two lines at the end of the VBA Code

Click on Stop Recording