12 Thủ Thuật Xử Lý Văn Bản Và Chuỗi Trong Excel (Cơ bản đến nâng cao)

Video hướng dẫn thực hiện đơn giản

Nối chuỗi trong Excel

Các bạn có thể tải file thực hành tại đây.

Ở đây mình có trường họ và trường tên bây giờ mình sẽ sử dụng hàm nối chuỗi để cho tên đầy đủ ta thực hiện như sau. Đầu tiên ta sẽ kích vào ô mà ta muốn hiển thị khi nối chuỗi mình kích vào ô F6 -> gõ = và kích vào ô D6 gõ &" "& kích vào ô E6 -> Enter.

Phần bên trong ngoặc kép là khoảng trắng cách hai chuỗi khi nối với nhau ta cũng có thể thay bằng chữ hay bằng bất cứ ký hiệu nào.

Ngoài ra bạn còn có thể sử dụng hàm CONCATENATE để nối chuỗi với cú pháp là CONCATENATE(ô đầu của chuỗi;" ";ô cuối của chuỗi) kết quả vẫn giống cách nối chuỗi ban đầu.

Thiết lập kiểu chữ

Để tất cả ký tự có trong hàng viết hoa thì bạn sử dụng hàm =UPPER(kích vào hàng mà bạn muốn viết hoa tất cả các ký tự) và Enter lúc này cả hàng bạn chọn trở thành chữ in hay gọi là viết hoa.

Nếu bạn muốn cả hàng đó trở thành ký tự thường thì bạn sử dụng hàm =LOWER(kích vào hàng mà bạn muốn chuyển hoa sang thường) -> Enter bây giờ các ký tự đã về dạng thường.

Viết hoa chữ cái đầu của mỗi từ ta làm như sau: ta dung làm =PROPER(kích vào ô mà bạn muốn chuyển đổi ) sau đi enter thì lập tức các ký tự hoa thường đều thành thường.

Để trả về độ dài của chuỗi ký tự ta sử dụng hàm Len với cú pháp =Len(kích vào ô mà bạn muốn biết độ dài chuỗi ký tự có trong ô) và Enter lúc này nó sẽ tính từ ký tự đầu tiên đến ký tự cuối cùng có trong ô và tính cả những khoảng trắng giữa các ký tự.

Bây giờ ta sẽ lấy ký tự bên trái hoặc bên phải mà ta muốn.

Để lấy ký tự bên trái ta sử dụng hàm =LEFT(ô mà ta muốn lấy;số ký tự mà ta muốn lấy) khi này nó sẽ trả về số ký tự mà bạn muốn lấy bắt đầu từ ký tự đầu tiên bên trái tính vào.

Lấy ký tự từ bên phải qua bạn sử dụng hàm =RIGHT(ô của chuỗi ký tự bạn cần lấy;số ký tự muốn lấy) khi này nó cũng trả về những ký tự có trong ô tính từ bên phải sang đủ số ký tự bạn nhập.

Bây giờ để chỉ viết hoa ký tự đầu tiên của mỗi chuỗi bạn tổng hợp các hàm lại với nhau thành là:

=UPPER(LEFT(C5;1))&LOWER(RIGHT(C5;LEN(C5)-1)) có nghĩa là đầu tiên bạn chọn ký tự đầu cho nó viết hoa lên sau đó bạn chọn tất cả các ký tự bỏ đi ký tự đầu và cho nó viết thường.

Loại khoảng trống thừa

Để loại bỏ khoảng trống thừa bạn sử dụng hàm trim hàm này sẽ có nhiệm vụ bỏ đi khoảng trống thừa nhưng nếu có nhiều khoảng trống thừa giữa hai ký tự thì hàm này chỉ có thể bỏ đi một khoảng trống thừa mà thôi. hàm này có cấu trúc như sau =Trim(kích vào ô bạn muốn bỏ khoảng trống thừa).

Tách một phần ký tự của chuỗi

Để tách một phần ký tự của chuỗi ta cần xác định lấy bao nhiêu ký tự, lấy từ đầu của chuỗi,...

Để lấy ký tự từ bên trái qua ta sử dụng hàm LEFT với cú pháp =LEFT(ô chứa chuỗi ký tự bạn muốn lấy; số ký tự muốn lấy).

Phần mã số sinh viên ta sẽ lấy từ bên phải qua nên ta sử dụng hàm RIGHT với cú pháp =RIGHT(ô chứa chuỗi ký tự bạn cần tách; số ký tự mà bạn muốn tách)

Bây giờ ta muốn lấy ký tự thứ 3 và thứ 4 trong mã trường thì ta phải sử dụng hàm mới đó là hàm MID với cú pháp là =MID(ô cần tách ký tự;ký tự bắt đầu tách;số ký tự muốn tách). Tức là mình sẽ bắt đầu lấy từ ký tự số 3 và chỉ lấy 2 ký tự.

Tách số từ chuỗi ký tự

Để tách số từ chuỗi ký tự thì đầu tiên ta cần xác định được số này nằm ở đâu trong chuỗi để ta có thể tìm kiếm và tách số.

Với chuỗi mình đang thực hành bạn cũng có thể thấy phần số nằm sau dấu - tức là ta chỉ cần tìm dấu - và lấy 2 ký tự phía sau là được.

Để tìm dấu - ta sử dụng hàm FIND với cú pháp là =FIND("-";ô mà bạn muốn tìm).

Khi đã tìm được vị trí của dấu - ta sẽ sử dụng hàm MID để tách số ra khỏi chuỗi vì số nằm sau dấu - nên ta thực hiện như sau: =MID(ô mà bạn sẽ tách số;1+FIND("-";ô mà bạn muốn tìm kiếm và ô này cũng là ô bạn sẽ tách số);số ký tự bạn muốn tách). 

VD: =MID(B10;1+FIND("-";B10);2) 

Tách chuỗi ký tự nâng cao

Để tách ký tự phía sau dấu - thứ hai thì ta phải sử dụng hai hàm FIND lồng nhau với cú pháp là =MID(B7;FIND("-";B7;FIND("-";B7)+1)+1;1000) số 1000 ở cuối là số ký tự bạn lấy với số này để đảm bảo bạn không bị thiếu ký tự.

Đếm số từ trong Excel

Để đếm số từ ta sẽ nghĩ ra thuật toán của nó là đầu tiên ta sẽ loại bỏ những khoảng cách giữa các từ đó đi sau đó ta sẽ lấy độ dài chuỗi đầu tiên trừ cho độ dài chuỗi sau khi loại bỏ khoảng trắng và cộng 1.

Đầu tiên là hàm xóa khoảng trắng với cấu trúc =SUBSTITUTE(B2;" ";"") có nghĩa ngõ nào có khoảng trắng sẽ thay bằng không khoảng trắng.

Tiếp theo ta sử dụng hàm LEN để đếm số ký tự sau đó trừ nhau là ra có bao nhiêu từ trong chuỗi ta thực hiện =1+LEN(B2)-LEN(SUBSTITUTE(B2;" ";"")). Lưu ý là nhớ cộng 1 vì hai từ mới có 1 khoảng trắng 3 từ 2 khoảng trắng nên phải cộng 1;

Thêm dấu xuống dòng của một ô trong Excel

Để khi ta vẽ đồ thị hay trong cùng một ô chứa hai thành phần và ta muốn xuống hàng để phân biệt hai thành phần đó thì ta làm như sau.

Đối với hàm nối chuỗi thông thường ta sẽ dùng =ô chuỗi thứ nhất & ô chuỗi thứ hai.

Nhưng để hai chuỗi này xuống dòng thì ta thêm vào hàm nối chuỗi hàm Char(10) như vậy ta có công thức: =B3&CHAR(10)& D3 sau đó bạn kích vào Wrap Text thì hai chuỗi khi nối sẽ tự động xuống hàng.

Tiếp theo để vẽ đồ thị bạn bôi đen cột tên danh số và doanh số sau đó vào Insert -> chọn dạng đồ thị bạn muốn vẽ.

Xóa ký tự đặc biệt 

Đối với những khoảng trắng thì ta có thể dùng hàm trim để xóa đi nhưng đối với những ký tự đặc biệt thì hàm trim không thể xóa được nên ta sẽ sử dụng hàm CLEAN. Để cho một câu hoàn chỉnh ta xóa cả những khoảng trắng thừa và những ký tự đặc biệt ta sẽ sử dụng hàm TRIM và hàm CLEAN lồng nhau. =TRIM(CLEAN(ô chứa chuỗi bạn muốn xóa ký tự đặc biệt)).

Đệm thêm số 0

Khi độ dài của chuỗi số khác nhau mà ta muốn đảm bảo mỗi chuỗi đều phải có đủ 10 số thì ta đệm thêm các số 0 vào phía sau. 

Đầu tiên ta thêm những số 0 và phía sau các chuỗi bằng lệnh =ô bạn muốn thêm &"000000000000". VD: =B5&"00000000000000"

Mình sẽ sử dụng hàm LEFT để lấy 10 ký tự với cú pháp là =LEFT(B5&"00000000000000";10) như vậy các chuỗi sẽ được lấy 10 ký tự nên độ dài như nhau.

Định dạng số trong chuỗi ký tự

Khi nối hai chuỗi tên và số nhưng có dấu $ phía trước ta nối chuỗi thông thường sẽ là =ô chứa chuỗi thứ nhất &":"& ô chứa chuỗi thứ hai nhưng khi nối chuỗi này thì sẽ không giữ được dấu $.

Để nối chuỗi nhưng vẫn giữ lại được dấu $ thì ta sử dụng thêm hàm Text với cú pháp: =B5&":"&TEXT(C5;"$0").

Định dạng số trong chuỗi ký tự bằng hàm Dollar

Mình cũng định dạng số như hàm TEXT nhưng mình sẽ sử dụng hàm Dollar với cú pháp đơn giản dễ nhớ hơn: =B5&":"$Dollar(C5;0)