TIN HỌC VĂN PHÒNG CƠ BẢN PHẦN 1 - HÀM CƠ BẢN EXCEL

CÁC CẤU TRÚC HÀM EXCEL CƠ BẢN - PHẦN 1

- Nguồn: Internet

--------------------------------------------------------------------------------------------------------------

PHẦN I: HÀM LEFT, RIGHT, MID, LEN, FIND VÀ CONCATENATE - với các ví dụ và tính huống áp dụn cụ thể/ LEFT, RIGHT, MID, LEN, FIND, and CONCATENATE — with examples and use cases.

🔹 𝗟𝗘𝗙𝗧(𝘁𝗲𝘅𝘁, [𝗻𝘂𝗺_𝗰𝗵𝗮𝗿𝘀])
𝗣𝘂𝗿𝗽𝗼𝘀𝗲: Extracts characters from the beginning (left side) of a text string.

𝑺𝒚𝒏𝒕𝒂𝒙:
=LEFT(text, num_chars)
𝙀𝙭𝙖𝙢𝙥𝙡𝙚:
=LEFT("ExcelMaster", 5) ➝ "Excel"
𝐔𝐬𝐞 𝐂𝐚𝐬𝐞: Getting first name from a full name like "Mohan Nayak" ➝
=LEFT(A2, FIND(" ", A2)-1)

🔹 𝗥𝗜𝗚𝗛𝗧(𝘁𝗲𝘅𝘁, [𝗻𝘂𝗺_𝗰𝗵𝗮𝗿𝘀])
𝗣𝘂𝗿𝗽𝗼𝘀𝗲: Extracts characters from the end (right side) of a string.

𝑺𝒚𝒏𝒕𝒂𝒙:
=RIGHT(text, num_chars)
𝙀𝙭𝙖𝙢𝙥𝙡𝙚:
=RIGHT("ExcelMaster", 6) ➝ "Master"

𝐔𝐬𝐞 𝐂𝐚𝐬𝐞: Getting last 4 digits of a phone number: =RIGHT(A2, 4)

🔹 𝗠𝗜𝗗(𝘁𝗲𝘅𝘁, 𝘀𝘁𝗮𝗿𝘁_𝗻𝘂𝗺, 𝗻𝘂𝗺_𝗰𝗵𝗮𝗿𝘀)
𝗣𝘂𝗿𝗽𝗼𝘀𝗲: Extracts characters from the middle of a text string.

𝑺𝒚𝒏𝒕𝒂𝒙:
=MID(text, start_num, num_chars)
𝙀𝙭𝙖𝙢𝙥𝙡𝙚:
=MID("ExcelMaster", 6, 6) ➝ "Master"

𝐔𝐬𝐞 𝐂𝐚𝐬𝐞: Extracting area code from "IND-500001" ➝ =MID(A2, 5, 6)

🔹 𝗟𝗘𝗡(𝘁𝗲𝘅𝘁)
𝗣𝘂𝗿𝗽𝗼𝘀𝗲: Returns the number of characters in a text string, including spaces.

𝑺𝒚𝒏𝒕𝒂𝒙:
=LEN(text)
𝙀𝙭𝙖𝙢𝙥𝙡𝙚:
=LEN("Excel Master") ➝ 12

𝐔𝐬𝐞 𝐂𝐚𝐬𝐞: Check if an input meets a required length (like 10-digit phone number).

🔹 𝗙𝗜𝗡𝗗(𝗳𝗶𝗻𝗱_𝘁𝗲𝘅𝘁, 𝘄𝗶𝘁𝗵𝗶𝗻_𝘁𝗲𝘅𝘁, [𝘀𝘁𝗮𝗿𝘁_𝗻𝘂𝗺])
𝗣𝘂𝗿𝗽𝗼𝘀𝗲: Returns the position of a character or substring within another text string (case-sensitive).

𝑺𝒚𝒏𝒕𝒂𝒙:
=FIND(find_text, within_text, [start_num])
𝙀𝙭𝙖𝙢𝙥𝙡𝙚:
=FIND("M", "ExcelMaster") ➝ 6

𝐔𝐬𝐞 𝐂𝐚𝐬𝐞: Find the position of space in "Mohan Nayak" ➝ =FIND(" ", A2)

🔹 𝗖𝗢𝗡𝗖𝗔𝗧𝗘𝗡𝗔𝗧𝗘(𝘁𝗲𝘅𝘁𝟭, 𝘁𝗲𝘅𝘁𝟮, ...) 𝗼𝗿 𝗧𝗘𝗫𝗧𝗝𝗢𝗜𝗡 CONCATENATE is now replaced by TEXTJOIN and &, but still works.
𝗣𝘂𝗿𝗽𝗼𝘀𝗲: Joins multiple text strings into one.

𝑺𝒚𝒏𝒕𝒂𝒙:
=CONCATENATE(text1, text2, ...)
OR
=A2 & " " & B2
OR
=TEXTJOIN(" ", TRUE, A2, B2)

𝙀𝙭𝙖𝙢𝙥𝙡𝙚:
=CONCATENATE("Excel", "Master") ➝ "ExcelMaster"
="Excel" & " " & "Master" ➝ "Excel Master"

PHẦN II TÌNH HUỐNG ÁP DỤNG /𝐔𝐬𝐞 𝐂𝐚𝐬𝐞:

Combine first and last names into full name.

✅ 𝑩𝒐𝒏𝒖𝒔 𝑪𝒐𝒎𝒃𝒊𝒏𝒆𝒅 𝑬𝒙𝒂𝒎𝒑𝒍𝒆: From cell A2 = Mohan Nayak
You want to get:

➙ First Name: =LEFT(A2, FIND(" ", A2)-1) ➝ Mohan
➙ Last Name: =RIGHT(A2, LEN(A2)-FIND(" ", A2)) ➝ Nayak
➙ Full Name Upper Case: =UPPER(A2) ➝ MOHAN NAYAK
➙ Full Name Rebuilt: =CONCATENATE(LEFT(A2, FIND(" ", A2)-1), " ", RIGHT(A2, LEN(A2)-FIND(" ", A2)))