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 |
After
| Order ID | Mins |
|---|---|
| BLK-260314-001 | 9 |
| AMN-260314-006 | 10 |
| BLK-260314-005 | 18 |
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).