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

Khi thao tác với Excel bạn thường gặp những vấn đề như cần nối chuỗi, thiết lập kiểu chữ, tách số từ chuỗi, xóa ký tự đặc biệt,... Vì thế bài viết này sẽ hướng dẫn các bạn 12 thủ thuật từ cơ bản đến nâng cao trong excel.

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 &" "& kích vào ô E6 -> Enter.

hàm nối chuỗi

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.

khoảng cách giữa hai chuỗi khi nối

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.

hàm concatenate

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.

hàm viết hoa tất cả các ký tự

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.

hàm chuyển ký tự hoa sang 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.

ký tự hoa đầu mỗi từ

Để 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ự.

đếm số 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ự trong ô

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.

lấy ký tự từ phải sang trái

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.

hàm viết hoa ký tự đầu

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).

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).

hàm tách ký tự

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)

hàm tách ký tự từ phải sang

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 ký tự tùy chọn

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).

tìm ký tự

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 số khỏi chuỗi

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ự.

hàm tách ký tự nâng cao

Đế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.

xóa khoảng trắng giữa các từ

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;

đếm số ký tự trong excel

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.

xuống hàng trong cùng một ô

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ẽ.

vẽ đồ thị trong excel

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)).

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"

thêm số không vào sau

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.

hàm lấy ký tự

Đị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 thông thường

Để 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").

nối chuỗi nâng cao

Đị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)

hàm nối chuỗi nâng cao

CHO ĐIỂM BÀI VIẾT NÀY

vote data
TOP