3.6 Text Functions: LEFT, RIGHT, MID, LEN, FIND and SEARCH
| Function | Syntax | Example | Result |
|---|---|---|---|
| LEFT | LEFT(text, [num_chars]) |
=LEFT("BLK-1001",3) |
BLK |
| RIGHT | RIGHT(text, [num_chars]) |
=RIGHT("BLK-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 |
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("BLK-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("BLK-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("BLK-1001",4) 1001 दिखाता है पर उसका SUM नहीं होता. Number चाहिए तो =VALUE(RIGHT(A2,4)) या --RIGHT(A2,4) इस्तेमाल करो. और तय गिनती के characters (जैसे 7) लिए तो अलग-अलग लंबाई वाले names गलत हो जाते हैं – FIND से position ढूँढो.
Practice task
From Order IDs like BLK-260314-001 extract the platform (BLK/AMN), the date part (260314) and the serial number as a real number.