Đánh giá lequocthai.com:
Thuật ngữ “ASCII” chỉ “American Standard Code for Information Interchange”, trong đó mỗi bit đại diện cho một ký tự khác nhau. ASCII bao gồm các số, chữ cái, dấu câu, ký tự đặc biệt và các ký tự không hiển thị.
Hàm CHAR rất hữu ích khi chúng ta muốn nhập một ký tự không có trên bàn phím. Nó cũng có thể dùng để tạo ký tự đại diện (wildcard). Ký tự đại diện là một dấu hiệu có thể thay thế cho một hoặc nhiều ký tự trong một biểu thức.
Cú pháp
Cú pháp của hàm CHAR như sau:
=CHAR(number)Đối số
`number` – là đối số bắt buộc. Đây là một số ASCII nằm trong khoảng từ 1 đến 255, tương ứng với một ký tự.
Các đặc điểm quan trọng của hàm CHAR
Mã ký tự trả về của hàm CHAR phụ thuộc vào hệ điều hành. Windows dùng bộ ký tự ANSI/ASCII, còn macOS (hệ điều hành Apple) dùng bộ ký tự Macintosh. Do đó, cùng một đối số `number` có thể cho kết quả khác nhau trên các hệ điều hành.
Các đặc điểm đáng chú ý khác:
- Kết quả của hàm CHAR luôn là một giá trị dạng văn bản.
- Nếu đối số `number` nằm ngoài phạm vi (nhỏ hơn 1 hoặc lớn hơn 255), Excel sẽ trả về lỗi #VALUE!.
- Đối số `number` không chấp nhận giá trị không phải số và sẽ trả về lỗi #VALUE!.
Ví dụ về hàm CHAR
Hàm CHAR thường được sử dụng kết hợp với các hàm Excel khác để thực hiện các kết quả mong muốn. Khi được sử dụng độc lập, nó chỉ trả về ký tự tương ứng với số nhập vào, như trong ví dụ dưới đây.
Cột B chứa giá trị đối số `number`, và cột C chứa công thức sử dụng hàm CHAR:
=CHAR(B3)
Cần lưu ý rằng khoảng cách giữa các ký tự hoa và thường chính xác là 32. Số cho ký tự “a” thường là 97, được tính bằng cách cộng 32 vào 65 – số cho ký tự “A” hoa.
Ví dụ 1: Địa chỉ gửi thư với ngắt dòng
Một trong những cách dùng CHAR được nói tới nhiều nhất là ngắt dòng. Trong Excel, việc chèn một dòng mới bên trong một ô được gọi là ngắt dòng. Chúng ta có thể di chuyển sang ô kế tiếp bằng phím Enter, nhưng để tạo một dòng mới trong cùng một ô, cần chèn ký tự ngắt dòng bằng hàm CHAR. Thực tế, việc này cũng dễ dàng như nhấn tổ hợp Alt + Enter, nhưng khi cần chèn nhiều ngắt dòng, cách này sẽ trở nên lộn xộn.
Tuỳ thuộc vào hệ điều hành, ký tự dùng để ngắt dòng sẽ khác nhau: CHAR(10) cho Windows và CHAR(13) (hoặc CHAR(10)) cho macOS. Ở đây chúng ta sẽ giải thích dựa trên phiên bản Windows – CHAR(10).
Giả sử chúng ta có một danh sách khách mời, bao gồm tên, địa chỉ, thành phố, quận, tiểu bang và mã zip (xem ở các cột A đến F). Chúng ta muốn tổng hợp tất cả các thông tin này thành một địa chỉ gửi thư duy nhất. Thực hiện thủ công sẽ mất nhiều thời gian, nhưng với hàm CHAR, chúng ta có thể hoàn thành trong vài phút.
Để ghép dữ liệu từ các cột lại thành địa chỉ, chúng ta chỉ cần chèn một ngắt dòng sau mỗi giá trị ô. Vì vậy công thức sẽ là: Tên (ngắt dòng) Địa chỉ (ngắt dòng) Thành phố (ngắt dòng) Quận (ngắt dòng) Tiểu bang (ngắt dòng) Mã zip.
Kết quả cuối cùng (địa chỉ gửi thư) sẽ trông như sau, rất gọn gàng:
Name
Address
City
County
State
Zip
Bây giờ, hãy viết công thức mà Excel hiểu được. Vì chúng ta sẽ dùng nhiều hàm và chuỗi văn bản trong một công thức, nên cần nối chúng lại bằng một trong các hàm nối chuỗi hoặc ký tự &. Hàm CONCATENATE (hoặc CONCAT) sẽ nối các chuỗi văn bản (bao gồm số, chuỗi, giá trị hoặc hàm) thành một chuỗi duy nhất. Vì vậy, công thức cuối cùng sẽ là:
=CONCAT(A2,CHAR(10),B2,CHAR(10),C2,CHAR(10),D2,CHAR(10),E2,CHAR(10),F2)
Hoặc chúng ta có thể dùng ký tự & để nối:
=A2&CHAR(10)&B2&CHAR(10)&C2&CHAR(10)&D2&CHAR(10)&E2&CHAR(10)&F2
Lưu ý:
Thay vì phải điều chỉnh chiều rộng cột và hàng để dữ liệu trông đẹp, bạn nên sử dụng tính năng Wrap Text của Excel. Sau khi nhập công thức, ô có thể hiển thị như sau:
Với tính năng Wrap Text, toàn bộ nội dung trong một ô sẽ được hiển thị dù nó vượt quá rìa ô. Để làm cho nó trông gọn hơn, chọn ô và nhấn nút Wrap Text trong nhóm Alignment trên thẻ Home.
Ví dụ 2: Tạo địa chỉ email cho nhân viên mới
Giả sử bạn là người phụ trách tuyển dụng và mỗi khi có nhân viên mới, bạn phải tạo địa chỉ email công ty cho họ. Thay vì làm thủ công, hãy dùng hàm CHAR để tự động hoá quá trình này.
Địa chỉ email bao gồm 2 ký tự đầu tiên của tên, 2 ký tự đầu tiên của họ, ký hiệu @, và cuối cùng là tên miền công ty. Ví dụ, nếu nhân viên mới là Mark Waycott, địa chỉ email sẽ là:
[email protected]Cột B chứa tên (first name) và cột C chứa họ (last name).
Bây giờ, hãy ghép các phần lại bằng hàm CHAR. Vì chúng ta cần 2 ký tự đầu tiên của tên và họ, chúng ta sẽ dùng hàm LEFT. Hàm LEFT lấy số ký tự xác định từ chuỗi bắt đầu từ phía trái.
Công thức trong Excel sẽ như sau:
=LEFT(B3,2)&LEFT(C3,2)&CHAR(64)&"companyabc.com"Giải thích: `LEFT(B3,2)` lấy 2 ký tự đầu của tên, `LEFT(C3,2)` lấy 2 ký tự đầu của họ, `CHAR(64)` trả về ký tự @, và cuối cùng chúng ta nối chuỗi tên miền trong dấu ngoặc kép.
Kết quả sẽ xuất hiện ở ô D3. Vì địa chỉ email thường được viết bằng chữ thường, chúng ta sẽ bao bọc toàn bộ công thức bằng hàm LOWER để chuyển về chữ thường:
=LOWER(LEFT(B3,2)&LEFT(C3,2)&CHAR(64)&"companyabc.com")
Ví dụ 3: Chèn dấu ngoặc kép trong công thức
Sử dụng dấu ngoặc kép (” “) trong công thức Excel có thể gây nhầm lẫn vì chúng thường được dùng để đánh dấu đầu và cuối một chuỗi văn bản. Nếu muốn in ra câu như:
The Marvel Movie "Iron Man" is a must watch.và phần tên phim được lấy từ một ô khác, chúng ta phải dùng dấu ngoặc kép bổ sung như ký tự escape.
Escape một ký tự nghĩa là chúng ta chỉ đạo Excel coi ký tự đặc biệt đó như văn bản thường. Do vậy, công thức sẽ là:
="The Marvel Movie """&B3&""" is a must watch."Để dễ hiểu hơn, chúng ta có thể viết cách nhau các dấu ngoặc kép như sau:
="The Marvel Movie """&B3&""" is a must watch."Ở đây, `&B3&` được bao quanh bởi ba dấu ngoặc kép liên tiếp để tạo ra một dấu ngoặc kép thực tế trong kết quả. Một cách gọn hơn là dùng hàm CHAR(34) (34 là mã ASCII của dấu ” ) để thay thế các dấu ngoặc kép bên trong:
="The Marvel Movie "&CHAR(34)&B3&CHAR(34)&" is a must watch."
Kết quả thu được sẽ giống như trước, nhưng cách viết tinh tế hơn – chứng tỏ sức mạnh của hàm CHAR.
Ví dụ 4: Loại bỏ ký tự không mong muốn
Trong ví dụ sau, chúng ta nhận được bảng giá các loại hoa cho một buổi tiệc. Khi tải về, mỗi mức giá đều có dấu đô la ($) phía trước, khiến việc cộng tổng trở nên khó khăn.
Việc xóa dấu $ thủ công ở cột C sẽ tốn rất nhiều thời gian. Thay vào đó, chúng ta có thể dùng hàm CHAR(36) (36 là mã ASCII của $) kết hợp với hàm SUBSTITUTE để thay thế $ bằng một chuỗi rỗng.
=SUBSTITUTE(C3,CHAR(36),"")
Ví dụ 5: Loại bỏ các ký tự đặc biệt khác nhau trong một chuỗi
Giả sử chúng ta tải về một danh sách tiêu đề sách từ Internet và nhận thấy có một số ký tự đặc biệt xen kẽ trong chuỗi. Để làm sạch dữ liệu, chúng ta sẽ áp dụng lại cách dùng CHAR và SUBSTITUTE như trong ví dụ trên.
Cột B chứa dữ liệu đã tải về. Như bạn thấy, có ba loại ký tự khác nhau xuất hiện.
Trước khi xây dựng công thức cuối cùng, chúng ta cần biết mã ASCII của các ký tự này:
Đầu tiên, loại bỏ ký tự đầu (ký tự có mã 154) bằng công thức:
=SUBSTITUTE(B3,CHAR(154),"")
Tiếp theo, dùng công thức lồng nhau để loại bỏ ký tự thứ hai (mã 156):
=SUBSTITUTE(SUBSTITUTE(B3,CHAR(154),""),CHAR(156),"")Cuối cùng, loại bỏ ký tự cuối cùng (mã 164) và có được công thức cuối cùng:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,CHAR(154),""),CHAR(156),""),CHAR(164),"")
Sau khi loại bỏ các ký tự không mong muốn, còn lại một vài dấu cách ở đầu và cuối chuỗi. Để loại bỏ chúng và chỉ để lại một khoảng trắng duy nhất giữa các từ, chúng ta bọc toàn bộ công thức bằng hàm TRIM:
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,CHAR(154),""),CHAR(156),""),CHAR(164),""))
CHAR và CODE
Bây giờ chúng ta đã nắm vững cách dùng hàm CHAR, hãy cùng khám phá hàm CODE, là phiên ngược lại của CHAR. Hàm CODE nhận vào một ký tự (dạng văn bản) và trả về mã số ASCII tương ứng.
Ví dụ, công thức sau trả về ký tự “%”:
=CHAR(37)Nếu muốn tìm mã của ký tự “%”, chúng ta dùng:
=CODE("%")
Thật là dễ dàng, đúng không?
Vậy là chúng ta đã khám phá hàm CHAR từ A đến Z. Hãy thực hành các công thức trên để trở thành một chuyên gia sử dụng hàm CHAR. Khi bạn đã thành thạo hàm này, chúng ta sẽ chuyển sang một hàm Excel khác để tiếp tục nâng cao kỹ năng!
