One code, four facts
A pupil code like Y09-AMY-03 packs in the year, the initials and the form number. The office wants each fact in its own column. It also wants first name and last name joined into one.
Retyping hundreds of codes is slow. Text formulas take them apart and join them up.
Key words
| Word | What it means |
|---|---|
| CONCATENATE | joins pieces of text into one piece |
| LEFT | takes a number of characters from the start of text |
| RIGHT | takes a number of characters from the end of text |
| MID | takes characters from the middle, from a start place for a length |
| Character | one letter, digit, space or symbol |
| String | a piece of text, in programming |
| Position | the place of a character, counted from 1 in a spreadsheet |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can use CONCATENATE, LEFT, RIGHT and MID to join and split text.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
No spreadsheet here
Important
This platform has no spreadsheet program. Here the cells are a table and the formula is shown as text. You do the same job with code. On site you use a real spreadsheet program.
The pupil codes as cells
| Row | A code | B first name | C last name |
|---|---|---|---|
| 1 | Y09-AMY-03 | Amy | Reed |
| 2 | Y07-BOJ-11 | Bo | Jones |
| 3 | Y11-CYD-07 | Cy | Dean |
The cells students fill in are left blank in this preview.
Four text formulas, row 1
| Formula | Gives | Why |
|---|---|---|
| `=CONCATENATE(B1," ",C1)` | Amy Reed | First name, a space, last name |
| `=LEFT(A1,3)` | Y09 | The first 3 characters |
| `=RIGHT(A1,2)` | 03 | The last 2 characters |
| `=MID(A1,5,3)` | AMY | Start at place 5, take 3 |
The cells students fill in are left blank in this preview.
Try it: the same jobs in Python
code = "Y09-AMY-03"
print(code[0:3]) # LEFT 3
print(code[len(code) - 2:len(code)]) # RIGHT 2
print(code[4:7]) # MID start 5, length 3
print("Amy" + " " + "Reed") # CONCATENATE
# Python counts from 0, a spreadsheet counts from 1.
# That is why MID start 5 is code[4].
Try it: and in SQL
SELECT code,
substr(code, 1, 3) AS year_part,
substr(code, 5, 3) AS initials,
first_name || ' ' || last_name AS full_name
FROM pupils;
-- substr(text, start, length) counts from 1, like MID.
-- The two bars join text, like CONCATENATE.