5.13 Extracting Parts of Text
Before
| Order Note |
|---|
| Order BLK-260314-001 delivered in 9 mins |
| Order AMN-260314-006 delivered in 10 mins |
| Order BLK-260314-005 delivered in 18 mins |
About this data: Teaching sample for a fictional shop “FreshGo Mart”. Figures and names (e.g. Sana Sheikh) are made up for this lesson on extracting parts of text.
After
| Order ID | Mins |
|---|---|
| BLK-260314-001 | 9 |
| AMN-260314-006 | 10 |
| BLK-260314-005 | 18 |
About this data: Demo table only — not real customer or company data. Built to practise extracting parts of text.
Steps in Excel
- Order ID (always 14 characters, starting after "Order "):
=MID(A2,7,14). - Minutes between "in " and " mins":
=VALUE(MID(A2,FIND("in ",A2)+3,FIND(" mins",A2)-FIND("in ",A2)-3)). - Microsoft 365 / Excel 2024:
=TEXTBEFORE(TEXTAFTER(A2,"Order ")," ")and=--TEXTBEFORE(TEXTAFTER(A2,"in ")," mins"). - Or Flash Fill with two examples.
Ravindra Bagale's Tip
Many students hard-code the positions (7, 14), and when one note is written differently the answer goes wrong. Hard-code only if you are sure the length is fixed; otherwise find the "markers" with FIND or TEXTBEFORE/TEXTAFTER. Turn the extracted number into a real number with VALUE – otherwise you can't average it.
Ravindra Bagale's Tip – मराठी
बरेच students position (7, 14) hard-code करतात आणि एखादी note वेगळी लिहिली की answer चुकतं. Length fixed आहे याची खात्री असेल तरच hard-code करा; नाहीतर FIND किंवा TEXTBEFORE/TEXTAFTER ने "markers" शोधा. Extract केलेला number VALUE ने number बनवा – नाहीतर average होत नाही.
Ravindra Bagale's Tip – हिंदी
बहुत से students position (7, 14) hard-code कर देते हैं और कोई note अलग तरह लिखा हो तो answer गलत आता है. Length fixed होने का पक्का भरोसा हो तभी hard-code करो; वरना FIND या TEXTBEFORE/TEXTAFTER से "markers" ढूँढो. Extract किए number को VALUE से number बनाओ – वरना average नहीं निकलेगा.
Practice task
From notes like "Rider Raja delivered BLK-260314-007 at 19:51", extract the rider name, the order ID and the time (as a real time value).
Loading practice tasks...
- First word (Easy): Get the first 5 characters of A2.
- Order number (Easy): Get the order number from A2 (start 7, length 9).
- Last word (Easy): Get the last 3 characters of A3.
- Length of the message (Easy): How many characters are in A4?
- Order number as a number (Medium): Get the order number from A3 as a number.
- Position of " in " (Medium): Find the position of " in " in A2.
- Text after "in " (Medium): Get the text after "in " in A2.
- Order number with TEXTBEFORE (Medium): Get the text before " delivered" in A4, after "ORDER ".
- Minutes as a number (Hard): Get the minutes from A2 as a number.
- Minutes for a 1-digit order (Hard): Get the minutes from A3 as a number.
- Late delivery? (Hard): Is the order in A5 late? (TRUE or FALSE)
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
