Trong công việc quản lý dữ liệu, chúng ta thường gặp tình huống cần lấy ký tự đầu và ký tự cuối của một chuỗi văn bản, rồi kết hợp lại để tạo ra một phần mới của dữ liệu, hoặc để kiểm tra tính hợp lệ của các trường. Đối với những người sử dụng Excel, việc thực hiện thao tác này có thể được thực hiện một cách nhanh chóng và chính xác nhờ vào một số hàm mạnh mẽ như LEFT, RIGHT, MID, LEN, CONCATENATE (hoặc dấu &) và một số công thức phức tạp hơn như TEXTJOIN, IFS hoặc LET.
Bài viết này sẽ cung cấp một hướng dẫn chi tiết, từ cơ bản đến nâng cao, giúp bạn hiểu rõ cách “nối ký tự đầu và cuối” trong Excel, cùng với các ví dụ thực tế và mẹo tối ưu hiệu suất. Ngoài ra, chúng ta cũng sẽ xem xét những tình huống đặc biệt như chuỗi rỗng, chuỗi có ký tự đặc biệt, và cách xử lý lỗi khi dữ liệu không hợp lệ.
Có thể bạn quan tâm: Nhập Nhiều Ô Thành Một Ô Trong Excel: Hướng Dẫn Chi Tiết Và Mẹo Thực Hành
1. Giới thiệu vấn đề và ứng dụng thực tiễn
1.1. Mục đích sử dụng
- Tạo mã chuẩn: Ví dụ, bạn muốn tạo mã SKU bắt đầu bằng ký tự đầu tiên của tên sản phẩm và kết thúc bằng ký tự cuối cùng của danh mục.
- Kiểm tra dữ liệu: Kiểm tra tính hợp lệ của một chuỗi bằng cách so sánh ký tự đầu/ cuối với một mẫu dữ liệu chuẩn.
- Tách dữ liệu: Trong trường hợp dữ liệu đã được gộp, bạn cần tách ra ký tự đầu/ cuối để phân loại.
- Định dạng dữ liệu: Thêm ký tự đặc biệt vào đầu/ cuối chuỗi để chuẩn hóa dữ liệu đầu vào.
1.2. Thách thức phổ biến
- Chuỗi rỗng: Khi ô trống, hàm
LEFThoặcRIGHTsẽ trả về lỗi hoặc giá trị không mong muốn. - Chuỗi quá ngắn: Nếu độ dài chuỗi nhỏ hơn số ký tự muốn lấy, hàm sẽ lỗi.
- Ký tự đặc biệt, dấu cách: Đôi khi dữ liệu có dấu cách đầu/ cuối mà cần loại bỏ.
- Hiệu suất: Khi làm việc với hàng triệu dòng, công thức phức tạp có thể làm giảm tốc độ tính toán.
Có thể bạn quan tâm: Nhảy Số Thứ Tự Tự Động Trong Excel: Hướng Dẫn Chi Tiết Và Các Mẹo Tối Ưu
2. Cơ bản: Hàm LEFT, RIGHT và CONCATENATE
2.1. Hàm LEFT(text, num_chars)
- Mô tả: Trả về
num_charský tự từ đầu chuỗitext. - Cú pháp:
LEFT("BacNghiep", 1)→"B" - Lưu ý: Nếu
num_chars> độ dài chuỗi, hàm trả về toàn bộ chuỗi.
2.2. Hàm RIGHT(text, num_chars)
- Mô tả: Trả về
num_charský tự từ cuối chuỗitext. - Cú pháp:
RIGHT("BacNghiep", 1)→"p" - Lưu ý: Tương tự như
LEFT, nếunum_chars> độ dài chuỗi, trả về toàn bộ chuỗi.
2.3. Nối các ký tự với CONCATENATE hoặc dấu &
- Ví dụ:
excel =CONCATENATE(LEFT(A1,1), RIGHT(A1,1))
Hoặc:excel =LEFT(A1,1) & RIGHT(A1,1) - Kết quả: Nếu
A1chứa"BacNghiep", công thức trả về"Bp".
2.4. Xử lý chuỗi rỗng
Để tránh lỗi khi ô trống, ta có thể dùng IF:
=IF(A1="","",LEFT(A1,1) & RIGHT(A1,1))
Có thể bạn quan tâm: Cách Nhân Một Cột Với Một Số Trong Excel – Hướng Dẫn Chi Tiết Từng Bước
3. Nâng cao: Sử dụng LEN, MID, TRIM và TEXTJOIN
3.1. Kiểm tra độ dài chuỗi
Hàm LEN(text) trả về số ký tự của text. Khi kết hợp với IF ta có thể kiểm tra độ dài trước khi lấy ký tự.
=IF(LEN(A1)<2,"",LEFT(A1,1)&RIGHT(A1,1))
Nếu độ dài < 2, ta trả về chuỗi rỗng.
3.2. Loại bỏ khoảng trắng đầu cuối
Sử dụng TRIM(text) để loại bỏ khoảng trắng không cần thiết.
=IF(TRIM(A1)="","",LEFT(TRIM(A1),1)&RIGHT(TRIM(A1),1))
3.3. Nối nhiều ký tự đầu/ cuối
Giả sử bạn muốn lấy 2 ký tự đầu và 3 ký tự cuối:
=LEFT(A1,2)&RIGHT(A1,3)
3.4. Sử dụng TEXTJOIN cho chuỗi dài
Nếu muốn nối nhiều ký tự đầu/ cuối của nhiều ô, TEXTJOIN giúp giảm độ phức tạp:
=TEXTJOIN("",TRUE,LEFT(A1,1),RIGHT(A1,1),LEFT(A2,1),RIGHT(A2,1))
TRUE bỏ qua các ô rỗng.
4. Công thức tối ưu với LET (Excel 365/2026)
LET cho phép khai báo biến, giúp công thức ngắn gọn và nhanh hơn khi tính nhiều lần.
=LET( txt, TRIM(A1), len, LEN(txt), IF(len<2,"",LEFT(txt,1)&RIGHT(txt,1))
)
txtlà chuỗi đã trim.lenlà độ dài chuỗi.- Chỉ khi
len>=2mới thực hiệnLEFTvàRIGHT.
4.1. Ví dụ nâng cao: Kiểm tra ký tự đầu/ cuối có phải là chữ số
Có thể bạn quan tâm: Nối 2 Ô Lại Với Nhau Trong Excel: Hướng Dẫn Chi Tiết Và Các Công Thức Thực Tiễn
=LET( txt, TRIM(A1), first, LEFT(txt,1), last, RIGHT(txt,1), IF(AND(ISNUMBER(--first),ISNUMBER(--last)),first&last,"")
)
Sử dụng -- để chuyển ký tự thành số, ISNUMBER kiểm tra.
5. Xử lý lỗi bằng IFERROR
Khi dữ liệu không hợp lệ, LEFT/RIGHT có thể trả về lỗi. Sử dụng IFERROR để tránh lỗi hiển thị.
=IFERROR(LEFT(A1,1)&RIGHT(A1,1),"Lỗi")
6. Tối ưu hiệu suất với công thức mảng và hàm FILTER
6.1. Công thức mảng (Dynamic Array)
Trong Excel 365, bạn có thể áp dụng công thức cho toàn bộ cột mà không cần sao chép:
=LET( txt, TRIM(A2:A1000), IF(LEN(txt)>=2, LEFT(txt,1)&RIGHT(txt,1),"")
)
Kết quả sẽ là mảng chứa kết quả cho từng ô trong phạm vi.
6.2. Kết hợp với FILTER để lấy dữ liệu chỉ có ký tự đầu/ cuối là chữ cái
=FILTER( LEFT(A2:A1000,1)&RIGHT(A2:A1000,1), ISNUMBER(--LEFT(A2:A1000,1))ISNUMBER(--RIGHT(A2:A1000,1))=0
)
Đây là một ví dụ phức tạp, nhưng minh họa cách lọc dữ liệu dựa trên điều kiện.
7. Ứng dụng thực tế: Ví dụ chi tiết
7.1. Tạo mã định danh dựa trên tên và danh mục
Giả sử bảng dữ liệu:
| A (Tên sản phẩm) | B (Danh mục) |
|——————-|————–|
| Tivi 55″ | Điện tử |
| Giường ngủ | Nội thất |
| Laptop | Điện tử |
Bạn muốn tạo mã định danh: ký tự đầu của tên + ký tự cuối của danh mục + số thứ tự.
Công thức:
=LEFT(A2,1)&RIGHT(B2,1)&ROW()-1
Kết quả:
– A2 → "T", B2 → "đ", mã → "Tđ1"
– A3 → "G", B3 → "t", mã → "Gt2"
– A4 → "L", B4 → "đ", mã → "Lđ3"
Lưu ý: Nếu danh mục có dấu cách hoặc ký tự đặc biệt, dùng
TRIMvàSUBSTITUTEđể chuẩn hoá.
7.2. Kiểm tra dữ liệu hợp lệ
Bạn có một cột chứa số điện thoại. Bạn muốn xác nhận rằng số điện thoại bắt đầu bằng ký tự + và kết thúc bằng ký tự 0. Công thức:
=IF(AND(LEFT(A2,1)="+",RIGHT(A2,1)="0"),"Đúng","Sai")
8. Các lỗi thường gặp và cách khắc phục
| Lỗi | Nguyên nhân | Giải pháp |
|---|---|---|
#VALUE! | Chuỗi rỗng hoặc quá ngắn | Sử dụng IFERROR hoặc kiểm tra độ dài bằng LEN |
| Ký tự không đúng | Chuỗi có dấu cách đầu/ cuối | Dùng TRIM trước khi lấy ký tự |
| Kết quả sai lệch | Dữ liệu chứa ký tự đặc biệt (như dấu gạch nối) | Sử dụng SUBSTITUTE để loại bỏ hoặc thay thế |
| Hiệu suất chậm | Công thức phức tạp trên hàng triệu dòng | Sử dụng LET, công thức mảng hoặc chuyển dữ liệu sang Power Query |
9. Mẹo nâng cao: Kết hợp với Power Query
Nếu bạn cần xử lý dữ liệu lớn, Power Query là công cụ mạnh mẽ. Dưới đây là ví dụ ngắn gọn:
- Chọn dữ liệu → Data → From Table/Range.
- Trong Power Query, thêm Custom Column:
m Text.Start(Tên,1) & Text.End(Tên,1) - Áp dụng và tải dữ liệu trở lại Excel.
Power Query thực thi nhanh hơn Excel công thức khi làm việc với hàng trăm nghìn dòng.
10. Kết luận
Việc “nối ký tự đầu và cuối trong Excel” là một thao tác phổ biến nhưng lại có nhiều cách tiếp cận khác nhau, từ đơn giản nhất với LEFT, RIGHT và & đến phức tạp hơn với LET, TEXTJOIN, Power Query và công thức mảng. Bạn có thể chọn phương pháp phù hợp với nhu cầu và kích thước dữ liệu của mình.
- Bắt đầu: Hãy dùng
LEFT+RIGHT+&cho những công thức ngắn gọn và dễ hiểu. - Tối ưu: Khi làm việc với dữ liệu lớn, hãy khai thác
LETvà công thức mảng. - Độ tin cậy: Sử dụng
IFERROR,LEN,TRIMđể tránh lỗi và đảm bảo dữ liệu sạch. - Mở rộng: Sử dụng Power Query khi cần xử lý phức tạp hoặc dữ liệu khổng lồ.
Hy vọng bài viết này đã cung cấp cho bạn một bộ công cụ toàn diện để xử lý mọi tình huống liên quan tới việc lấy ký tự đầu và ký tự cuối trong Excel. Chúc bạn thành công trong công việc!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.