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
LEFT/RIGHT/MID cha result nehmi text asto – =RIGHT("BLK-1001",4) ha 1001 disto pan tyachi SUM hot nahi. Number hava asel tar =VALUE(RIGHT(A2,4)) kiwa --RIGHT(A2,4) vapra. Ani fixed number of characters (udaharanartha 7) ghetla tar vegvegalya lambichya names la chukta – FIND ne position shodha.
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.