Did you know that extracting the fourth character of a string in Python and in a spreadsheet starts with different indexing conventions? Python calls the first character index 0, while Excel’s MID function counts the first character as position 1. A student who knows both tools can still get the wrong output by carrying a correct rule into the wrong environment. The solution is to read the function, its arguments and the data type before calculating.
Ministry of Education V3.0 Reference 366 explains the 2027 Singapore–Cambridge SEC G3 Computing K349 spreadsheet text and date functions. Students learn LEFT, RIGHT, MID, LEN, CONCAT, FIND and SEARCH, then move to DAYS, TODAY and NOW. Original practice examples show character extraction, precise match positions, case sensitivity, date differences, copied cell references and errors caused by missing text or incorrectly stored dates. The guide also includes a twelve-stage checking routine and twenty-four worked cases.
This independent eduKateSG resource is not an official MOE, SEAB or Cambridge examination publication. Examples use invented school-style records and fictional dates rather than live personal information. The SEAB-published K349 syllabus, official function list and the candidate’s school instructions are authoritative. Spreadsheet software can display dates differently according to formatting and locale; mathematical claims below assume genuine date values in the specified cells, not arbitrary text that merely looks like a date.
1. Know the official K349 spreadsheet boundaries
Module 3.2.3 of the 2027 K349 syllabus includes extraction from the left, middle and right of text, text length, concatenation and locating text with both case-sensitive and case-insensitive functions. The examinable function list names LEFT, RIGHT, MID, LEN, CONCAT, FIND and SEARCH. Module 3.2.5 covers current date and date/time plus days between dates through TODAY, NOW and DAYS.
The same syllabus also requires students to work with relative, absolute and mixed cell references, and the lab paper uses spreadsheet software. This reference concentrates on interpreting text and dates while showing where reference choice matters. Do not assume that any function available in the current Excel menus is necessarily part of the specific examination function list.
2. A spreadsheet treats text and numbers differently
A cell containing the text ‘K349’ has five visible characters. Extracting three characters from the right produces ‘349’ as text in a typical formula result, not automatically an integer. Whether a downstream operation should interpret it as a number depends on the task. Identifiers often need to retain leading zeros.
Another cell may contain a real date value such as 10 February 2027. A spreadsheet can display it as ’10 Feb 2027′, but the value is more than a string. Date functions depend on valid spreadsheet dates. Text that merely resembles a date is not guaranteed to behave the same way, so data import and formatting assumptions matter.
3. LEFT extracts a specified number of beginning characters
LEFT(text,num_chars) returns characters from the start of a text value. For =LEFT(‘K349’,1), the conceptual result is ‘K’; in actual spreadsheet formula syntax, use double-quoted text such as =LEFT(“K349”,1). For =LEFT(“SEC2027”,3), the result is ‘SEC’.
If the first argument refers to A2, =LEFT(A2,2) uses the current contents of that cell. This makes the formula useful for classifying consistent code prefixes. It does not verify that the entire code is valid. An input that begins with ‘G3’ but contains missing characters afterwards may still pass a simple prefix-extraction test.
=LEFT("SEC2027",3)
Result: SEC
=LEFT("K349",1)
Result: K4. RIGHT counts characters from the end
RIGHT(text,num_chars) extracts the requested number of characters from the final end of a string. =RIGHT(“K349”,3) returns ‘349’. =RIGHT(“G3-2027”,4) returns ‘2027’. The count is a number of characters, not a character position measured from the beginning.
If the text contains unexpected trailing spaces or punctuation, the rightmost characters may not be the ones the student expected. Check the source data instead of assuming the function miscalculated. A robust solution establishes the identifier format and tests enough examples to see whether a fixed-length suffix is appropriate.
5. MID uses one-based positions
MID(text,start_num,num_chars) extracts a specified number of characters beginning at a one-based position. For ‘COMPUTING’, the fourth character is P, the fifth U and the sixth T; =MID(“COMPUTING”,4,3) therefore gives ‘PUT’. A student who starts at zero would extract a different span.
This is a valuable comparison with Python. The Python slice ‘COMPUTING'[3:6] also returns ‘PUT’, because Python index 3 identifies the fourth character and index 6 is an exclusive boundary. The spreadsheet formula instead states the one-based starting position and the number of characters. Keep the two argument conventions distinct.
=MID("COMPUTING",4,3)
Result: PUT
=RIGHT("K349",3)
Result: 3496. LEN measures the number of characters
LEN(text) counts characters in a text value, including ordinary spaces and punctuation. =LEN(“SEC 2027”) is 8: three letters, one space and four digits. =LEN(“G3”) is 2. Do not confuse character count with the numerical value of digits inside the text.
Length checks are useful for validating fixed-width codes, but a correct length alone does not establish correct contents. A six-character string of punctuation may satisfy =LEN(A2)=6 while failing the intended code format. Combine length reasoning with the other requirements of the task, not a vague promise that a string ‘looks long enough’.
7. CONCAT joins values without inserting separators
CONCAT joins its arguments as text. =CONCAT(“G3″,”-“,”2027”) produces ‘G3-2027’. If the hyphen is omitted, the result is ‘G32027’. A student must include spaces, commas and separators deliberately whenever the required output contains them.
The function also supports references to cells containing source values. This helps construct a label from code components without manually retyping every record. It does not automatically ensure the components are meaningful. A malformed source cell can produce a malformed combined identifier even when CONCAT operates exactly as specified.
=CONCAT("G3","-","2027")
Result: G3-2027
=LEN("SEC 2027")
Result: 88. FIND gives a one-based, case-sensitive position
FIND(find_text,within_text) returns the starting position of the first matching substring, counting the first character as position 1. For =FIND(“-“,”G3-2027”), the hyphen is at position 3. Unlike Python find(), which would return index 2 for that same character, the spreadsheet result is one-based.
FIND is case-sensitive. In ‘Data’, the first lowercase ‘a’ is at position 2. FIND(“a”,”Data”) returns 2, while FIND(“A”,”Data”) produces a not-found error because uppercase A is not present. This difference is the intended property of the function, not evidence of random spreadsheet behaviour.
9. SEARCH locates text without case sensitivity
SEARCH(find_text,within_text) locates a substring while ignoring letter case in ordinary matching. In the text ‘Data’, SEARCH(“A”,”Data”) returns 2 because the lowercase a at the second position matches under a case-insensitive search. FIND with the same uppercase search text does not find that character.
Students should distinguish case sensitivity from numerical position. Both FIND and SEARCH return the first matching position using one-based spreadsheet indexing. The fundamental comparison is whether capitalisation can prevent a match. Other capabilities of SEARCH can vary by details of the application; the K349 question’s stated constraints should govern the answer.
=FIND("-","G3-2027")
Result: 3
=FIND("A","Data")
Result: #VALUE! (no case-sensitive match)
=SEARCH("A","Data")
Result: 210. A missing substring is not position zero
When FIND or SEARCH cannot locate the specified substring, ordinary Excel behaviour is an error result such as #VALUE!, not the numeric position 0 or −1. This is different from Python’s string find(), which uses −1 for a missing match and zero for a match at the beginning.
A student who transfers Python’s not-found convention into a spreadsheet formula can produce an incorrect written answer. Begin by identifying the environment and function. If the source text begins with the matching sequence, spreadsheet FIND or SEARCH returns 1. Position 1 is valid, just as index 0 is valid in Python.
11. Text extraction can be composed carefully
Suppose A2 contains the fictional code ‘G3-2027’. =LEFT(A2,2) returns ‘G3’, and =RIGHT(A2,4) returns ‘2027’. A separate formula can use FIND to locate the hyphen. These operations may be useful for a consistent format, but their results do not automatically establish that the entire code has no errors.
A stronger practice task asks students what happens when the code becomes ‘G3-27’ or ‘G3 2027’. A fixed RIGHT extraction might return a different field, while FIND for ‘-‘ might fail on the second variant. This is why parsing requires format assumptions and not just a memorable formula.
12. Relative references change when copied
If a formula in B2 refers to A2, copying it one row down generally changes the relative reference to A3. That is useful when each row contains a separate record. A formula =LEFT(A2,2) can therefore be filled down a column to extract each row’s two-character prefix.
If a formula also depends on a fixed cell containing a target value, absolute or mixed references may be needed. The K349 syllabus explicitly covers these reference types. A copied formula can have perfect function syntax but produce an incorrect result if its references shift to the wrong cells. Always inspect the first and last filled rows.
13. Dates are values, not just display strings
A spreadsheet date has an internal representation that supports date arithmetic, and its display can be formatted in different human-readable ways. Entering what looks like a date as plain text may prevent the intended operation or produce an error. Before calculating, confirm that the relevant cells contain valid date values.
For a fictional activity schedule, A2 might contain 1 February 2027 and B2 might contain 10 February 2027. The task is to calculate elapsed calendar days between them. The appearance of the displayed date is less important than the proper interpretation of both cells as dates.
14. DAYS(end_date,start_date) follows argument order
The DAYS function returns the number of days between the specified dates in end-minus-start order. When B2 is 10 February 2027 and A2 is 1 February 2027, =DAYS(B2,A2) returns 9. Reversing the arguments returns −9. The direction is part of the question.
This function measures a date difference rather than automatically counting both endpoint dates as participating days. An assignment asking for inclusive calendar dates from 1 to 10 February would need an additional interpretation of what is being counted. Do not silently add one when the question asks only for the elapsed difference.
A2: 1 February 2027 (valid date)
B2: 10 February 2027 (valid date)
=DAYS(B2,A2)
Result: 9
=DAYS(A2,B2)
Result: -915. TODAY returns the current date
TODAY() supplies the current date according to the spreadsheet’s calculation environment. It does not require the student to type a particular date as an argument. Because the current date changes over time, an answer that hard-codes one calendar date into an explanation can become outdated.
For a task that compares a given deadline in B2 with the day the workbook is calculated, =DAYS(B2,TODAY()) returns a date difference that may change as days pass. It can be negative after the deadline. Students should describe the formula and sign rather than claiming that the result must always be positive.
16. NOW returns date and time
NOW() supplies the current date and time in the spreadsheet environment. It differs from TODAY(), which represents the current date. Formatting may cause NOW() to appear to show only a date, but the underlying value can include time as well. A display format does not necessarily remove underlying information.
These functions can update when a workbook recalculates; their outputs are not fixed historic records. If an examination asks for a saved timestamp that must never change, a volatile function may not be suitable without further requirements. For the K349 outcome, the key is to identify the function and explain the kind of current information it supplies.
17. Check date signs and assumptions
If an end date is later than a start date, DAYS produces a positive difference; if it is earlier, the result is negative. This gives a quick sense check for deadline calculations. Two identical dates yield zero. The result should be judged against the actual task: overdue days, days remaining and elapsed time are not identical interpretations.
When an imported cell contains text that resembles a date, a formula may behave differently across locales or fail. Good spreadsheet work explicitly validates the input format. A candidate should not claim that a written string ’02/03/2027′ has one guaranteed interpretation without knowing the locale or date convention.
18. A twelve-stage Text–Date–Reference checking method
This independent eduKateSG teaching routine is not an official examination mark scheme. It helps learners avoid confusing text position with date arithmetic or mixing Python conventions into spreadsheet work. Each stage asks which part of the task is established and which assumption still needs verification.
Stage 1 — Name the required output
Is the answer an extracted substring, location, joined label, text length, current date or day difference?
Stage 2 — Inspect the source cell
Read the exact text or confirm that the source is a valid date value, not a similar-looking string.
Stage 3 — Choose the function from the approved list
Use LEFT, RIGHT, MID, LEN, CONCAT, FIND, SEARCH, DAYS, TODAY or NOW as the task requires.
Stage 4 — Recognise one-based positions
For MID, FIND and SEARCH, the first character is position 1, unlike Python’s zero-based indices.
Stage 5 — State start and count separately
MID takes a starting position and a character count, not the ending position used by a Python slice.
Stage 6 — Account for case sensitivity
Use FIND for a case-sensitive match and SEARCH for a case-insensitive one.
Stage 7 — Handle a missing match
Recognise that a failed search ordinarily produces an error, not a valid character position.
Stage 8 — Supply separators deliberately
CONCAT joins components; it does not invent the hyphen, comma or space an identifier requires.
Stage 9 — Check copied references
Know which cell addresses should shift and which must remain fixed when filling a formula across rows.
Stage 10 — Check date direction
DAYS subtracts start from end; reversing the arguments changes the sign.
Stage 11 — Distinguish TODAY from NOW
One supplies the current date; the other includes current date and time.
Stage 12 — Test a malformed input
Try a short code, a missing delimiter or an invalid date and explain what the function can no longer establish.
19. Twenty-four original worked spreadsheet cases
Case 1 — Leftmost character
=LEFT(“K349”,1) returns ‘K’. It extracts a character count from the beginning.
Case 2 — Leftmost prefix
=LEFT(“SEC2027”,3) returns ‘SEC’, the first three characters.
Case 3 — Rightmost code digits
=RIGHT(“K349”,3) returns ‘349’, which may still be a text result.
Case 4 — Year suffix
=RIGHT(“G3-2027”,4) returns ‘2027’. The hyphen remains outside the extracted span.
Case 5 — MID starts at one
=MID(“COMPUTING”,4,3) returns ‘PUT’. Positions 4, 5 and 6 are selected.
Case 6 — MID length one
=MID(“ABC”,2,1) returns ‘B’. The third argument is the count of characters.
Case 7 — Full text length
=LEN(“ABC123”) returns 6, counting each letter and digit.
Case 8 — Count the separating space
=LEN(“SEC 2027”) returns 8 because the space is one character.
Case 9 — Join without punctuation
=CONCAT(“G3″,”2027”) produces ‘G32027’. It does not add a separator.
Case 10 — Join with hyphen
=CONCAT(“G3″,”-“,”2027”) produces ‘G3-2027’.
Case 11 — Locate the first hyphen
=FIND(“-“,”G3-2027”) returns 3, a one-based position.
Case 12 — Case-sensitive successful find
=FIND(“a”,”Data”) returns 2, the first lowercase a.
Case 13 — Case-sensitive missing match
=FIND(“A”,”Data”) returns an error because uppercase A is absent.
Case 14 — Case-insensitive search
=SEARCH(“A”,”Data”) returns 2 even though the source character is lowercase.
Case 15 — Python versus spreadsheet positions
Python ‘Data’.find(‘a’) is 1; spreadsheet FIND(“a”,”Data”) is 2. Both identify the same second character.
Case 16 — A missing substring is not zero
FIND(“z”,”Data”) does not return 0; it gives an error when no match is present.
Case 17 — Simple copied row
A formula =LEFT(A2,2) copied one row down becomes =LEFT(A3,2) with a normal relative reference.
Case 18 — Fixed input cell
When every row should refer to the same configuration cell, a suitable absolute reference can prevent unintended shifting.
Case 19 — Positive day difference
With A2 set to 1 February 2027 and B2 to 10 February 2027, =DAYS(B2,A2) yields 9.
Case 20 — Negative day difference
Reversing the two date arguments in the previous case yields −9.
Case 21 — Same-date difference
=DAYS(A2,A2) gives 0 for a valid date in A2.
Case 22 — The meaning of TODAY
=TODAY() returns the environment’s current date and can update on recalculation.
Case 23 — The meaning of NOW
=NOW() returns current date and time; display formatting can conceal the time part.
Case 24 — Current deadline comparison
=DAYS(B2,TODAY()) produces the signed number of days from the current date to a valid deadline in B2.
20. Complete worked task: a fictional student-event register
Imagine an invented worksheet with column A containing event codes such as ‘G3-2027’, column B containing event dates and column C containing an optional category description. The task is to extract each code’s prefix, identify the year suffix and calculate the number of days between the event date and another specified date. These operations are separate even though they occupy one spreadsheet row.
A useful solution might place =LEFT(A2,2) in D2, =RIGHT(A2,4) in E2 and =DAYS(B2,F2) in G2, where F2 is a genuine comparison date. Filling the formulas down requires checking relative references carefully. If the comparison date is held in one fixed configuration cell instead, the relevant reference should be anchored. The learner must also decide what to do when a code is shorter than expected or an entered date is not a date value.
21. Diagnose the first wrong assumption
A candidate uses =MID(A2,3,5) intending to extract positions three through five. The third argument is a count of five characters, not an ending index, so more text may be returned than expected. The correction comes from reading the function signature rather than experimenting with random numbers.
Another candidate uses FIND and expects case-insensitive behaviour. The result differs from SEARCH because the functions make different case assumptions. A third candidate uses DAYS(start,end) and obtains a negative number; the arithmetic is not necessarily wrong, but the argument order is reversed for the intended days-remaining question. Each error has a distinct cause.
22. Practical exam readiness: check actual cells
A screenshot of a correct-looking result is not proof that the underlying formula is valid. A value might have been typed manually, a reference may point to the wrong row, or the result may be a text string when a valid date was required. During practice, students should inspect the formula bar and test a second row that differs meaningfully from the first.
A useful lab discipline is to begin with a small set of invented rows, inspect the first computed result, fill formulas carefully, then test boundary cases and save the specified workbook. Check the actual saved file and required location. This connects the spreadsheet unit to the wider software engineering habits of specification, testing and verification.
23. Questions for parents and tutors
Question one: how does =MID(“SINGAPORE”,2,3) differ conceptually from Python slicing? It uses a one-based start and a character count. Its result is ‘ING’. Question two: why can a correct RIGHT formula return the wrong logical field? Because the source identifier format might be inconsistent or include unexpected trailing characters.
Question three: when a formula moves from B2 to B3, what may change? Relative references. Question four: which function distinguishes ‘A’ and ‘a’? FIND; SEARCH is case-insensitive for its ordinary text matching. Question five: which date function includes time? NOW. Encourage students to explain the rule and then supply a counterexample.
24. Frequently asked questions
Is MID zero-based like Python?
No. Excel MID uses one-based starting positions and a character count, while Python uses zero-based slice boundaries.
Does RIGHT convert extracted digits into integers?
Not by itself. It extracts text; a later task may require a separate numeric interpretation.
Does LEN count spaces?
Yes. Spaces and punctuation are characters in the source string.
Does CONCAT insert punctuation?
No. Add the desired space, hyphen or comma explicitly.
What is the difference between FIND and SEARCH?
FIND is case-sensitive; SEARCH is case-insensitive for ordinary matching.
Does a missing text match return −1?
Not in ordinary Excel FIND or SEARCH. A missing match produces an error such as #VALUE!.
Does DAYS count both endpoint dates inclusively?
It returns the difference between two dates; inclusive day-count tasks require separate interpretation.
Why can DAYS be negative?
Its argument order is end date followed by start date. Reversing the direction changes the sign.
What does TODAY return?
The current date in the spreadsheet’s calculation environment.
What does NOW return?
The current date and time, which may be displayed differently depending on cell formatting.
Why can copying a formula cause errors?
Relative references may shift to the wrong cells if the task actually requires a fixed reference.
How should I revise?
Practise text positions, exact function arguments, case rules, date direction and one copied-formula scenario with independent checks.
Extended practice: spreadsheet text, dates and copied formulas
Text functions and date functions become more challenging when they share one worksheet. The following exercises illustrate how an apparently correct formula can produce an unsuitable result because its source value, reference or convention was wrong. They use fictional records and fixed historical or future dates so students can calculate without any live personal information.
A full code extraction with a documented format
Imagine A2 contains ‘G3-ENG-2027’. =LEFT(A2,2) returns ‘G3’, =MID(A2,4,3) returns ‘ENG’, and =RIGHT(A2,4) returns ‘2027’. The positions in MID are one-based, with the first E at position 4. The operation’s third argument is the number of characters to extract, so it is 3 for the three-letter subject code. A formula that copied Python’s zero-based index 3 as the same kind of starting position would mistakenly begin with the hyphen.
Now replace the record with ‘G3-EN-2027’. A fixed MID extraction can include the hyphen before the year because the original assumption about subject length has failed. The calculation was not necessarily performed incorrectly; the source violates the expected format. A proper check can use LEN and the location of separator characters to decide whether the code meets the specification before treating the extracted fields as meaningful.
The same text, two different search rules
Take ‘DataAnalysis’. FIND(‘A’,’DataAnalysis’) returns 5 because uppercase A appears at the start of ‘Analysis’ (positions D1, a2, t3, a4, A5). SEARCH(‘A’,’DataAnalysis’) returns 2 because it can match the earlier lowercase a under case-insensitive searching. Both positions are correct for their respective rules. This is a particularly useful example because the two functions return different successful numbers rather than one success and one error.
Another quick check uses ‘CODE’ as the source. FIND(‘code’,’CODE’) ordinarily returns #VALUE! because the case-sensitive substring is absent. SEARCH(‘code’,’CODE’) returns 1. A student should not memorise that SEARCH always returns 1 or FIND always fails; the outcome depends on the actual characters. Name the matching rule before comparing positions.
A deadline report with stable and changing dates
Suppose B2 holds the valid date 10 February 2027 and C2 holds the valid date 1 February 2027. The expression =DAYS(B2,C2) returns 9, while =DAYS(C2,B2) returns −9. The sign reveals which date is treated as the end. A task asking for days remaining would also need to specify which date is the reference point.
Replacing C2 with TODAY() makes the answer dynamic. It can be positive when the deadline is ahead, zero on the deadline date and negative afterwards, depending on the dates involved. NOW() includes time information, which can be important when interpreting a timestamp. Neither function should be used to claim that a displayed date has a fixed answer for all future openings of the workbook.
What happens when formulas are filled down
Imagine A2 through A6 contain five fictional event identifiers. The formula =LEFT(A2,2) in D2 refers to the first identifier. When copied down through D6 under ordinary relative-reference rules, the input reference advances to A3, A4, A5 and A6. That is desirable because every row should process its corresponding event.
Now imagine the task compares each event’s date to one fixed reference date in F1. A formula that refers relatively to F1 may shift when copied. Using an appropriate absolute reference, such as $F$1, preserves the intended fixed cell. This technique is distinct from the LEFT or DAYS function itself: the functions may be correctly written while the cell addresses produce incorrect results.
A test set that checks more than the happy path
Use four invented code values: ‘G3-ENG-2027’ (normal), ‘G3-EN-2027’ (short subject field), ‘G3ENG2027’ (no separators) and ‘g3-ENG-2027’ (different case). For each, ask what LEFT, MID, RIGHT, FIND and SEARCH return and whether the output represents a valid record. The point is not simply to obtain values; it is to know when the input assumptions fail.
Pair these with three date tests: two identical valid dates, a later end date and an earlier end date. The expected DAYS results respectively are zero, positive and negative. Finally, test one cell that is formatted as text instead of a genuine date and explain why its interpretation requires verification. This small suite provides more confidence than checking one pretty screenshot of a successful formula.
A tutor-led explanation test
Ask the learner to explain the difference between Python ‘ABC'[1] and spreadsheet =MID(‘ABC’,2,1) using proper spreadsheet double-quoted text. Both select the second character B, but the numerical positions and calling conventions differ. Then ask why Python find(‘B’) returns index 1 while spreadsheet FIND for B returns position 2. The learner should articulate a general convention rather than just report the answer.
Next ask whether =LEN(‘A B’) measures words or characters, whether =CONCAT(‘A’,’B’) inserts a space, and why =DAYS(end,start) can be negative. The correct explanations link the function name to its exact argument rules. When a student can supply a contrasting case on their own, they are beginning to understand the spreadsheet as a system rather than a collection of magic formulas.
Official 2027 syllabus and connected eduKateSG learning
The authority is the 2027 SEC G3 Computing K349 syllabus, especially Module 3.2.3 and 3.2.5 and the examinable function list. Related eduKateSG material: Reference 311 — Spreadsheet Formulas and Lookups, Reference 364 — Python String Operations, Reference 361 — Software Engineering, the MOE V3.0 reference library and the immutable Clementi teaching reference.
Reviewed 29 October 2026, Singapore. All event registers, labels and deadlines are fictional classroom examples. The durable habit is to identify what the function measures, follow its indexing and argument conventions, confirm the source data type, and test the saved result against the specification.
MOE V3.0 sequence navigation
Previous: Reference 365 — Python Dictionaries · MOE V3.0 library.