Text functions
In Excel text takes part in formulas just like numbers: a text string is put in double quotation marks. LEN(text) counts the characters, and spaces count too. To join texts the & operator is used: =A1&" "&B1; CONCATENATE(text1, text2, …) does the same, and newer Excel also has CONCAT and TEXTJOIN. REPT(text, n) repeats a text n times. REPLACE(old_text, start, number_of_characters, new_text) replaces the given part of a string. TEXT(value, format) turns a number into text in the given form, for example TEXT(7,"000") = “007”; VALUE(text) turns a text that looks like a number back into a number. A number stored as text is ignored by the SUM function, which is why VALUE is useful. When you practise with lists, use invented names: do not put the personal data of real people into shared tables.
| Function | Job |
|---|---|
| LEN(text) | number of characters |
| &, CONCATENATE | joining texts |
| REPT(text, n) | repeat n times |
| REPLACE(old_text, start, number_of_characters, new_text) | replace a part |
| TEXT(value, format) | number to text |
| VALUE(text) | text to number |
A school library gives each book a code made of a subject letter and a serial number. Instead of typing them one by one, the librarian uses a formula that joins the letter and the number. Hundreds of codes come out in the same pattern with fewer typing slips.
Librarians, office staff and data-cleaning specialists use functions that join text and check its length.
Count the letters in the word “school”. Then write “book”, “shelf” and a single blank space on three paper cards and join them in different orders.
| A | B | C | |
|---|---|---|---|
| 1 | Aziza | Karimova | Aziza Karimova |
| Formula | Result |
|---|---|
| =LEN("Informatika") | 11 |
| =REPT("*",5) | ***** |
| =REPLACE("Tashkent-5",10,1,"7") | Tashkent-7 |
| =TEXT(7,"000") | 007 |
| =VALUE("45")+5 | 50 |
“The text builder”: each pair writes an invented surname, first name and class in three cells and builds an entry such as “Surname I., class” with one formula; a neighbouring pair checks the result.