3.6 Text Functions: LEFT, RIGHT, MID, LEN, FIND and SEARCH
LEFT, RIGHT and FIND with example — in 30 seconds
What: LEFT and RIGHT take characters from the start or end of a text. FIND tells you the position of a character.
Why: The platform is hidden inside the Order ID. Pulling it out lets you count QuickKart vs ZipBasket.
Tiny example: From "ZBK-1003", =LEFT(A2,3) gives ZBK and =RIGHT(A2,4) gives 1003.
LEFT, RIGHT and FIND with example — 30 सेकंदात
काय: LEFT आणि RIGHT text च्या सुरुवातीचे किंवा शेवटचे characters काढतात. FIND एखादा character कितव्या जागेवर आहे ते सांगतो.
का: Platform Order ID मध्येच लपलेला आहे. तो बाहेर काढला की QuickKart आणि ZipBasket मोजता येतात.
छोटं उदाहरण: "ZBK-1003" मधून =LEFT(A2,3) ZBK देतो आणि =RIGHT(A2,4) 1003 देतो.
LEFT, RIGHT and FIND with example — 30 सेकंड में
क्या: LEFT और RIGHT text के शुरू या आख़िर के characters निकालते हैं. FIND बताता है कि कोई character किस position पर है.
क्यों: Platform Order ID के अंदर छुपा है. उसे निकालने पर QuickKart और ZipBasket गिन सकते हैं.
छोटा उदाहरण: "ZBK-1003" से =LEFT(A2,3) ZBK देता है और =RIGHT(A2,4) 1003 देता है.
First look at the data (before → after):
Expected result — Parts of the ID
| A: Order ID | B: Prefix | C: Number | D: Position of "-" |
|---|---|---|---|
| QKT-1001 | QKT | 1001 | 4 |
| ZBK-1003 | ZBK | 1003 | 4 |
| QKT-1007 | QKT | 1007 | 4 |
About this data: Teaching sample for a fictional shop “BrightLedger Finance”. Figures and names (e.g. Rohan Patil) are made up for this lesson on text functions left right mid len find and.
Before table and formula as text
Before — Order IDs
| A: Order ID |
|---|
| QKT-1001 |
| ZBK-1003 |
| QKT-1007 |
About this data: Demo table only — not real customer or company data. Built to practise text functions left right mid len find and.
Formula / click path
B2: =LEFT(A2,3)
C2: =RIGHT(A2,4)
D2: =FIND("-",A2)
Do it step by step:
- In B2, type
=LEFT(A2,3). - In C2, type
=RIGHT(A2,4). - In D2, type
=FIND("-",A2). - Select B2:D2 and double-click the fill handle.
- Note: RIGHT returns text "1003". Wrap it in VALUE if you need a number.
स्टेप बाय स्टेप करून बघा:
- B2 मध्ये
=LEFT(A2,3)type करा. - C2 मध्ये
=RIGHT(A2,4)type करा. - D2 मध्ये
=FIND("-",A2)type करा. - B2:D2 select करा आणि fill handle वर double-click करा.
- लक्षात ठेवा: RIGHT text "1003" देतो. Number हवा असेल तर VALUE मध्ये wrap करा.
स्टेप बाय स्टेप करके देखें:
- B2 में
=LEFT(A2,3)type करें. - C2 में
=RIGHT(A2,4)type करें. - D2 में
=FIND("-",A2)type करें. - B2:D2 select करें और fill handle पर double-click करें.
- ध्यान दें: RIGHT text "1003" देता है. Number चाहिए तो VALUE में wrap करें.
| Function | Syntax | Example | Result |
|---|---|---|---|
| LEFT | LEFT(text, [num_chars]) |
=LEFT("QKT-1001",3) |
QKT |
| RIGHT | RIGHT(text, [num_chars]) |
=RIGHT("QKT-1001",4) |
1001 (text) |
| MID | MID(text, start, num_chars) |
=MID("PUN-Kothrud-01",5,7) |
Kothrud |
| LEN | LEN(text) |
=LEN("Kothrud") |
7 |
| FIND | FIND(find_text, within_text, [start]) |
=FIND("-","PUN-Kothrud-01") |
4 |
| SEARCH | SEARCH(find_text, within_text, [start]) |
=SEARCH("k","PUN-Kothrud-01") |
5 |
About this data: Made-up rows so you can copy the pattern for text functions left right mid len find and. Currencies may use ₹, £, € or $.
FIND is case-sensitive and has no wildcards; SEARCH ignores case and allows * and ?.
Worked example – extract the area from a store code of any length. Code in A2: NSK-College Road-01.
=MID(A2, FIND("-",A2)+1, FIND("-",A2,FIND("-",A2)+1) - FIND("-",A2) - 1)
First FIND = 4, second FIND (starting after 4) = 17. MID starts at 5 and takes 17 − 4 − 1 = 12 characters → College Road. (In Microsoft 365 the same is simply =TEXTBEFORE(TEXTAFTER(A2,"-"),"-") – see 3.9.)
Ravindra Bagale's Tip
The result of LEFT/RIGHT/MID is always text – =RIGHT("QKT-1001",4) shows 1001, but you can't SUM it. If you need a number, use =VALUE(RIGHT(A2,4)) or --RIGHT(A2,4). And if you take a fixed number of characters (for example 7), names of different lengths go wrong – find the position with FIND.
Ravindra Bagale's Tip – मराठी
LEFT/RIGHT/MID चा result नेहमी text असतो – =RIGHT("QKT-1001",4) हे 1001 दिसतं पण त्याची SUM होत नाही. Number हवा असेल तर =VALUE(RIGHT(A2,4)) किंवा --RIGHT(A2,4) वापरा. आणि ठराविक संख्येचे characters (उदाहरणार्थ 7) घेतले तर वेगवेगळ्या लांबीच्या names ला चुकतं – FIND ने position शोधा.
Ravindra Bagale's Tip – हिंदी
LEFT/RIGHT/MID का result हमेशा text होता है – =RIGHT("QKT-1001",4) 1001 दिखाता है पर उसका SUM नहीं होता. Number चाहिए तो =VALUE(RIGHT(A2,4)) या --RIGHT(A2,4) इस्तेमाल करो. और तय गिनती के characters (जैसे 7) लिए तो अलग-अलग लंबाई वाले names गलत हो जाते हैं – FIND से position ढूँढो.
Practice task
From Order IDs like QKT-260314-001 extract the platform (QKT/ZBK), the date part (260314) and the serial number as a real number.
Loading practice tasks...
- Platform code (Easy): Get the first 3 characters of Order ID QKT-1001.
- Order number (Easy): Get the last 4 characters of Order ID ZBK-1003.
- IT: ticket type (Easy): On the IT sheet, get the first 3 characters of Ticket ID INC-20451-P1.
- IT: length of the ID (Easy): On the IT sheet, count the characters in Ticket ID INC-20451-P1.
- Where is the dash? (Medium): Find the position of the "-" character inside Order ID QKT-1001.
- Length of a city name (Medium): Count the characters in the City of order ZBK-1008 (Sambhaji Nagar).
- IT: ticket number (Medium): On the IT sheet, get 5 characters from Ticket ID REQ-30017-P2, starting at position 5.
- Middle part (Hard): Get 4 characters from Order ID QKT-1007, starting at position 5.
- Text before the dash (Hard): Get the text before "-" in Order ID ZBK-1003, using LEFT and FIND.
- IT: user name (Hard): On the IT sheet, get the user name before "@" in the Raised by (email) of ticket INC-20451-P1, using LEFT and FIND.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
