Một số hàm cắt chuỗi trong excel

Thứ bảy - 14/09/2013 11:52
Trong excel, ngoài việc kết hợp các chuỗi riêng lẻ với nhau dựa vào hàm con-catenate, thì excel cũng trang bị cho chúng ta cách để cắt chuỗi ra thành các chuỗi nhỏ hơn.
Trong excel, ngoài việc kết hợp các chuỗi riêng lẻ với nhau dựa vào hàm con-catenate hay dùng toán tử & như bài học hôm trước, thì excel cũng trang bị cho chúng ta cách để cắt chuỗi ra thành các chuỗi nhỏ hơn. Hôm nay tôi xin giới thiệu đến các bạn một số hàm cắt chuỗi trong excel như sau:

Hàm Right: Dùng để cắt và lấy phần bên phải của một chuỗi
Hàm Left: Dùng để cắt và lấy phần bên trái của một chuỗi
Hàm Mid: Dùng để cắt và lấy phần giữa của một chuỗi

Sau đây chúng ta học cách sử dụng của từng hàm nhé

1. Hàm Right:
=Right(Text;[num_c-har])
Có thể được hiểu như sau
=Right("Chuỗi gốc";"Số ký tự muốn cắt lấy")
Trong đó
"Chuỗi lớn" hay Text là một chuỗi chứa trong ô mà ta muốn cắt
"Số ký tự muốn cắt lấy" hoặc [num_c-hars] là số ký tự bạn muốn hàm Right trả về. Số này phải là số nguyên lớn hơn 0. nếu bằng không thì hàm right sẽ không cắt được ký tự nào cả.
Ví dụ: xuất ra quốc tịch của từng người trong ô B1~B3
 A                B                         
1   Nguyễn Văn Phong - Kỹ sư - Việt Nam       =right(A1;8)
2   Đào Hồng Đăng -Kỹ Sư - Lào                    =right(A2;3)
3   Ưa Mít Ướt - Kế Toán - Campuchia            =right(A3;9)

Như vậy muốn cắt chữa Việt Nam ở ô A1 và cho nó xuất hiện ở ô B1 thì dùng hàm Right, Text=A1,Num_C-hars=8 (Số ký tự của chữ Việt Nam là 8 (8 C-haracters). và ô B2, Num_C-hars = 3 vì chữ LÀO chỉ có 3 kí tự thôi.

Bây giờ chúng ta học cách sử dụng hàm Left.
2. Hàm left thì ngược với hàm Right thôi, là cắt và xuất ra phần chuỗi nằm bên trái của chuỗi lớn.
=Left(Text;[num_c-har])
Có thể được hiểu như sau
=Left("Chuỗi gốc";"Số ký tự muốn cắt lấy")
Ví dụ: xuất ra họ và tên của nhân viên trong ô B1~B3
 A                  B                        
1   Nguyễn Văn Phong - Kỹ sư - Việt Nam               =left(A1;8)
2   Đào Hồng Đăng - Kỹ Sư - Lào                          =left(A2;3)
3   Ưa Mít Ướt - Kế Toán - Campuchia                    =left(A3;9)

Chữ Việt Nam có 8 ký tự (kể cả khoảng trắng) nên ta nhập vào Num_c-hars=8

3. Hàm Mid: Cắt phần ở giữa một chuỗi và xuất ra.
Cú pháp:
=MID(Text;start_num;Num_c-hars) và được hiểu như sau:
=MID("Chuỗi gốc";"vị trí cắt";"Số ký tự cần cắt và xuất ra")

Áp dụng hàm mid để cắt và xuất ra phần text nghề nghiệp ở bảng sau:
 A               B                       
1   Nguyễn Văn Phong - Kỹ sư - Việt Nam    =mid(A1;21;5)
2   Đào Hồng Đăng -Kỹ Sư - Lào                =mid(A2;18;5)
3   Ưa Mít Ướt - Kế Toán - Campuchia         =mid(A3;13;8)

Ta thấy chữ Kỹ Sư trong ô A1 nằm ở vị trí 21 và có 5 ký tự bao gồm cả khoảng trắng Space Bar.

 Tuy nhiên nếu chúng ta dùng hàm mà phải đi đếm bằng mắt rồi sửa bằng tay cho từng công thức thì không có ý nghĩa gì cả. Cái ta muốn là làm sao viết một công thức rồi áp dụng cho tất cả các dòng thì mới thực sự mang lại hiệu quả trong công việc.

Dưới đây là cách tôi tối ưu hóa để xuất chuỗi bên trái, bên phải, và ở giữu một cách tự động. bạn có thể copy công thức rồi dán vào excel để thực hành nhé.
Ta dùng Hàm Search để để tìm đến ký tự "-" và cho biết ký tự này ở vị trí thứ bao nhiêu trong chuổi chứa trong ô A2,A3,A4... Vị trí này chính là số ký tự chúng ta cần lấy cho Hàm Left.

 A               B                                C    D
1   Họ Tên - Nghề Nghiệp - Quốc Tịch      Họ Và Tên                              
2  Nguyễn Văn Phong -Kỹ sư - Việt Nam  =LEFT(A2;SEARCH(" - ";A2)-1)
3  Đào Hồng Đăng -Kỹ Sư - Lào             =LEFT(A3;SEARCH(" - ";A3)-1)
4  Ưa Mít Ướt - Kế Toán - Campuchia      =LEFT(A4;SEARCH(" - ";A4)-1)

Cột C: Nghề nghiệp có công thức như sau:
C2 =MID(RIGHT(A2;LEN(A2)-LEN(B2)-3);1;LEN(LEFT(RIGHT(A2;LEN(A2)-LEN(B2)-3);SEARCH(" - ";RIGHT(A2;LEN(A2)-LEN(B2)-3)))))
C3=MID(RIGHT(A3;LEN(A3)-LEN(B3)-3);1;LEN(LEFT(RIGHT(A3;LEN(A3)-LEN(B3)-3);SEARCH(" - ";RIGHT(A3;LEN(A3)-LEN(B3)-3)))))
C4=MID(RIGHT(A4;LEN(A4)-LEN(B4)-3);1;LEN(LEFT(RIGHT(A4;LEN(A4)-LEN(B4)-3);SEARCH(" - ";RIGHT(A4;LEN(A4)-LEN(B4)-3)))))

* Ta dùng Hàm Len() dùng để đếm chiều dài của chuỗi trong ô A2,A3,A4, B2,B3,B4, từ đó ta tính toán để suy ra vị trí cần cắt.

enlightened Hàm Mid như trên có thể thay thế bằng:
C2=LEFT(RIGHT(A2;LEN(A2)-LEN(B2)-3);SEARCH(" - ";RIGHT(A2;LEN(A2)-LEN(B2)-3)))

Và cột D: Quốc tịch có công thứ như sau:
D2=RIGHT(A2;LEN(A2)-LEN(B2)-LEN(C2)-5)
D3=RIGHT(A3;LEN(A3)-LEN(B3)-LEN(C3)-5)
D4=RIGHT(A4;LEN(A4)-LEN(B4)-LEN(C4)-5)

Chúc các bạn thành công.

Tác giả bài viết: Minh Phú

Chú ý: Việc đăng lại bài viết trên ở website hoặc các phương tiện truyền thông khác mà không ghi rõ nguồn http://aneedz.com là vi phạm bản quyền

Tổng số điểm của bài viết là: 54 trong 11 đánh giá

Xếp hạng: 4.9 - 11 phiếu bầu
Click để đánh giá bài viết

  Ý kiến bạn đọc

Mã bảo mật   
Thống kê
  • Đang truy cập27
  • Hôm nay6,773
  • Tháng hiện tại64,764
  • Tổng lượt truy cập1,585,616
Bạn đã không sử dụng Site, Bấm vào đây để duy trì trạng thái đăng nhập. Thời gian chờ: 60 giây