Các Hàm Text Trong Excel: 19 Cách Giúp Xử Lý Văn Bản Hiệu Quả – Học Excel Online Miễn Phí

Đấy là nguyên do vì sao mà tất cả chúng ta phải phụ thuộc vào nhiều vào MS Excel từ những điều đơn thuần đến phức tập nhất .

Bài viết này sẽ giúp bạn hiểu thêm về các hàm văn bản trong Excel và làm cách nào để áp dụng chúng một cách hiệu quả khi phân tích dữ liệu theo hệ thống.

Danh sách những hàm giải quyết và xử lý văn bản trong Exce l

Hàm CONCATENATE

Hàm này được cho phép nối hai hay nhiều chuỗi văn bản với nhau, max là 255
Cú pháp : CONCATENATE ( text1, [ text2 ], … )Text 1 : Văn bản tiên phong bạn muốn thêm
Text 2 : Thứ hai, ba, bốn … cho đến 255
Ví dụ 1 : Nối tên chính ở cột A với tên đệm ở cột B
Ta có :
= CONCATENATE ( A2, ”. ”, B2 )

Trong công thức trên A1 và B2 là những ô tài liệu còn dấu chấm để phân tách giữa tên chính và tên đệm
Tương tự bạn hoàn toàn có thể thêm những ký hiệu khác như, – * etc .
Ví dụ 2 : Vì tất cả chúng ta đã có cột C gồm cả tên chính và tên đệm nên giờ hãy chuyển chúng về dạng email bằng cách thêm “ @ xyz. com ” ở cuối .
Đây là công thức :
= CONCATENATE ( C2, ” @ xyz. com ” )
= [email protected]
Trong công thức này, lấy ô C2 do tại nó là nơi mà tất cả chúng ta vừa tạo công thức nối tên chính và tên đệm với nhau .
“ @ xyz. com ” là phần lan rộng ra của e-mail, nó cũng giống như : @ hp. com. @ oracle. com ..
Lưu ý : bạn hoàn toàn có thể nối bất kể văn bản nào nhưng chúng phải nằm giữa hai dấu ngoặc kép theo công thức “ your text ” .
Ví dụ 3 : Bây giờ tất cả chúng ta đã có địa chỉ e-mail ở cột D, với nó ta hoàn toàn có thể gửi email đến tổng thể mọi người. Nhưng điều bạn phải làm là copy paste tổng thể email này cho chúng nằm cạnh nhau để gửi hàng loạt .
Bạn hoàn toàn có thể sử dụng công thức dưới đậy để tạo ra một mailing list, có 2 bước trong công thức này :
Bước 1 : Nối mail tiên phong và mail thứ hai vào ô E2
= CONCATENATE ( D2, ” ; ”, D3 )
Bước 2 : Nối ô E2 với ô D4 để khi tất cả chúng ta kéo công thức đến hàng cuối nó sẽ tự động hóa tạo thành một mailing list .
= CONCATENATE ( E2, ” ; ”, D4 )

E2 : là ô nối mail tiên phong vs mail thứ hai
D4 : là mail tiếp theo muốn thêm trong mailing list
Note : đừng lựa chọn D3 cho công thức số 2 do tại nó sẽ thêm mỗi địa chỉ email đến 2 lần
Cuối cùng, đi đến hàng ở đầu cuối để xem mailing list

Hàm EXACT ( )

Đây là hàm so sánh hai văn bản xem liệu chúng có giống nhau hay không ? Nếu “ có ” tác dụng trả về là “ True ”, ” không ” là “ False ”
Cú pháp : EXACT ( text1, text2 )
Ví dụ 4 : Trong Ví dụ này, tất cả chúng ta so sánh tên những saleman để kiểm tra sự đúng mực của tài liệu từ hai nguồn khác nhau
= EXACT ( A2, B2 )

Nhìn vào Ví dụ, ta đang xem liệu tên ở cột A có nối với đúng dòng trong cột B hay không .
Ở ô C2 cho ra tác dụng là “ False ” bởi 2 tên không giống nhau, còn ô C4 ra “ False ” là do chữ D không viết hoa .

Hàm FIND

Hàm  find này trả về kết quả là vị trí đầu tiên của chuỗi văn bản bạn muốn tìm.
Xem thêm: Cách sử dụng hàm dò tìm ký tự trong excel

Cú pháp : FIND ( find_text, within_text, [ start_num ] )
find_text : văn bản bạn đang tìm kiếm ( chuỗi con )
within_text : văn bản đích, chứa chuỗi muốn tìm ( chuỗi mẹ )
[ start_num ] : bạn hoàn toàn có thể tùy chọn điểm mở màn từ nơi bạn muốn kiểm tra trong within_text
Ví dụ 5 :
Trong trườn hợp này, tất cả chúng ta có tên của một công nhân bên dưới, bạn hãy tìm vị trí khởi đầu của tên đệm ( dấu chấm )
= FIND ( “. ”, ” Barry. Chee ” ) = 6

Hàm LEFT

Hàm lấy ra đúng chuẩn số ký tự từ văn bản cho trước
Cú pháp : LEFT ( text, [ num_chars ] )
Text : văn bản
[ num_chars ] ) : số ký tự bạn muốn tách ra
Ví dụ 6 : Trong bài này, tất cả chúng ta có một list loại sản phẩm gồm có cả mã loại sản phẩm, giờ đây hãy lấy ra chỉ mã mẫu sản phẩm để tách chúng thành cột riêng không liên quan gì đến nhau. Để làm điều này bạn hoàn toàn có thể dùng công thức dưới đây :
= LEFT ( A2, 6 )
A2 : văn bản
6 : bạn muốn lấy 6 ký tự đầu từ bên trái sang

Kết hợp hàm Left và Find
Bạn hoàn toàn có thể làm những nghiên cứu và phân tích phức tập bằng cách phối hợp 2-3 công thức .
Ví dụ 7 : Chúng ta thấy số và tên của công nhân ở chung một cột, giờ đây hãy tách lấy số
Vấn đề là những số này không có số ký tự đơn cử, có người có 3 số, có người có 5 số
Chúng ta sẽ chia công thức thành 2 phần, tiên phong là xác lập số ký tự cần tách, để làm điều này bạn hoàn toàn có thể sử dụng hàm FIND
Bước 1 :
FIND ( ” “, A2 ) = 7
Trong find_text, thêm khoảng chừng trống “ “ để chỉ rằng tất cả chúng ta muốn tìm kiếm khoảng trống trong chuỗi văn bản .
A2 là văn bản gốc
Với công thức này bạn sẽ thấy số vần âm cần tách dựa trên vị trí của khoảng chừng trống
Bước 2 :
LEFT ( A2, FIND ( ” “, A2 ) )
Để ý rằng khi dùng hàm FIND để xác lập số ký tự cần tách ra, bạn sẽ được tác dụng theo mong ước .

Hàm LEN

Hàm này để đếm số ký tự trong văn bản
Cú pháp : LEN ( text )
LEN là một hàm được sử dụng thoáng đãng bởi hầu hết những chuyên viên Excel .
Ví dụ 8 : Trong Ví dụ này, tất cả chúng ta có địa chỉ e-mail ở trong một cột, có bao nhiêu ký tự trong mỗi e-mail ?
LEN ( “ [email protected] ” ) = 21
Note : LEN cũng đếm cả khoảng chừng trống
Sử dụng phối hợp hàm Len với Left
Ví dụ 9 : Chúng ta sử dụng địa chỉ email từ Ví dụ trước, giờ đây hãy tách tên của những công nhân từ địa chỉ mail này .
= LEFT ( A2, LEN ( A2 ) – 8 )

A2 là văn bản – địa chỉ mail
LEN ( A2 ) : đếm số ký tự trong mail
– 8 : trừ đi số ký tự của “ @ xyz. com ”

Vậy, chúng ta đang sử dụng hàm Len để đếm toàn bộ ký tự và trừ 8 ký tự để còn lại các ký tự (tên) cần tách ra.

Hàm LOWER

Hàm chuyển tổng thể văn bản về dạng vần âm thường
Cú pháp : LOWER ( text )
Ví dụ 10 : Trong Ví dụ này tất cả chúng ta có văn bản viết hoa, giờ đây hãy chuyển chúng sang kiểu chữ thường .
= LOWER ( “ HELLO JAY GOOD MORNING.. ! ! ” ) = hello jay good morning .. ! !

Hàm MID

Hàm này lấy giá trị ở giữa văn bản theo ý của người dùng
Cú pháp : MID ( text, start_num, num_chars )
Text : văn bản bạn muốn lấy ký tự
Start_num : điểm khởi đầu lấy ký tự
Num_chars : số ký tự muốn lấy
Ví dụ 11 : Trong Ví dụ này, tất cả chúng ta có list loại sản phẩm gồm tên công ty, tên mẫu sản phẩm và mã loại sản phẩm. Bạn hãy tách lấy mỗi tên của mẫu sản phẩm
= MID ( A1, 8,40 )

A1 : ô tài liệu
8 : điểm mở màn của tên. Trong trường hợp này “ Ballon ” có 6 chữ cộng với 1 khoảng trống = 7 nên điểm mở màn sẽ là 8
40 : Số vần âm trong mẫu sản phẩm, nó biến hóa theo loại sản phẩm nên để 40 là maximum .

Hàm PROPER

Hàm này viết hoa vần âm tiên phong của mỗi từ
Ví dụ
= PROPER ( “ HELLO JaY GOoD MoRnINg .. ! ! ” ) = Hello Jay Good Morning .. ! !

Hàm REPLACE

Hàm này thay thế sửa chữa một phần văn bản
Cú pháp : REPLACE ( old_text, start_num, num_chars, new_text )
Old_ text : văn bản cũ
start_num : vị trí khởi đầu muốn thay trong văn bản
num_chars : số ký tự muốn thay thế sửa chữa
new_text : văn bản muốn thêm vào
Ví dụ 13 : Sử dụng tài liệu từ Ví dụ 11. Trong trường hợp này, hãy thay thế sửa chữa mã loại sản phẩm thành khoảng trống .
= REPLACE ( A1, 19,3, ” ” )

Hàm REPT

Hàm được sử dụng để tái diễn văn bản hoặc ký hiệu dựa theo ý người dùng
Ví dụ, bạn muốn lặp ký tự * 10 lần trong một ô, bạn hoàn toàn có thể viết công thức như sau :
= REPT ( “ * ”, 10 ) = * * * * * * * * * *
Hàm REPT sẽ rất giúp ích trong việc tạo biểu đồ đường trong khoanh vùng phạm vi những ô mà người dùng tùy chỉnh .

Hàm SEARCH

Hàm này để xác lập vị trí văn bản con trong một văn bản khác
Cú pháp : SEARCH ( find_text, within_text, [ start_num ]
find_text : văn bản, vần âm hay kí hiệu cần tìm ( văn bản con )
within_text : văn bản chứa văn bản cần tìm
[ start_num ] : tùy chọn điểm mở màn tìm kiếm trong within_text
Ví dụ 14 : Danh sách tên công nhân ở cột A, tìm vị trí mở màn của tên họ người công nhân đó ( last name )
A2 = Lillie Merrit
= SEARCH ( ” “, A2 ) = 7
7 là hiệu quả trả về, có nghiã là tên họ của công nhân trong A2 khởi đầu từ vị trí thứ 7

Biến thể: Nếu văn bản có nhiều khoảng trống thì áp dụng công thức sau:

= SEARCH ( ” “, E2, SEARCH ( ” “, E2 ) + 1 )
Hàm SEARCH để xác lập vị trí khoảng trống tiên phong và cộng thêm một để xác lập số bắt đâu cho khoảng trống thứ hai

Ví dụ, dựa vào công thức thứ nhất, ta có “ 7 ” là vị trí khoảng trống tiên phong. Vì vậy thêm 1 sẽ là 8 là số mở màn phần tiếp theo. Chúng ta đang dẫn Excel tìm khoảng trống khởi đầu từ vị trí thứ 8 của văn bản

Hàm RIGHT

Hàm tách ký tự từ bên phải văn bản
Cú pháp : RIGHT ( text, [ num_chars ] )
Text : văn bản
[ num_chars ] : số ký tự muốn lấy
Ví dụ 15 : Ta có tên khá đầy đủ của công nhân ở cột A, giờ đây hãy lấy ra tên họ ở cột B
Text in A2 : Lillie. Merrit
= RIGHT ( A2, 6 ) = Merrit

Biến thể

Phải làm thế nào nếu bạn không biết đúng chuẩn số ký tự cần tách ?
Đây là công thức tự động hóa xác lập tên họ và tách ra
= RIGHT ( A2, LEN ( A2 ) – SEARCH ( “. ”, A2 ) )

Hàm LEN để đếm tổng số ký tự trong văn bản và hàm SERACh để tìm vị trí đơn cử

Hàm SUBSTITUE

Hàm để sửa chữa thay thế văn bản, ký hiệu và chữ số theo ý ngươi dùng
Cú pháp : SUBSTITUTE ( text, old_text, new_text, [ instance_num ] )
Text : văn bản
old_text : văn bản muốn sửa chữa thay thế
new_text : văn bản sửa chữa thay thế
[ instance_num ] : tùy chọn thay thế sửa chữa ở vị trí nào thứ nhất hay thứ hai …
Ví dụ 16 : Chúng ta có tên ở cột A, hãy thay thế sửa chữa khoảng trống giữa tên và tên đệm thành ký tự gạch dưới “ _ ”
Text in A2 : Lillie. Merrit
= SUBSTITUTE ( A2, ”. ”, ” _ ” ) = Lillie_Merrit

Biến thể: trong Ví dụ này chúng ta có khoảng trống cả ở giữa tên, tên đệm và mã số.  Hãy thay thế ký tự  gạch dưới “_” chỉ cho tên còn mã số thì giữ nguyên.

= SUBSTITUTE ( E2, ” “, ” _ ”, 1 )

Điều biến hóa duy nhất là công thức trên là thêm 1 – insurance number, có nghĩa là hàm này sẽ chỉ sửa chữa thay thế văn bản ở vị trí tiên phong và bỏ lỡ những vị trí khác .

Hàm TRIM

Hàm vô hiệu những khoảng trống thừa trong câu
Cú pháp : TRIM ( text )
Ví dụ 17 : Hãy bỏ những khoảng trống trong văn bản sau

= TRIM ( “ Hello Jay Good Morning .. ! ! ” ) = Hello Jay Good Morning .. ! !
Với hàm trên, ta có thẻ xóa được những khoảng trống thừa, chỉ giữ lại một khoảng trống giữa những từ

Hàm UPPER

Hàm chuyển văn bản từ chữ thường sang chữ hoa
Cú pháp : UPPER ( text )

Ví dụ, chuyển “hi good morning”sang dạng viết hoa

= UPPER ( “ hi good morning ” ) = HI GOOD MORNING

Kết luận

Cách vận dụng những hàm văn bản trong Excel là yếu tố lớn so với những nhà nghiên cứu và phân tích, tài liệu càng phức tạp thì tất cả chúng ta càng phải phụ thuộc vào nhiều vào nó. Vì vậy nếu bạn vẫn chưa biết cách sử dụng những hàm này thì hãy rèn luyện hàng ngày trong việc làm của mình .

Source: https://mix166.vn
Category: Thủ Thuật