-
Làm thế nào để trình bày số liệu dưới định dạng dễ đọc hơn trong Microsoft Excel? Có cách nào để kết hợp số liệu với văn bản và các kí tự đặc biệt trong Excel? Nếu bạn đang băn khoăn những điều trên thì hàm Text trong Excel chính là câu trả lời. Hãy cùng Gitiho tìm hiểu về sự lợi hại của thủ thuật này nhé.
Tóm Tắt
Hàm Text trong Excel – Công thức và cách sử dụng căn bản
Hàm Text trong Excel giúp người dùng chuyển đổi giá trị số sang văn bản bằng một cú pháp đơn giản:
=TEXT(giá trị, định dạng_văn bản)
Trong đó:
Giá trị: Giá trị số cần chuyển đổi sang văn bản.
Định dạng văn bản: Định dạng cần hiển thị.
Để thấy rõ hơn sự lợi hại của hàm Text, hãy cùng áp dụng vào một tình huống cụ thể. Nếu bạn muốn chuyển đổi dữ liệu ngày/tháng/năm sang định dạng văn bản thông thường, bạn có thể dùng công thức như sau:
Với sự lợi lại của mình, hàm Text trong Excel không chỉ giúp ta thay đổi định dạng số liệu, mà còn giúp kết hợp số liệu với văn bản và các kí tự đặc biệt một cách nhanh chóng và chính xác.
Mã định dạng hàm Text trong Excel
Để sử dụng đúng cách hàm Text trong Excel, chúng ta cần nắm chắc các mã định dạng được sử dụng. Sau đây là danh sách những mã định dạng cơ bản và thông dụng nhất trong Excel.
Mã
Mô tả
Định dạng mã mẫu
0
Mã thể hiện các chữ số 0 vô nghĩa
#.00 tính đến chữ số thập phân thứ 2.
Ví dụ: Nhập 1.5, kết quả hiện thị 1.50
#
Mã ngăn không hiện thị thêm chữ số 0
#.## tính đến chữ số thập phân thứ 2
Ví dụ: Nhập 1.5, kết quả hiện thị 1.5
Nhập 1.555,
kết quả hiện thị 1.56
?
Mã để khoảng trống cho chữ số 0 vô nghĩa nhưng không biểu diễn chúng.
Thường được dùng để sắp xếp các số trong cột tại một điểm thập phân
#.?? biểu diễn tối đa 2 chữ số thập phân và căn nhập cho các điểm thập
phân trong một cột.
.
Mã biểu diễn điểm thập phân
###,###.## biểu diễn số có 6 chữ số phân tách hàng nghìn và các chữ số
thập phân
,
Mã biểu diễn phân cách hàng nghìn
Đồng thời, ta có thể sử dụng các kí tự sau đây để biểu thị ý nghĩa số liệu rõ hơn mà không làm thay đổi kết quả trả về.
Kí tự
Mô tả
+ và –
Dấu cộng và trừ
()
Dấu ngoặc đơn
:
Dấu hai chấm
‘
Dấu nháy
{}
Dấu ngoặc nhọn
<>
Dấu nhỏ hơn và lớn hơn
=
Dấu bằng
/
Dấu gạch chéo
Để việc sử dụng hàm Text trong Excel trở nên thuận tiện hơn, sau đây là tổng hợp công thức chi tiết và kết quả định dạng tương ứng.
A
B
C
1
Giá
trị gốc
Giá
trị định dạng
Công
thức
2
1.5
1.50
=TEXT(A2,“0.00”)
3
150%
=TEXT(A2,”#%”)
4
$1.50
=TEXT(A2,“$#,##0.00”)
5
+$1.50
=TEXT(A2,”+$#,##0.00;-$#,##0.00;$0.00”)
6
-$1.50
=TEXT(A2,“-$#,##0.00;-$#,##0.00;$0.00”)
7
1½
=TEXT(A2,”#?/?”)
8
1.50E+00
=TEXT(A2,”0.00E+00”)
9
~2!
=TEXT(A2,”~#!”)
Ngoài ra, dưới đây là danh sách các mã định dạng chúng ta có thể sử dụng với hàm Text trong Excel.
Mã định dạng
Mô tả
Ví dụ
d
(day)
Ngày
d – 1 hoặc 2 chữ số, không chứa số 0 ở đầu (1 đến 31)
dd – 2 chữ số, có chứa số 0 ở đầu (01 đến
31)
ddd – 3 kí tự viết tắt (Mon – Sun)
m
(month)
Tháng
m – 1 hoặc 2 chữ số, không chứa số 0 ở đầu (1 đến 12)
mm – 2 chữ số, có chứa số 0 ở đầu (01 đến
12)
mmm
– tên tháng viết tắt (Jan-Dec)
mmmm
– tên tháng viết đầy đủ
y
(year)
Năm
yy- 1 hoặc 2 chữ số trong năm (01 là 2001, 21 là 2021)
yyyy – 4 chữ số trong năm (2001, 2021)
h
(hour)
Giờ
h – 1 hoặc 2 chữ số không chứa chữ số 0 ở đầu (1-60)
hh -2 chữ số chứa chữ số 0 ở đầu (01-60)
m
(minute)
Phút
m – 1 hoặc 2 chữ số không chứa chữ số 0 ở đầu (1-60)
mm – 2 chữ số chứa chữ số 0 ở đầu (01-60)
s
(second)
Giây
s – 1 hoặc 2 chữ số không chứa chữ số 0 ở đầu (1-60)
ss – 2 chữ số chứa chữ số 0 ở đầu (01-60)
Cụ thể hơn, ta có thể nhìn vào bảng ví dụ sau đây hiển thị các định dạng cho một ngày cụ thể.
A
B
C
1
Ngày
gốc
Ngày
được định dạng
Công
thức
2
10/03/2021
Mar 10 2021
=TEXT(A2,“mmm dd yyyy”)
3
Wednesday 10 March, 2021
=TEXT(A2,“dddd dd mmmm, yyyy”)
4
10-Mar-21
=TEXT(A2,“d-mmm-yy”)
5
Wednesday
=TEXT(A2,”dddd”)
Cách sử dụng hàm Text trong Excel – Công thức mẫu
Kết nối văn bản và số (hoặc ngày) theo định dạng tùy chỉnh
Trong quá trình phân tích dữ liệu bằng Excel, chúng ta không dừng lại ở việc tính toán các số liệu đơn thuần mà còn qua đó đưa ra các kết luận về ý nghĩa của số liệu. Để làm được điều này, bên cạnh cách sử dụng hàm Concatenate để nhập văn bản, ta có thể dùng hàm Text trong Excel để biểu thị ngày số (hoặc ngày) theo định dạng mong muốn.
Xem thêm: Cách sử dụng hàm Text Trong Excel: 19 Cách Giúp Xử Lý Văn Bản Hiệu Quả
Định dạng số bên trong một chuỗi văn bản
Giả sử ta cần tính tổng giá đơn hàng dựa trên giá hàng ở ô A2, số lượng hàng ở ô B2 và giảm tỉ lệ ở ô C2. Cách đơn giản nhất là sử dụng công thức: =A2*B2*(1-C2). Sau đó, ta chèn thêm câu diễn giải: “Tổng giá đơn hàng là”. Ta muốn biểu thị giá trị dữ liệu dưới dạng tiền tệ, kết hợp dấu phân cách hàng nghìn và 2 chữ số thập phân.
Giả sử ta cần tính tổng giá đơn hàng dựa trên giá hàng ở ô A2, số lượng hàng ở ô B2 và giảm tỉ lệ ở ô C2. Cách đơn giản nhất là sử dụng công thức: =A2*B2*(1-C2). Sau đó, ta chèn thêm câu diễn giải: “Tổng giá đơn hàng là”. Ta muốn biểu thị giá trị dữ liệu dưới dạng tiền tệ, kết hợp dấu phân cách hàng nghìn và 2 chữ số thập phân.
Để Excel trả về kết quả dữ liệu đúng như mong muốn, ta thực hiện các bước sau:
- Nhập công thức tính A2*B2*(1-C2) vào đối số thứ nhất của hàm Text.
- Nhập mã định dạng tương ứng trong đối số thứ hai.
- Nối hàm Text với chuỗi bằng hệ thống các kí hiệu hoặc hàm Concatenate.
Công thức như sau:
=”Tổng giá đơn hàng là “&TEXT(A2*B2*(1-C2), “$###,###.00”)
Hoặc
=CONCATENATE(“Tổng giá đơn hàng là “, TEXT(A2*B2*(1-C2), “$###,###.00”))
Kết hợp văn bản và ngày tháng theo định dạng mong muốn
Khi muốn diễn đạt một câu hoàn chỉnh kết hợp với hàm Today hoặc Now để thể hiện ngày ở thời điểm hiện tại, ta có thể sử dụng hàm Text và hàm Concatenate như sau:
=CONCATENATE(“Today is “, TEXT(TODAY(),”dddd d mmm, yyyy”))
Hoặc
=”Today is “&TEXT(TODAY(),”dddd d mmm, yyyy”)
Lưu ý: Phụ thuộc vào ngôn ngữ cài đặt của Excel của người dùng, kết quả trả về có thể không khớp với ngôn ngữ cần hiển thị trong văn bản. Để chỉnh sửa điều này, ta cần thay đổi ngôn ngữ cài đặt của Excel.
Xem thêm: Hướng dẫn chuyển đổi định dạng date thành text trong Excel bằng hàm TEXT và không sử dụng công thức
Thêm số 0 đứng đầu mỗi số với độ dài ngắn khác nhau
Nếu bạn muốn giữ nguyên chữ số 0 ở đầu một dãy số, một cách đơn giản là sử dụng mã định dạng chứa chữ số 0, ví dụ như “00000”, đưa vào hàm Text. Giả sử trong trường hợp biểu diễn số có 5 chữ số với chữ số 0 ở đầu, ta có thể áp dụng công thức:
=TEXT(A2,”00000″)
Trong đó, A2 là ô chứa giá trị gốc.
Chuyển giá trị thành số điện thoại theo một định dạng nhất định
Để dễ dàng biểu thị số điện thoại trong Excel, ta có thể sử dụng hàm Text như sau:
=TEXT(A2,”###-####”)
hoặc trong trường hợp biểu thị cả dãy số điện thoại đầy đủ 10 chữ số bao gồm mã khu vực, ta dùng công thức hàm Text dưới đây:
=TEXT(A2,”[<=9999999]###-####;(###) ###-####”)
Hàm Text trong Excel không thực hiện được – Nguyên nhân và cách khắc phục
Để thực hiện hàm Text trong Excel, chúng ta chỉ cần nhập giá trị số và định dạng văn bản cần chuyển đổi, do vậy việc sử dụng dễ hơn phần lớn các hàm khác. Cũng chính vì cấu trúc hàm như vậy, đa số các lỗi hàm Text đến từ việc nhập sai mã định dạng.
Hàm Text trả về lỗi #NAME?
Nếu người không dùng dấu ngoặc kép “” ngoài mã định dạng, Excel sẽ hiển thị “#NAME?” thay vì giá trị cần tìm.
Ví dụ:
=TEXT(A2, dd/mm/yy) bị lỗi “#NAME?” do người dùng không dùng dấu “” cho mã định dạng dd/mm/yy
=TEXT(A2,”dd/mm/yy”) là cách nhập đúng
Xem thêm: Giải đáp lỗi #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME?, #NULL! trong Excel và cách sửa
Hàm Text yêu cầu mã định dạng ngày và giờ cụ thể
Ví dụ:
=TEXT(A2,”dd/mm/yy”) được sử dụng phổ biến cho hầu hết các quốc gia trên thế giới. Tuy nhiên, ở một số quốc gia có những cách sử dụng từ ngữ khác, mã định dạng cũng phải thay đổi.
=TEXT(A2,”tt/mm/jj”) là công thức đúng trong Excel phiên bản Đức, trong đó chúng ta sử dụng “t” và “j” thay cho “d” và “y”.
Trên đây là những kiến thức về hàm Text trong Excel mà Gitiho muốn gửi đến các bạn. Qua bài viết này, các bạn có thể tiết kiệm thời gian qua việc chuyển đổi giá trị số sang bất kì định dạng văn bản nào mong muốn một cách tự động thay vì nhập thủ công. Để tối ưu hóa việc sử dụng hàm Text trong Excel, đừng quên kết hợp với những hàm thông dụng khác như IF, VLOOKUP, INDEX và MATCH,… bạn nhé.
Gitiho hi vọng các bạn sẽ sử dụng thủ thuật này thật thành công!
Bài viết tham khảo khác:
Hướng dẫn cách dùng hàm index trong Excel chi tiết nhất
Hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel
Hướng dẫn cách sắp xếp ngày trong Excel
Hướng dẫn cách dùng hàm VLOOKUP qua các ví dụ từ cơ bản đến nâng cao
HƯỚNG DẪN CÁCH SỬ DỤNG HÀM IF VỚI NHIỀU ĐIỀU KIỆN: AND, OR, HÀM IF LỒNG NHAU VÀ HƠN THẾ
Cùng tham gia cộng đồng hỏi đáp về chủ đề Tin học văn phòng
