Step by step exercise. Text functions.


Objective

 

To practise the use of text functions in Excel2003.

 

Step by step exercise.

 

1 Please, open Excel2003, if it isn't already opened, so you can do this exercise.

2 Go to cell A1 and press , select Category Text and chose CODE() function, press OK.

3 Type as argument for that function letter a and press Enter.

You will obtain code 97, this is the number asigned to the letter a. Now we are going to check whether that is correct using the CHAR() function.

4 Go to cell B1 and press on , select the Category Text and select the CHAR().

5 As argument type 97. As you can see, Excel returns exactly the letter "a".

6 Go to cell B2 and type "Today is ". Make sure that you leave the blank spaces

7 Go to cell B3 and type " January ". Make sure you leave the blank spaces.

8 In cell B4 type 22 and in cell B5 type 2004.

Now we are going to create a concatenated phrase in several cells.

9 Go to cell C5 and press on , select the Category Text

10 Select CONCATENATE() function and as arguments type, in the first argument B2, in the second B4, as a third argument type " of", as fourth argument type B3, as fifth argument type "of " and as last argument type B5.

11 In order to have the date in only one cell the function must look like this =CONCATENATE(B2;B4;" of";B3;"of ";B5) *.

Now we are going to use the PROPER() function that is also very useful to format a text chain.

12 Go to cell A5 and type "john" in cell B5 type "adam" and in cell C5 type "smith". Do not type capital letters.

13 Now in cell D5 press on , select category Text, select the CONCATENATE() function press OK and choose as parametres A5, B5 and C5. You must add the spaces to separate the complete name. The formula must look like this =CONCATENATE(A5;" ";B5;" ";C5).

Now that you have the full name in one cell, you can use the PROPER() function.

14 Go to cell G5 and select the PROPER() function and pass it as parametre cell D5.

You must get in cell G5 John Adam Smith. The initials have been converted into capital letters.

15 Save the workbook in My documents folder in the hard disc, with the name Text functions.

16 Close the workbook.



   
Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited.
November-2005.