Excel's Text Functions - Các hàm xử lý văn bản và chuỗi

[BNTT]

Vài dòng tâm sự

Trước giờ, trong diễn đàn này đã có nhiều bài viết về các hàm Excel, tổng hợp, liệt kê các hàm Excel. Tuy nhiên, nếu gọi là tổng hợp theo hệ thống, phân loại theo từng chủ đề thì chưa có.

Với một mong muốn là đóng góp được chút gì cho GPE, tôi xin gửi đến các bạn chùm topic này. Gọi là chùm bởi vì nó có nhiều topic. Được tổng hợp từ cuốn sách Formulas and Functions with Microsoft Excel 2007 của Paul McFedries, và dịch từ phần Help của Excel.

Trong các topic sau đây, các hàm Excel được biên soạn theo từng chủ đề, từng loại. Thiết nghĩ rất cần thiết cho các bạn, nhất là các bạn mới làm quen với Excel.

Vì trình độ dịch thuật cũng như sự am hiểu về các hàm trong Excel là có giới hạn, nên sẽ không tránh được những sai sót. Rất mong các bạn đóng góp những ý kiến cũng như nói cho tôi biết những chỗ sai, để loạt topic này sớm được hoàn thiện.
​

​

​

​

​

 

[BNTT]

List of Excel’s Text Functions


Danh mục các hàm xử lý văn bản và chuỗi

ASC (text) : Chuyển đổi các ký tự double-byte sang các ký tự single-byte.

BATHTEXT (number) : Dịch số ra chữ (tiếng Thái-lan), rồi thêm hậu tố "Bath" ở phía sau.

CHAR (number) : Chuyển đổi một mã số trong bộ mã ANSI (có miền giá trị từ 1 - 255) sang ký tự tương ứng.

CLEAN (text) : Loại bỏ tất cả những ký tự không in ra được trong chuỗi

CODE (text) : Trả về mã số của ký tự đầu tiên chuỗi text

CONCATENATE (text1, text2, ...) : Nối nhiều chuỗi thành một chuỗi

DOLLAR (number, decimals) : Chuyển đổi một số thành dạng tiền tệ (dollar Mỹ), có kèm theo dấu phân cách hàng ngàn, và có thể làm tròn theo ý muốn.

EXACT (text1, text2) : So sánh hai chuỗi. Nếu giống nhau thì trả về TRUE, nếu khác nhau thì trả về FALSE. Có phân biệt chữ hoa và chữ thường.

FIND (find_text, within_text, start_num) : Tìm vị trí bắt đầu của một chuỗi con (find_text) trong một chuỗi (within_text), tính theo ký tự đầu tiên

FIXED (number, decimals, no_commas) : Chuyển đổi một số thành dạng văn bản (text), có hoặc không kèm theo dấu phân cách hàng ngàn, và có thể làm tròn theo ý muốn

LEFT (text, num_chars) : Trả về một hay nhiều ký tự đầu tiên bên trái của một chuỗi, theo số lượng được chỉ định

LEN (text) : Đếm số ký tự trong một chuỗi

LOWER (text) : Đổi tất cả các ký tự trong một chuỗi văn bản thành chữ thường

MID (text, start_num, num_chars) : Trả về một hoặc nhiều ký tự liên tiếp bên trong một chuỗi, bắt đầu tại một vị trí cho trước

PROPER (text) : Đổi ký tự đầu tiên trong chuỗi thành chữ in hoa, và đổi các ký tự còn lại thành chữ in thường

REPLACE (old_text, start_num, num_chars, new_text) : Thay thế một phần của chuỗi bằng một chuỗi khác, với số lượng các ký tự được chỉ định

REPT (text, times) : Lặp lại một chuỗi với số lần được cho trước

RIGHT (text, num_chars) : Trả về một hay nhiều ký tự tính từ bên phải của một chuỗi, theo số lượng được chỉ định

SEARCH (find_text, within_text, start_num) : Tìm vị trí bắt đầu của một chuỗi con (find_text) trong một chuỗi (within_text), tính theo ký tự đầu tiên

SUBSTITUTE (text, old_text, new_text, instance_num) : Thay thế chuỗi này bằng một chuỗi khác

T (value) : Trả về một chuỗi nếu trị tham chiếu là chuỗi, ngược lại, sẽ trả về chuỗi rỗng

TEXT (value, format_text) : Chuyển đổi một số thành dạng văn bản (text) theo định dạng được chỉ định

TRIM (text) : Xóa tất cả những khoảng trắng vô ích trong chuỗi văn bản, chỉ chừa lại những khoảng trắng nào dùng làm dấu cách giữa hai chữ

UPPER (text) : Đổi tất cả các ký tự trong chuỗi thành chữ in hoa

VALUE (text) : Chuyển một chuỗi thành một số
​

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​

 

[BNTT]

Hàm CHAR

Chuyển đổi một mã số trong bộ mã ANSI (có miền giá trị từ 1 - 255) sang ký tự tương ứng.

Cú pháp: =CHAR(number)

number : là một con số từ 1 đến 255, là mã số trong bảng mã ANSI.
​

Ví dụ: Để kết quả có được là ký tự bản quyền ©, chúng ta nhập: = CHAR(169)​

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​

 

[BNTT]

Hàm CODE

Hàm CODE() là ngược lại với hàm CHAR(), nghĩa là, cho 1 ký tự, CODE() sẽ cho biết mã số của ký tự đó. Nếu đối số của CODE() là một chuỗi, thì kết quả trả về sẽ là mã số của ký tự đầu tiên trong chuỗi đó.

Cú pháp: =CODE(text)

text : Là một ký tự hoặc một chuỗi (một câu)​

Ví dụ:

CODE("a") = 97

CODE("A") = 65​

​

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​

 

[BNTT]

Hàm LOWER

Hàm LOWER() đổi tất cả các ký tự trong một chuỗi văn bản thành chữ thường.
Hàm này thường được dùng khi bạn cần định dạng lại một câu văn (thường là do chép ở đâu đó dán vào trong Excel) mà trong đó, chữ thường chữ hoa được gõ hoặc bị thay đổi lung tung...

Cú pháp: =LOWER(text)

text : Là một chuỗi, hoặc tham chiếu đến một chuỗi cần định dạng​

Ví dụ: Công thức sau đây sẽ sửa cell A2 (Giải pháp Excel) thành toàn là chữ thường

=LOWER(A2) → giải pháp excel​

​

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​

 

[BNTT]

Hàm UPPER

Hàm UPPER() đổi tất cả các ký tự trong một chuỗi văn bản thành chữ in hoa.

Cú pháp: =UPPER(text)

text : là một chuỗi, hoặc tham chiếu đến một chuỗi cần định dạng.​

Ví dụ:

Công thức sau đây sẽ nối chuỗi văn bản trong cell A5 (Giải pháp) và cell B5 (Excel) lại với nhau, ở giữa có một dấu cách, và tất cả ký tự trong kết quả toàn là chữ in hoa.

=UPPER(A5) & " " & UPPER(B5) → GIẢI PHÁP EXCEL​

​

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​

 

[BNTT]

Hàm PROPER

Hàm PROPER() đổi ký tự đầu tiên trong một chữ của một chuỗi văn bản thành chữ in hoa, còn tất cả các ký tự còn lại trong chuỗi đó trở thành chữ thường.

Cú pháp: =PROPER(text)

text : Là một chuỗi, hoặc tham chiếu đến một chuỗi cần định dạng​

Ví dụ: Công thức sau đây sẽ định dạng lại chuỗi văn bản cell A7 (công cụ tuyệt vời của bạn) theo kiểu Proper:

=PROPER(A7) → Công Cụ Tuyệt Vời Của Bạn​

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​

 

[BNTT]

Hàm DOLLAR

Chuyển đổi một số thành dạng tiền tệ (dollar Mỹ), có kèm theo dấu phân cách hàng ngàn, và có thể làm tròn theo ý muốn.

Cú pháp: =DOLLAR(number [, decimals])

Number : Số cần chuyển sang dạng tiền tệ

Decimals : Số số thập phân, mặc định là 2, nếu là số âm thì sẽ làm tròn về bên trái​

Ví dụ:

= "Tổng chi phí ngoại tệ của năm 2007 là " & DOLLAR(1500670000, 0) → Tổng chi phí ngoại tệ của năm 2007 là $1,500,670,000

Ở đây, số tiền này đã được định dạng thành số nguyên (decimals = 0).​

​

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​

 

[BNTT]

Hàm FIXED

Chuyển đổi một số thành dạng văn bản (text), có hoặc không kèm theo dấu phân cách hàng ngàn, và có thể làm tròn theo ý muốn.

Cú pháp: =FIXED(number [, decimals] [, no_commas])

Number : Số cần chuyển sang dạng text

decimals : là số số thập phân, mặc định là 2, nếu là số âm thì sẽ làm tròn về bên trái

no_commas : TRUE hoặc FALSE, dùng TRUE nếu không muốn có dấu phân cách hàng ngàn, và FALSE (mặc định) thì có bao gồm cả dấu phân cách hàng ngàn
​

Bài tập : Bạn thử dùng hàm FIXED với con số 123456789,85 để ra lập công thức cho ra kết quả giống như cột B nhé.

​

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​

 

[BNTT]

Hàm TEXT

Chuyển đổi một số thành dạng văn bản (text) theo định dạng được chỉ định.

Cú pháp: =TEXT(number, format)

Number : là số cần chuyển sang dạng text

format: kiểu định dạng số hoặc ngày tháng năm​

Ví dụ:

Công thức sau sẽ dùng hàm AVERAGE() để tính số trung bình của mảng cell A1:A31, và dùng hàm TEXT để chuyển đổi kết quả ra dạng nhiệt độ

=”Nhiệt độ trung bình của lò phản ứng là “ & TEXT(AVERAGE(A1:A31), “#,##0.00°F”) → Nhiệt độ trung bình của lò phản ứng là 7,560.00°F​

​

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​

 

[BNTT]

Hàm TRIM

Xóa tất cả những khoảng trắng vô ích trong chuỗi văn bản, chỉ chừa lại những khoảng trắng nào dùng làm dấu cách giữa hai chữ.
Những khoảng trắng vô ích này có thể nằm ở bất kỳ đâu trong đoạn văn: ở đầu, ở cuối hoặc ở giữa...

Cú pháp: =TRIM(text)
text : chuỗi văn bản cần xóa những khoảng trắng vô ích.


Ví dụ:

Hình sau đây cho thấy công dụng của hàm TRIM.
Mỗi chuỗi văn bản trong khối A2:A7 đều có những khoảng trắng vô ích, nằm ở đầu, ở giữa hoặc ở cuối.
Tôi đặt hàm TRIM() ở cột C. Để các bạn thấy công dụng của hàm TRIM, tôi có dùng thêm hàm LEN() đặt ở cột B và D, để đếm số ký tự trong chuỗi trước và sau khi thực hiện hàm TRIM()

​

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​

 

[BNTT]

Hàm CLEAN

Dùng để xóa tất cả những ký tự không in ra được (nonprintable characters) trong chuỗi văn bản.
Hàm CLEAN() thường được dùng để gỡ bỏ những dấu ngắt đoạn, ngắt câu (có được do nhấn phím Enter), nghĩa là nối những đoạn bị xuống hàng lại với nhau.

Cú pháp: =CLEAN(text)

text: chuỗi văn bản cần xóa những ký tự không in ra được. (Những ký tự không in ra được là những ký tự có mã số từ 1 đến 31 trong bảng mã ANSI.)
​

Ví dụ: Hình sau đây cho thấy công dụng của hàm CLEAN.

​

​

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​

 

[BNTT]

Hàm REPT

Dùng để lập đi lập lại (nhân bản) một ký tự hoặc một từ, với số lần được định trước.

Cú pháp: =REPT(text, times)

text : ký tự (character), một con số hoặc một từ (string) cần được nhân bản

times: số lần lập đi lập lại của text​

Lưu ý:

• Nếu times = 0, hàm REPT() sẽ trả về một chuỗi rỗng
• Nếu text là một con số, REPT() sẽ làm tròn con số đó thành số nguyên

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​

 

[BNTT]

Hàm LEFT

Dùng để trích xuất phần bên trái của một chuỗi một hoặc nhiều ký tự tùy theo sự chỉ định.

Cú pháp: =LEFT(text [, num_chars])

text: chuỗi văn bản cần trích xuất ký tự
num_chars : số ký tự cần trích ra phía bên trái chuỗi text, mặc định là 1.​

Lưu ý:

• num_chars phải là số nguyên dương
• Nếu num_chars lớn hơn độ dài của chuỗi thì kết quả trả về sẽ là toàn bộ chuỗi

Ví dụ: =LEFT(“Karen Elizabeth Hammond”, 5) → Karen​

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​

 

[BNTT]

Hàm RIGHT

Dùng để trích xuất phần bên phải của một chuỗi một hoặc nhiều ký tự tùy theo sự chỉ định.

Cú pháp: =RIGHT(text [, num_chars])

text: chuỗi văn bản cần trích xuất ký tự
num_chars : số ký tự cần trích ra phía bên phải của chuỗi text, mặc định là 1​

Lưu ý :

• num_chars phải là số nguyên dương
• Nếu num_chars lớn hơn độ dài của chuỗi thì kết quả trả về sẽ là toàn bộ chuỗi text

Ví dụ =RIGHT(“Karen Elizabeth Hammond”, 7) → Hammond​

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​

 

[BNTT]

Hàm MID

Công dụng: Dùng để trích xuất một chuỗi con (substring) từ một chuỗi

Công thức: =MID(text, start_num, num_chars])
___text: chuỗi văn bản cần trích xuất
___start_num: vị trí bắt đầu trích ra chuỗi con, tính từ bên trái sang
___num_chars: số ký tự của chuỗi con cần trích ra

- num_chars phải là số nguyên dương
- start_num phải là số nguyên dương
- Nếu start_num lớn hơn độ dài của chuỗi thì kết quả trả về sẽ là chuỗi rỗng


Ví dụ:___=MID(“Karen Elizabeth Hammond”, 7, 9) → Elizabeth​

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​

 

[BNTT]

Hàm FIND và Hàm SEARCH

Công dụng: Dùng để tìm vị trí bắt đầu của một chuỗi con (substring) trong một chuỗi

Công thức:

__ =FIND(find_text, within_text [, start_num])
__ =SEARCH(find_text, within_text [, start_num])

___find_text: chuỗi văn bản cần tìm (chuỗi con)

___within_text: chuỗi văn bản chứa chuỗi cần tìm (chuỗi mẹ)

___start_num: vị trí bắt đầu tìm trong chuỗi within_text (mặc định là 1)


Một số lưu ý:

- Kết quả của hai hàm này là một con số, chỉ vị trí bắt đầu (tính từ start_num) của find_text trong within_text

- Dùng SEARCH() khi muốn tìm một chuỗi bất kỳ. Ví dụ: SEARCH(“e”, “Expenses”) sẽ cho kết quả là 1.

- Dùng FIND() khi muốn tìm chính xác một chuỗi có phân biệt chữ hoa, chữ thường. Ví dụ: FIND(“e”, “Expenses”) sẽ cho kết quả là 4.

- Nếu không tìm thấy find_text, hàm sẽ báo lỗi #VALUE

- Có thể dùng những ký tự đại diện như *, ? trong find_text của hàm SEARCH()

- Với hàm SEARCH(), nếu muốn tìm chính ký tự * hoặc ? thì gõ dấu ~ trước ký tự đó ( ~* hoặc là ~?)

​

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​

 

[BNTT]

Hàm tìm kiếm một chuỗi con (tt)


Trích xuất họ, tên đệm và tên (ví dụ với tên tiếng Anh, phần tên đệm được viết tắt)

Đây cũng là dạng bài toán đã được rất nhiều bạn hỏi trên GPE.
Cách làm giống như bài Trích xuất họ và tên ở trên, tuy nhiên có khác một chút, để trích thêm phần tên đệm.

Giả sử Họ và Tên (full name) nằm ở cell A2, và đang có giá trị là Karen E. Hammond

Đầu tiên, như bài trên, dùng công thức sau để tách phần Tên (first name):

=LEFT(A2, FIND(" ", A2) - 1) → Karen​

Công thức FIND(" ", A2) sẽ cho kết quả là 6, là vị trí của khoảng trắng đầu tiên (sau chữ Karen).
Để tìm vị trí của khoảng trắng thứ hai, thì bạn phải gán vị trí bắt đầu tìm (start_num) là 7, hoặc là bằng kết quả của FIND(" ", A2) cộng thêm 1:

=FIND(" ", A2, FIND(" ",A2) + 1)
​

Rồi dùng kết quả của công thức này làm tham số cho hàm RIGHT() để trích ra phần Họ (last name):

=RIGHT(A2, LEN(A2) - FIND(" ", A2, FIND(" ", A2) +1)) → Hammond​

Để trích phần tên đệm, dùng hàm FIND() để tìm vị trí của dấu chấm (.) rồi đưa vào trong công thức của hàm MID() để tìm ký tự đứng trước dấu chấm:

=MID(A2, FIND(".", A2) - 1, 1) → E​

Hình sau đây là một minh họa cho phần vừa trình bày ở trên:

​

Xác định tên của cột (Determining the Column Letter)

Trong Excel có hàm COLUMN(), cho ra kết quả là số của cột (ví dụ, gõ hàm này trong cột B thì kết quả sẽ là 2).
Nhưng đôi khi bạn muốn kết quả là tên của cột chứ không muốn đó là con số (B chứ không phải là 2), thì làm sao?

Đây là một vấn đề đòi hỏi sự khôn khéo một chút, vì tên cột trong bảng tính chạy từ A đến Z, từ AA đến AZ... và cho tới tận cùng là XFD (!)

Có một hàm giúp chúng ta tìm địa chỉ tuyệt đối của một cell, đó là hàm CELL("address"), ví dụ $A$2, hoặc $B$10...

Hàm CELL(info_type [,reference])
Với info_type là một tham số đã được định nghĩa (sẽ nói kỹ hơn trong những bài sau)
Và reference là cell mà bạn chỉ định, nếu bỏ trống thì Excel sẽ lấy ngay cái cell có chứa công thức CELL().
Trong bài này, để tìm địa chỉ tuyệt đối của một cell, chúng ta sẽ dùng công thức CELL() với info_type là "address"

Mời bạn xem thêm về hàm CELL()

​

Tinh ý một chút, ta thấy tên của cột chính là những chữ cái nằm giữa hai dấu dollar ($) trong cái địa chỉ tuyệt đối này.

Bắt đầu làm nhé: dùng hàm MID() trích ra chữ cái từ vị trí thứ 2 trong địa chỉ tuyệt đối của cell:

=MID(CELL("Address"), 2, num_chars)
​

Cái khó là cái num_chars này đây, vì tên cột thì có thể là 1, 2, hoặc 3 ký tự (ví dụ: A, AA hoặc AAA). Vận dụng hàm FIND thôi:

FIND("$", CELL("address"”,A2), 3) - 2
​

Giải thích chút nhé: Dùng hàm FIND(), tìm vị trí của dấu $ trong cái địa chỉ tuyệt đối của cell, và bắt đầu tìm từ vị trí thứ 3 trong cái địa chỉ này.

Tại sao phải trừ đi 2 ? Công thức trên sẽ chỉ ra vị trí (là một con số) của dấu $ thứ hai trong địa chỉ tuyệt đối của cell, tức là cái dấu $ phía sau tên cột,
phải trừ đi 2 tức là trừ bớt đi 2 cái $, lúc này kết quả sẽ chính là số ký tự của tên cột (1 chữ, 2 chữ hoặc 3 chữ)

Bây giờ, công thức hoàn chỉnh sẽ như sau:

=MID(CELL("Address"), 2, FIND("$", CELL("address"), 3) - 2)
​

Công thức này áp dụng cho chính cell chứa công thức.
Nếu muốn tìm tên cột tại một cell nào đó, bạn chỉ việc thêm địa chỉ (hoặc một cái gì đó tham chiếu đến địa chỉ này) của cell muốn tìm vào phía sau cái "address"

Ví dụ, muốn tìm tên của cell AGH68, bạn gõ:

=MID(CELL("Address", AGH68), 2, FIND("$", CELL("address", AGH68), 3) - 2) → AGH
​

 

[BNTT]

Có nhiều chương trình có khả năng tìm kiếm một số đoạn văn và thay thế nó bằng đoạn văn khác.
Excel cũng có khả năng làm chuyện đó bằng cách dùng hàm. Đó là hàm REPLACE và hàm SUBSTITUTE.



Hàm REPLACE

Công dụng: Dùng để thay thế một phần của chuỗi bằng một chuỗi khác, dựa vào số ký tự được chỉ định

Công thức: =REPLACE(old_text, start_num, num_chars, new_text)
___old_text: chuỗi văn bản cần được xử lý
___start_num: vị trí bắt đầu tìm cái sẽ thay thế, tính từ bên trái sang
___num_chars: số ký tự của chuỗi cần được thay thế
___new_text: chuỗi văn bản sẽ thay thế cho số ký tự đã chọn bởi start_num và num_chars

Cái khó của hàm này là xác định được bởi start_num và num_chars. Làm sao biết được bắt đầu từ đâu và thay thế bao nhiêu chữ? Tôi gợi ý nhé:

- Bạn dùng hàm FIND() hoặc SEARCH() để xác định vị trí bắt đầu (start_num)

- Dùng hàm LEN() để xác định số ký tự của chuỗi sẽ được thay thế (num_chars)

Ví dụ: đế thay số 2007 bằng 2008 trong câu Expense Budget for 2007

Dùng công thức như sau:

=REPLACE(A1, FIND("2007", A1), LEN("2007"), "2008")→ Expense Budget for 2008​

Với A1 = Expense Budget for 2007

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​

​

 

[BNTT]

Hàm SUBSTITUTE

Công dụng: Dùng để thay thế một chuỗi này bằng một chuỗi khác. Hàm này cũng tương tự hàm REPLACE(), nhưng dễ sử dụng hơn.

Công thức: =SUBSTITUTE(text, old_text, new_text [,instance_num])
___text: chuỗi văn bản gốc, cần được xử lý
___old_text: chuỗi văn bản cần được thay thế
___new_text: chuỗi văn bản sẽ thay thế vào
___instance_num: số lần thay thế old_text bằng new_text, nếu bỏ qua thì tất cả old_text tìm được sẽ được thay thế bằng new_text

Ví dụ: đế thay số 2007 bằng 2008 trong câu Expense Budget for 2007

Dùng công thức như sau:

=SUBSTITUTE("Expense Budget for 2007", "2007", "2008") → Expense Budget for 2008​

Xem thêm: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản​