Hàm TEXT là một hàm được dùng thường xuyên trên ứng dụng Excel, nó có công dụng chuyển đổi chất lượng của số thành một chuỗi văn bản và có khả năng định dạng những văn bản ấy. Để tìm hiểu rõ hơn về công thức và cách sử dụng hàm TEXT trong Excel hiệu quả, mời bạn theo dõi nội dung sau đây nhé.
Bạn đang xem bài viết: Hàm TEXT trong Excel là gì? Cách sử dụng hàm TEXT trong Excel
Table of Contents
Hàm TEXT là gì? Ứng dụng của hàm TEXT trong Excel
Hàm Text là hàm được sử dụng để chuyển đổi thành quả số sang chuỗi văn bản ở một định dạng rõ ràng.
Xem thêm: Hướng dẫn thực hiện phép chia trong Excel có ví dụ
app của hàm TEXT trong Excel:
- Hiển thị số theo cách dễ đọc hơn hoặc theo một định dạng tốt hơn cho người dùng.
- Định dạng được ngày tháng trong tính toán.
- kết hợp các số hoặc ngày tháng với văn bản hoặc các kí tự cụ thể.
Cú pháp hàm TEXT và bảng mã hàm TEXT trong Excel
Cú pháp hàm
Cú pháp hàm TEXT: =TEXT(value, format_text)
Trong đó:
- Value: Là thành quả số cần chuyển sang văn bản.
- Format_text: Là định dạng cần sử dụng, mang lại thể thức của mã định dạng được đặt trong dấu ngoặc kép, như “mm/dd/yy”.
Lưu ý: Hàm Text thích hợp với hầu hết các phiên bản Excel 2016, Excel 2013, 2010, 2007, 2003 và thấp hơn.
Mã định dạng hàm TEXT trong Excel
Bảng phía dưới lên danh sách những mã định dạng căn bản nhất:
| Mã | miêu tả | Định dạng mã mẫu |
| 0 | Chữ số thể hiện những số 0 vô nghĩa |
|
| # | Dấu ngăn không biểu diễn thêm số 0 |
|
| ? | Dấu để chỗ trống cho số 0 vô nghĩa tuy nhiên không biểu diễn chúng. hay được sử dụng để phân bổ các số trong cột tại một điểm thập phân | #.?? Biểu diễn tối đa 2 phần thập phân và căn nhập cho các điểm thập phân trong một cột. |
| . | Điểm thập phân | ###,###.## biểu hiện dấu phân tách hàng nghìn và 2 số thập phân. |
| , | Ngăn cách hàng nghìn | ###,###.## biểu hiện dấu phân tách hàng nghìn và 2 số thập phân. |
Khi sử dụng hàm TEXT trong Excel với ngày và giờ bạn có thể sử dụng mã định dạng sau:
| Mã định dạng | miêu tả | VD |
| d (day) | Ngày của tháng hoặc ngày trong năm |
|
| m (month) | Tháng (được sử dụng như một phần của ngày) |
|
| y (year) | Năm |
|
| h (hour) | Giờ |
|
| m (minute) | Phút |
|
| S (second) | giây |
|
Cách sử dụng hàm TEXT trong Excel
Dùng hàm TEXT để kết nối văn bản và số (hoặc ngày) theo định dạng tùy chỉnh
ví dụ 1: Định dạng số bên trong một chuỗi văn bản
Bước 1: Trong bảng tính Excel bạn điền hàm =”Giá của chúng ta là”&TEXT(A7*B7*(1-C7), “$###,###.00”) vào ô tham chiếu muốn hiển thị kết quả, như hình ảnh minh họa.
Mẹo: Bạn cũng có thể sử dụng hàm CONCATENATE để thực hiện tương tự. rõ ràng ở VD này sẽ là =CONCATENATE(“Giá của chúng ta là”,TEXT(A7*B7*(1-C7), “$###,###.00”))
ví dụ minh họa hàm TEXT.
Bước 2: Bạn nhấn phím Enter để hiển thị kết quả.
Kết quả hiển thị minh họa hàm TEXT.
ví dụ 2: kết hợp văn bản và ngày tháng theo định dạng ngày hôm nay.
Bước 1: Trong bảng tính Excel bạn nhập hàm =”Hôm nay là”&TEXT(TODAY(),”dddd d mmm, yyyy”) vào ô tham chiếu muốn hiển thị kết quả, như hình minh họa.
Mẹo: Bạn cũng có thể sử dụng hàm CONCATENATE để thực thi tương tự. rõ ràng ở VD này sẽ là =CONCATENATE(“Hôm nay là”,TEXT(TODAY(),”dddd d mmm, yyyy”)).
VD minh họa hàm TEXT.
Bước 2: Bạn nhấn phím Enter để hiển thị kết quả.
Nhấn Enter để hiển thị kết quả.
Sử dụng hàm TEXT để thêm số 0 đứng đầu mỗi số với độ dài ngắn khác nhau
Lưu ý: Microsoft Excel sẽ tự động xóa những số 0 đứng đầu mỗi con số trong một ô và điều này không có ảnh hưởng gì trong hầu hết các hoàn cảnh.
Ở đây, mình sẽ hướng dẫn bạn giữ lại những số 0 đứng đầu rất đơn giản.
Bước 1: Trong bảng tính Excel bạn nhập hàm =TEXT(ô tham chiếu dữ liệu, số lượng số 0 mong muốn lấy) vào ô tham chiếu mong muốn hiển thị kết quả, như hình minh họa.
Lưu ý: Microsoft Excel sẽ tự động xóa những số 0 đứng top đầu mỗi con số trong một ô và việc này không có ảnh hưởng gì trong hầu hết các hoàn cảnh.
Ở đây, mình sẽ hướng dẫn bạn giữ lại những số 0 đứng đầu rất đơn giản.
Bước 1: Trong bảng tính Excel bạn điền hàm =TEXT(ô tham chiếu dữ liệu, số lượng số 0 muốn lấy) vào ô tham chiếu muốn hiển thị kết quả, như hình minh họa.
ví dụ minh họa hàm TEXT.
Bước 2: Bạn nhấn phím Enter để hiển thị kết quả và dùng trỏ chuột kéo kết quả xuống sẽ được kết quả như hình.
Nhấn Enter để hiển thị kết quả.
Sử dụng hàm TEXT để chuyển giá trị thành số điện thoại theo một định dạng nhất định
Bước 1: Trong bảng tính Excel bạn nhập hàm =TEXT(B4,”[<=9999999]###-####;(###)> vào ô tham chiếu mong muốn hiển thị kết quả, như hình minh họa.
trình bày hàm:
- TEXT: là hàm dùng để chuyển đổi.
- B4: là ô tham chiếu để thu thập dữ liệu.
- [<=9999999]###-####:>Là điều kiện giới hạn chữ số và kiểu hiển thị trong kết quả.
- (###) ###-####: Là điều kiện hiển thị nếu như ô tham chiếu có dãy chữ số lớn hơn điều kiện giới hạn.
VD minh họa hàm TEXT.
Bước 2: Bạn nhấn phím Enter để hiển thị kết quả và dùng trỏ chuột kéo kết quả xuống có thể được kết quả như hình.
Nhấn Enter để hiển thị kết quả.
Một số ví dụ hàm TEXT
phía dưới là một số ví dụ về cách sử dụng phổ cập của hàm TEXT để bạn tham khảo:
Kết nối văn bản và số hoặc ngày
ví dụ 1: Chèn định dạng số bên trong một chuỗi văn bản
VD, mục tiêu của bạn là tính được tổng số tiền người mua hàng cần trả (Thành tiền) với số liệu như trên.
công thức để ra được kết quả là:
Thành tiền = Đơn giá x Số lượng x % còn lại một khi được giảm giá (ở đây sẽ thu thập 1 – % giảm)
Như vậy, nếu như tính toán đơn thuần bạn có thể chỉ phải nhập công thức là: =A2*B2*(1-C2). Kết quả trả về cho bạn có thể là con số 156.8.
tuy nhiên, bạn chưa ưng ý với việc kết quả trả về chỉ là một con số, vẫn chưa có đơn vị, vẫn chưa có định dạng đẹp đẽ, lại vẫn chưa có dòng chú thích khiến những con số lẫn lộn với nhau gây rối mắt, phức tạp.
rõ ràng, để dữ liệu trông dễ xem hơn, bạn muốn chèn thêm câu giải thích là “Thành tiền: ” trước giá tiền; đồng thời bạn muốn giá tiền có thêm biểu tượng tiền trở nên xấu hơn, dấu phân cách hàng ngàn và hai chữ số thập phân.
Lúc này hàm TEXT sẽ hỗ trợ bạn. Bạn có thể thực hiện 1 trong 2 cách sau:
Cách 1: Nối chuỗi ký tự với hàm TEXT bằng hệ thống ký hiệu. Bạn nhập:
=”Thành tiền: “&TEXT(A2*B2*(1-C2),”$###,###.00”)
Cách 2: Nối chuỗi ký tự với hàm TEXT bằng hàm CONCATENATE.
Cú pháp của CONCATENATE như sau:
=CONCATENATE (text1, text2,…).
trong số đó text là một chuỗi văn bản, tham chiếu ô hoặc giá trị trong công thức.
Như vậy, đối với VD trên, bạn nhập:
=CONCATENATE(“Thành tiền: “,TEXT(A2*B2*(1-C2),”$###,###.00”))
ví dụ 2: kết hợp văn bản và ngày tháng
Giả sử, bạn có một bảng dữ liệu và bạn muốn thêm một dòng chú thích “Ngày hình thành dữ liệu: “, cùng lúc đó muốn ngày tháng hiển thị theo thứ tự: Ngày, tháng, năm (Ví dụ: 08/08/2021)
chúng ta có thể lồng hàm TODAY trong công thức của hàm TEXT, và khai báo mã định dạng “dd/mm/yyyy” để kết quả trả về theo ý mong muốn của mình.
Cách 1: Bạn sử dụng bộ máy ký hiệu để nối chuỗi ký tự với hàm TEXT:
=”Ngày hình thành dữ liệu: ” & TEXT(TODAY(), “dd/mm/yyyy”)
Cách 2: Bạn dùng hàm TEXT kết hợp CONCATENATE:
=CONCATENATE(“Ngày hình thành dữ liệu: “, TEXT(TODAY(), “dd/mm/yyyy”))
Thêm số 0 vào đầu mỗi dãy số
ví dụ 1: Thêm những số 0 đứng trước mỗi con số trong một cột
Bạn có thể sử dụng hàm TEXT để thêm số 0 đứng trước các con số trong một cột. Những con số 0 này cần phải tương ứng với cơ quan số được hiển thị.
Lưu ý: Dữ liệu trả về của hàm TEXT là chuỗi văn bản, bạn không thể sử dụng dữ liệu này để làm các phép tính trong Excel nữa.
VD 2: Thêm số 0 vào trước dữ liệu số máy đã bị Excel xóa mất số 0 đầu tiên
Cách này thường sử dụng khi bạn thu về một bảng dữ liệu số điện thoại nhưng bị mất số 0 trước tiên (Excel hiểu số điện thoại là một dãy ký tự số bình thường, vì thế sẽ loại đi số 0 dư thừa) gây chông gai trong quá trình xem và lấy dữ liệu.
Lưu ý: phương thức này chỉ sử dụng được khi số lượng chữ số trong số máy của tất cả ô dữ liệu đều bằng nhau (ví dụ: Data dữ liệu của chúng ta chỉ chứa số máy có 10 chữ số).
Chuyển giá trị thành số điện thoại theo định dạng
Hàm TEXT trong Excel cho phép bạn sử dụng dấu gạch ngang (-) và dấu ngoặc đơn () trong mã định dạng để tạo thành những chuỗi văn bản có định dạng phù hợp với mục tiêu dùng của mình.
Với số máy có chứa mã vùng, bạn có thể trải nghiệm mã định dạng như hình dưới để thiết lập định dạng số máy dạng: (123) 456-7890.
Bên cạnh đấy, Bạn có thể chuyển số máy 335263321 (tại ô C2) thành 0335 263 321 để phù hợp với cách xem của người Việt nhờ nối số “0” với hàm TEXT:
=”0″&TEXT(C2, “### ### ###”)
Ở đây, bạn có thể trải nghiệm dấu cách để phân cách các nhóm số.
Lỗi hàm TEXT trong Excel
trong quá trình thao tác với hàm TEXT, chúng ta có thể gặp một vài lỗi sau:
Lỗi #NAME?
– Nguyên nhân: người sử dụng không dùng dấu ngoặc kép (“”) ở mã định dạng.
VD bí quyết =TEXT(D2, mm/dd/yy) là bí quyết sai, hàm trả về lỗi #NAME?
Cách khắc phục: Thêm dấu ngoặc kép vào mã định dạng, thành: =TEXT(D2, “mm/dd/yy”)
Hàm báo sai cú pháp
– nguyên nhân 1: bạn điền sai cú pháp, hoặc khi mà bạn copy đoạn công thức từ nguồn khác về, dấu ngoặc kép (“) bị sai định dạng, khiến Excel không thể thực hiện lệnh. Hàm sẽ báo Lỗi công thức (There’s a problem with this formula).
Cách khắc phục: Xóa dấu ngoặc kép cũ có trong công thức và tiến hành nhập tay lại.
– nguyên nhân 2: Do sự không giống nhau trong cài đặt ứng dụng Excel giữ các nước
một số nước có thiết lập phần mềm Excel để tối ưu việc sử dụng cho hoạt động theo ngôn ngữ của nước đó, Điển hình là nước Đức.
VD trong trường hợp dùng Excel tại Đức, thay vì nhập bí quyết =TEXT(A1, “mm/dd/yy”), người sử dụng bản Excel phát hành ở Đức sẽ cần nhập “t” thay cho “d” và “j” thay cho “y”. Như vậy, bí quyết vừa nói đến có thể được thay bằng =TEXT(A1; “mm/tt/jj”).
Cách khắc phục: Bạn có thể nghiên cứu để khai báo cú pháp hàm theo thiết lập ở phiên bản Excel mình đang sử dụng, hoặc đổi sang sử dụng một phiên bản Excel khác gần gũi hơn với ngôn ngữ mình sử dụng.
Tổng kết
Trên đây chính là hướng dẫn hàm text trong excel và các cách khắc phục mà Thế giới táo khuyết muốn gửi đến bạn. Hy vọng qua bài viết này, bạn đọcsẽ có thêm nhiều thông tin và kiến thức hữu ích.
Nguồn: Tổng hợp
