Hàng này công việc kinh doanh của bạn tạo ra hàng triệu dữ liệu và hầu hết chúng không được sắp xếp theo cấu trúc, thậm chí công nghệ tốt nhất trên thế giới hiện nay cũng không thể cho ra những dữ liệu sẵn có để bạn có thể dựa vào chúng mà ra quyết định ngay được.

Đấy là lý do vì sao mà chúng ta phải phụ thuộc nhiều vào MS Excel từ những điều đơn giả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 các hàm xử lý văn bản trong Excel

Hàm CONCATENATE

Hàm này 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 đầu tiên 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à các ô dữ 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 có thể thêm các ký hiệu khác như , – * etc.

Ví dụ 2: Vì 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”)

= Lillie.Merrit@xyz.com

Trong công thức này, lấy ô C2 bởi vì nó là nơi mà 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 mở rộng của e-mail,nó cũng giống như : @hp.com. @oracle.com..

Lưu ý: bạ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ờ chúng ta đã có địa chỉ e-mail ở cột D, với nó ta có thể gửi email đến tất cả mọi người. Nhưng điều bạn phải làm là copy paste tất cả email này cho chúng nằm cạnh nhau để gửi đồng loạt.

Bạ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 đầu tiên và mail thứ hai vào ô E2

=CONCATENATE(D2,”;”,D3)

Bước 2: Nối ô E2 với ô D4 để khi chúng ta kéo công thức đến hàng cuối nó sẽ tự động tạo thành một mailing list.

=CONCATENATE(E2,”;”,D4)

E2: là ô nối mail đầu tiên 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 bởi vì nó sẽ thêm mỗi địa chỉ email đến 2 lần

Cuối cùng, đi đến hàng cuối cùng để 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ó” kết quả trả về là “True”,” không” là “False”

Cú pháp: EXACT(text1, text2)

Ví dụ 4: Trong Ví dụ này, chúng ta so sánh tên các saleman để kiểm tra sự chính xác của dữ 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 kết quả 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 có thể tùy chọn điểm bắt đầu từ nơi bạn muốn kiểm tra trong within_text

Ví dụ 5:

Trong trườn hợp này, 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í bắt đầu của tên đệm (dấu chấm)

=FIND(“.”,”Barry.Chee”) = 6

Hàm LEFT

Hàm lấy ra chính xác 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, chúng ta có một danh sách sản phẩm bao gồm cả mã sản phẩm, bây giờ hãy lấy ra chỉ mã sản phẩm để tách chúng thành cột riêng biệt. Để làm điều này bạ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 có thể làm các phân tích phức tập bằng cách kết 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, bây giờ hãy tách lấy số

Vấn đề là các số này không có số ký tự cụ thể, 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, đầu tiên là xác định số ký tự cần tách, để làm điều này bạn có thể sử dụng hàm FIND

Bước 1:

FIND(” “,A2)  = 7

Trong find_text, thêm khoảng trống “ “để chỉ rằng 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ố chữ cái cần tách dựa trên vị trí của khoảng trống

Bước 2:

LEFT(A2,FIND(” “,A2))

Để ý rằng khi dùng hàm FIND để xác định  số ký tự cần tách ra, bạn sẽ được kết quả theo mong muốn.

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 rộng rãi bởi hầu hết các chuyên gia Excel.

Ví dụ 8: Trong Ví dụ này, 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(“Lillie.Merrit@xyz.com”)  = 21

Note: LEN cũng đếm cả khoảng trống

Sử dụng kết 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, bây giờ hãy tách tên của các 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ất cả văn bản về dạng chữ cái thường

Cú pháp: LOWER(text)

Ví dụ 10: Trong Ví dụ này chúng ta có văn bản viết hoa,bây giờ 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 bắt đầu lấy ký tự

Num_chars: số ký tự muốn lấy

Ví dụ 11: Trong Ví dụ này, chúng ta có danh sách sản phẩm gồm tên công ty, tên sản phẩm và mã sản phẩm. Bạn hãy tách lấy mỗi tên của sản phẩm

=MID(A1,8,40)

A1: ô dữ liệu

8: điểm bắt đầu 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 bắt đầu sẽ là 8

40: Số chữ cái trong sản phẩm, nó thay đổi theo sản phẩm nên để 40 là maximum.

Hàm PROPER

Hàm này viết hoa chữ cái đầu tiên 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ế 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í bắt đầu muốn thay trong văn bản

num_chars: số ký tự muốn thay thế

new_text: văn bản muốn thêm vào

Ví dụ 13: Sử dụng dữ liệu từ Ví dụ 11. Trong trường hợp này, hãy thay thế mã sản phẩm thành khoảng trống.

=REPLACE(A1,19,3,””)

Hàm REPT

Hàm được sử dụng để lặp lại 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 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 phạm vi các ô mà người dùng tùy chỉnh.

Hàm SEARCH

Hàm này để xác định 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, chữ cái 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 bắt đầu 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í bắt đầu của tên họ người công nhân đó (last name)

A2 = Lillie Merrit

=SEARCH(” “,A2) = 7

7 là kết quả trả về, có nghiã là tên họ của công nhân trong A2 bắt đầ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 định vị trí  khoảng trống đầu tiên và cộng thêm một để xác định 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 đầu tiên. Vì vậy thêm 1 sẽ là 8 là số bắt đầu phần tiếp theo. Chúng ta đang  dẫn Excel tìm khoảng trống bắt đầ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 đầy đủ của công nhân ở cột A, bây giờ 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 sao nếu bạn không biết chính xác số ký tự cần tách?

Đây là công thức tự động xác định 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í cụ thể

Hàm SUBSTITUE

Hàm để 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 thay thế

new_text : văn bản thay thế

[instance_num] : tùy chọn thay thế ở 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ế 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 thay đổi 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ỉ  thay thế văn bản ở vị trí đầu tiên và bỏ qua các vị trí khác.

Hàm TRIM

Hàm loại bỏ các khoảng trống thừa trong câu

Cú pháp: TRIM(text)

Ví dụ 17: Hãy bỏ các 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 các khoảng trống thừa, chỉ giữ lại một khoảng trống giữa các 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 áp dụng các hàm văn bản trong Excel là vấn đề lớn đối với các nhà phân tích, dữ liệu càng phức tạp thì chúng ta càng phải phụ thuộc nhiều vào nó. Vì vậy nếu bạn vẫn chưa biết cách sử dụng các hàm này thì hãy luyện tập hàng ngày trong công việc của mình.