19 Cách Sử Dụng Hàm Text Trong Excel Để Xử Lý Dữ Liệu Văn Bản

Với việc hằng ngày công việc kinh doanh phát sinh hàng triệu bản ghi dữ liệu, việc sắp xếp và tổ chức thông tin này có thể trở nên phức tạp. Ngay cả những công nghệ hiện đại nhất cũng chưa thể tự động hóa hoàn toàn quá trình này mà không cần đến sự can thiệp của con người trong việc ra quyết định.

Chính vì vậy, chúng ta thường xuyên phải dựa vào MS Excel để giải quyết nhiều vấn đề từ cơ bản đến nâng cao.

Thông qua bài viết này, bạn sẽ được mở rộng kiến thức về các hàm văn bản trong Excel và cách ứng dụng chúng vào việc phân tích dữ liệu một cách hệ thống.

Đây là danh sách các hàm xử lý dữ liệu văn bản trong Excel

Hàm CONCATENATE

Hàm CONCATENATE dùng để ghép chung hai hoặc nhiều đoạn văn bản, không quá 255 phần tử.

Cú pháp:  CONCATENATE (text1, [text2],…)

Text 1: Chuỗi văn bản đầu tiên mà bạn muốn ghép

Text 2: Chuỗi văn bản thứ hai, thứ ba, và tiếp tục đến 255 chuỗi

Ví dụ 1: Hãy ghép tên ở cột A với họ ở cột B

Dưới đây là cách thực hiện:

=CONCATENATE(A2,”.”,B2)

Trong cách sử dụng trên, A1 và B2 là các ô chứa dữ liệu và dấu chấm được dùng để phân cách giữa tên và họ.

Bạn cũng có thể thêm các dấu phân cách khác như đấu phẩy, gạch ngang, sao, v.v.

Ví dụ 2: Giả sử chúng ta đã có cột C chứa cả tên và họ, bây giờ ta sẽ chuyển chúng thành định dạng email bằng cách thêm “@xyz.com” vào cuối mỗi chuỗi.

Công thức như sau:

=CONCATENATE(C2,”@xyz.com”)

Kết quả là: Lillie.Merrit@xyz.com

Trong việc áp dụng công thức này, ô C2 được sử dụng bởi nó chứa chuỗi đã được ghép từ tên và họ.

“@xyz.com” chính là phần mở rộng của địa chỉ email, tương tự như “@hp.com”, “@oracle.com”, vv.

Chú ý: bạn có thể ghép kết bất kỳ chuỗi văn bản nào, miễn là đặt chúng giữa hai dấu ngoặc kép theo định dạng “văn bản của bạn”.

Ví dụ 3: Giờ đây khi đã có sẵn địa chỉ email trong cột D, chúng ta có thể gửi email hàng loạt. Cần phải sao chép và dán tất cả các địa chỉ email cạnh nhau để tạo được danh sách mail.

Dùng công thức sau để tạo ra một danh sách gửi mail liên tục, gồm có hai bước:

Bước 1: Ghép địa chỉ email đầu tiên và thứ hai vào ô E2

=CONCATENATE(D2,”;”,D3)

Bước 2: Kết hợp ô E2 với ô D4 sao cho khi đảo công thức này xuống dòng tiếp theo, nó sẽ tự động tạo ra danh sách mail liên tục.

=CONCATENATE(E2,”;”,D4)

E2: là ô kết hợp hai email đầu tiên

D4: là ô chứa địa chỉ email tiếp theo muốn thêm vào danh sách

Lưu ý: Đừng chọn ô D3 cho công thức thứ hai bởi điều này sẽ khiến một địa chỉ email bị thêm vào danh sách hai lần

Cuối cùng, kéo công thức đến dòng cuối cùng để kiểm tra danh sách mail hoàn chỉnh

Hàm EXACT()

Hàm EXACT được dùng để so sánh hai chuỗi văn bản, xác định xem chúng có giống hệt nhau không. Nếu giống nhau sẽ trả về giá trị “True” còn không thì là “False”.

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

Ví dụ 4: Trong trường hợp này, chúng ta sẽ so sánh tên của các nhân viên bán hàng để kiểm tra độ chính xác của thông tin từ hai nguồn khác nhau.

=EXACT(A2,B2)

Xem xét Ví dụ, ta đang đánh giá xem tên trong cột A có khớp với đúng dữ liệu tương ứng trong cột B hay không.

Tại ô C2 kết quả là “False” do hai tên này không giống hệt nhau, trong khi ô C4 cũng cho ra kết quả “False” vì không đúng viết hoa chữ cái đầu.

Chức năng Hàm FIND

Chức năng của hàm find là xác định vị trí xuất hiện đầu tiên trong văn bản của chuỗi cần tìm kiếm.
Xem chi tiết tại: Hướng dẫn sử dụng hàm tìm kiếm ký tự trong excel

Cách sử dụng: FIND(find_text, within_text, [start_num])

find_text: đoạn văn bản mà bạn đang tìm (sub-string)

within_text: đoạn văn bản chứa chuỗi mà bạn muốn tìm (main-string)

[start_num]: một giá trị tùy chọn để xác định vị trí bắt đầu kiểm tra trong within_text

Thí dụ 5:

Đối với trường hợp sau, ta có tên của một nhân viên ở dưới đây, hãy tìm ra vị trí bắt đầu của tên đệm của người đó (ký tự chấm).

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

Chức năng Hàm LEFT

Hàm này giúp trích xuất một lượng ký tự nhất định từ đầu một chuỗi văn bản.

Cách sử dụng: LEFT(text, [num_chars])

Text: đoạn văn bản cần xem xét

[num_chars]: lượng ký tự bạn muốn lấy

Thí dụ 6: Trong trường hợp sau, danh sách sản phẩm cần phân tách mã sản phẩm ra khỏi các thông tin khác và để chúng vào cột riêng biệt. Dưới đây là công thức bạn có thể dùng:

=LEFT(A2,6)

A2: đoạn văn bản cần phân tách

6: số lượng ký tự bạn muốn lấy từ bên trái

Kết hợp giữa hàm Left và Find

Bằng cách ghép nối từ hai đến ba hàm, bạn có thể thực hiện phân tích dữ liệu nâng cao.

Thí dụ 7: Quan sát số và tên của những nhân viên được liệt kê trong cùng một cột, chúng ta cần tách số ra.

Khó khăn nằm ở chỗ không phải số nào cũng có cùng một độ dài – một số có ba chữ số, trong khi số khác có năm.

Chia công thức ra làm hai bước, bước đầu tiên là xác định độ dài chữ số cần lấy ra, bạn có thể dùng hàm FIND trong bước này.

Bước 1:

FIND(” “,A2) = 7

Trong trường hợp find_text, hãy thêm một dấu cách “ “ để chỉ ra việc chúng ta đang tìm khoảng trống trong đoạn văn bản.

A2 đại diện cho văn bản gốc.

Với công thức này, bạn sẽ biết được độ dài cần tách dựa vào vị trí của khoảng trống.

Bước 2:

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

Lưu ý rằng khi áp dụng hàm FIND vào việc xác định số ký tự cần lấy, kết quả sẽ như bạn mong đợi.

Chức năng Hàm LEN

Hàm này dùng để tính số lượng ký tự có trong văn bản.

Cách sử dụng: LEN(text)

LEN là hàm được dùng phổ biến bởi nhiều người chuyên nghiệp trong Excel.

Thí dụ 8: Trong Thí dụ dưới đây, ta có địa chỉ email trong một cột và muốn biết chúng chứa bao nhiêu ký tự.

LEN(“Lillie.Merrit@xyz.com”) = 21

Chú ý: LEN cũng tính cả khoảng cách.

Kết hợp hàm Len và Left

Thí dụ 9: Sử dụng lại địa chỉ email từ Thí dụ trước, giờ đây hãy tách riêng phần tên của người công nhân từ địa chỉ email đó.

=LEFT(A2,LEN(A2)-8)

A2 là văn bản chứa địa chỉ email

LEN(A2): tính tổng số ký tự trong email

-8: loại bỏ số ký tự phần “@xyz.com”

Bằng cách này, chúng ta sử dụng hàm Len để đếm tổng số ký tự và trừ đi 8 để biết được số ký tự của tên cần tách ra.

Chức năng Hàm LOWER

Hàm này chuyển đổi toàn bộ ký tự trong văn bản sang dạng chữ thường.

Cách sử dụng: LOWER(text)

Thí dụ 10: Trong Thí dụ này, ta có một đoạn văn bản viết HOA, giờ đây cần phải biến chúng thành chữ thường.

=LOWER(“HELLO JAY GOOD MORNING..!!”) = hello jay good morning..!!

Chức năng Hàm MID

Hàm này được dùng để trích xem xét các giá trị nằm giữa chuỗi văn bản theo ý muốn của người dùng.

Cách sử dụng: MID(text,

Text: nội dung văn bản cần xem xét

Start_num: vị trí khởi đầu để tách ký tự

Num_chars: số lượng ký tự muốn lấy ra

Ví dụ số 11: List sản phẩm với thông tin về tên công ty, tên và mã sản phẩm. Mục đích là tách riêng tên sản phẩm

=MID(A1,8,40)

A1: cell chứa dữ liệu

8: là điểm khởi đầu của tên sản phẩm. “Ballon” dài 6 ký tự cộng thêm dấu cách là 7 nên bắt đầu từ 8

40: số ký tự tối đa có thể thay đổi tùy sản phẩm

Chức năng Hàm PROPER

Hàm này chuyển đầu mỗi chữ cái trong từ thành chữ hoa

Ví dụ

=PROPER(“HELLO JaY GOoD MoRnINg..!!”) sẽ trở thành Hello Jay Good Morning..!!

Hàm REPLACE

Hàm này chuyên dùng để thay thế một phần của văn bản

Công thức: REPLACE(old_text, start_num, num_chars, new_text)

Old_text: đoạn văn bản cũ cần chỉnh sửa

start_num: vị trí bắt đầu muốn thay đổi trong văn bản

num_chars: lượng ký tự cần thay thế

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

Ví dụ 13: Từ Ví dụ 11 nếu bạn muốn thay mã sản phẩm thành không gian trống.

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

Hàm REPT

Hàm này cho phép lặp lại text hoặc ký hiệu theo số lần người dùng cài đặt

Ví dụ, lặp ký tự * trong một ô 10 lần, bạn dùng cú pháp sau:

=REPT(“*”,10) cho ra kết quả **********

Hàm REPT hỗ trợ trong việc tạo các biểu đồ từ dãy ký hiệu trong vùng ô do người dùng thiết lập.

Hàm này giúp tìm vị trí của chuỗi con nằm trong chuỗi khác

Công thức: SEARCH(find_text, within_text, [start_num])

find_text: là chuỗi, chữ cái, hoặc ký hiệu cần tìm kiếm (văn bản con)

within_text: là nội dung chứa chuỗi cần tìm

[start_num]: là điểm bắt đầu tìm kiếm không bắt buộc trong within_text

Ví dụ 14: Có danh sách tên công nhân, hãy xác định vị trí bắt đầu của họ của họ.

A2 = Lillie Merrit

=SEARCH(” “,A2) cho kết quả là 7

Vị trí 7 chính là nơi bắt đầu của họ công nhân trong A2

Biến thể: Nếu có nhiều dấu cách, sử dụng công thức:

=SEARCH(” “,E2,SEARCH(” “,E2)+1)

SEARCH xác định dấu cách đầu tiên và cộng 1 để tìm điểm khởi đầu của dấu cách kế tiếp

Dựa vào công thức đầu, “7” là vị trí dấu cách đầu, cộng thêm 1 tạo ra số 8 làm điểm bắt đầu cần tìm. Như vậy, ta hướng dẫn Excel tìm kiếm dấu cách bắt đầu từ vị trí 8 trong văn bản.

Hàm RIGHT

Hàm này dùng để tách ký tự bên phải của văn bản

Công thức: RIGHT(text, [num_chars])

Text: chuỗi văn bản

[num_chars]: số ký tự muốn tách ra

Ví dụ 15: Lấy ra họ của công nhân từ thông tin đầy đủ ở cột A, đặt ở cột B

Thông tin ở A2: Lillie.Merrit

=RIGHT(A2,6) cho ra Merrit

Khác biệt:

Nếu bạn không chắc lượng ký tự cần tách là bao nhiêu thì làm thế nào?

Công thức sau tự động xác định và tách họ:

=RIGHT(A2,LEN(A2)-SEARCH(“.”,A2))

Hàm LEN đếm tổng số ký tự và SERACh

Bạn có thể sử dụng hàm để thay thế ký tự hoặc văn bản theo yêu cầu.

Hàm SUBSTITUE

Công thức này cho phép bạn thay thế chuỗi ký tự cũ bằng chuỗi mới.

Cú pháp của nó là: SUBSTITUTE(text, old_text, new_text, [instance_num])

Đây là các thành phần của cú pháp:

Text đại diện cho văn bản gốc.

old_text là chuỗi cần được thay thế.

new_text là chuỗi thay thế mới.

[instance_num] là số thứ tự tùy chọn của chuỗi cần thay thế, nếu cần chỉ định.

Ví dụ thực tế số 16: Giả sử tên của chúng ta nằm ở cột A và chúng ta muốn chuyển khoảng trắng trong tên thường thành dấu gạch dưới “_”.

Dữ liệu trong A2 : Lillie.Merrit

Dùng công thức này: =SUBSTITUTE(A2,”.”,”_”) kết quả là Lillie_Merrit

Một biến thể khác của việc sử dụng hàm này trong ví dụ đưa ra khi tên, tên đệm và mã số đều có khoảng trắng. Ký tự gạch dưới “_” chỉ áp dụng cho phần tên, và giữ nguyên phần mã số.

Thực hiện công thức: =SUBSTITUTE(E2,” “,”_”,1)

Sự khác biệt duy nhất trong công thức này là thêm số 1 ở cuối, tức là chỉ thay thế khoảng trắng đầu tiên và bỏ qua những khoảng trắng khác.

Hàm TRIM

Hàm này giúp bạn loại bỏ những khoảng trống không cần thiết trong văn bản.

Cú pháp cần sử dụng: TRIM(text)

Dưới đây là một ví dụ số 17: Bỏ bớt các khoảng trắng thừa trong một chuỗi văn bản.

Công thức:=TRIM(“Hello Jay Good Morning..!!”) sẽ trả về Hello Jay Good Morning..!!, loại bỏ khoảng trắng dư thừa chỉ còn lại một khoảng trắng giữa các từ.

Hàm UPPER

Công cụ này chuyển đổi toàn bộ văn bản từ chữ thường sang chữ in hoa.

Cú pháp dùng là: UPPER(text)

Đặt “hi good morning” làm ví dụ, và chuyển nó sang dạng chữ hoa.

Khi dùng công thức =UPPER(“hi good morning”), kết quả trả về sẽ là HI GOOD MORNING

Kết luận

Việc sử dụng các hàm xử lý văn bản trong Excel rất quan trọng đối với các nhà phân tích, càng dữ liệu phức tạp càng cần sự hỗ trợ của chúng. Nếu bạn chưa quen sử dụng các công cụ này, hãy thực hành mỗi ngày trong công việc của bạn.

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop