Hàm Text Chuyển Định Dạng Số Thành Văn Bản Trong Excel

Hàm TEXT trong Excel là một công cụ mạnh mẽ giúp người dùng dễ dàng chuyển đổi các giá trị số thành văn bản với định dạng tùy chỉnh. Với hàm này, bạn có thể thay đổi cách hiển thị số, ngày tháng, hoặc thời gian theo ý muốn, làm cho bảng tính trở nên rõ ràng và dễ hiểu hơn. Trong bài viết này, HỌC TIN sẽ giúp bạn nắm vững cách sử dụng hàm TEXT trong Excel để tối ưu hóa việc xử lý và trình bày dữ liệu một cách hiệu quả.

Table of Contents

Toggle

• Hàm TEXT trong Excel là gì?
• Công thức hàm TEXT trong Excel
• Các loại mã định dạng hàm TEXT trong Excel
  • Mã định dạng cơ bản trong hàm TEXT
  • Mã định dạng ngày, giờ trong hàm TEXT
• Cách sử dụng hàm TEXT trong Excel và ví dụ minh họa
  • Kết nối văn bản với số hoặc ngày tháng
    • Ví dụ 1: Chèn định dạng số bên trong một chuỗi văn bản
    • Ví dụ 2: Kết hợp văn bản và ngày tháng
  • Thêm số 0 đứng đầu một dãy số
    • Ví dụ 1: Thêm số 0 đứng đầu trong một cột số
    • Ví dụ 2: Thêm số 0 vào trước số điện thoại
  • Chuyển giá trị thành số điện thoại theo định dạng
    • Ví dụ 1: Định dạng số điện thoại với mã vùng
    • Ví dụ 2: Định dạng số điện thoại không có mã vùng
• Lỗi thường gặp khi sử dụng hàm TEXT và cách khắc phục
  • Lỗi #NAME?
  • Lỗi cú pháp

Hàm TEXT trong Excel là gì?

Hàm TEXT trong Excel là một công cụ hữu ích cho phép chuyển đổi các giá trị số thành chuỗi văn bản với định dạng tùy chỉnh. Thay vì chỉ hiển thị một con số thuần túy, hàm này giúp bạn có thể định dạng các giá trị số sao cho chúng dễ nhìn và dễ sử dụng hơn, chẳng hạn như thêm dấu phân cách hàng nghìn, thay đổi kiểu ngày tháng hoặc làm nổi bật các số liệu quan trọng. Với hàm TEXT Excel, bạn có thể dễ dàng tạo ra các định dạng phù hợp với nhu cầu của mình.

Ngoài việc định dạng số, hàm TEXT trong Excel còn giúp kết hợp các giá trị số hoặc ngày tháng với văn bản, cho phép bạn tạo ra những chuỗi dữ liệu phức tạp và có tính thẩm mỹ cao. Ví dụ, bạn có thể kết hợp giá trị ngày tháng với các từ ngữ như “Ngày”, “Tháng” để tạo ra những thông báo hoặc báo cáo rõ ràng hơn. Đây là một trong những lý do khiến hàm TEXT trở thành công cụ không thể thiếu đối với những người làm việc với dữ liệu trong Excel.

Xem thêm: 7 Hàm Trung Bình Nâng Cao Trong Excel: Công Thức Và Ví Dụ Thực Tế

Công thức hàm TEXT trong Excel

Công thức hàm TEXT trong Excel có cấu trúc đơn giản như sau: =TEXT(value, format_text)

Trong đó:

• value: Là giá trị số cần chuyển đổi thành văn bản. Giá trị này có thể là một con số, một ngày tháng, hoặc tham chiếu đến một ô chứa giá trị số, hoặc là kết quả trả về từ một hàm khác.
• format_text: Là định dạng bạn muốn áp dụng cho giá trị số. Bạn cần khai báo mã định dạng dưới dạng chuỗi văn bản, được đặt trong dấu ngoặc kép, ví dụ: “mm/dd/yy” hoặc “# ?/?”,…

Lưu ý khi sử dụng hàm TEXT trong Excel:

• Kết quả trả về của hàm TEXT luôn là chuỗi văn bản, ngay cả khi nó trông giống một số hay ngày tháng. Do đó, kết quả này không thể sử dụng cho các phép tính số học.
• Đối với số thập phân, hãy sử dụng dấu chấm (.) để phân cách phần nguyên và phần thập phân và dấu phẩy (,) để phân cách hàng nghìn, vì đây là thiết lập mặc định của Excel.
• Nên giữ lại giá trị gốc ở một ô khác trong Excel và chỉ áp dụng hàm TEXT trên ô riêng biệt để dễ dàng tham chiếu lại giá trị gốc khi cần thiết.

Xem thêm: Ứng Dụng Hàm VALUE Trong Excel Để Xử Lý Dữ Liệu Chuỗi Thành Số

Các loại mã định dạng hàm TEXT trong Excel

Khi sử dụng hàm TEXT trong Excel, một yếu tố quan trọng giúp bạn tùy chỉnh kết quả đầu ra chính là mã định dạng. Các mã định dạng này cho phép bạn kiểm soát cách thức hiển thị của giá trị số, ngày tháng hay giờ trong Excel, giúp dữ liệu trở nên dễ hiểu và phù hợp với nhu cầu sử dụng. Trong phần này, chúng ta sẽ tìm hiểu các loại mã định dạng phổ biến có thể áp dụng trong hàm TEXT để tối ưu hóa việc trình bày dữ liệu trong bảng tính của bạn.

Mã định dạng cơ bản trong hàm TEXT

Như đã trình bày, công thức của hàm TEXT rất đơn giản. Mỗi phần được gán một mã định dạng tương ứng, và kết quả trả về sẽ là số theo định dạng yêu cầu. Hàm TEXT có khả năng chấp nhận hầu hết các mã định dạng phổ biến trong Excel. Dưới đây là bảng liệt kê các mã định dạng cơ bản nhất:

Mã	Mô tả	Định dạng mã mẫu
0	Chữ số thể hiện những số 0 vô nghĩa	#.00 – Tính đến số thập phân thứ 2. Nếu nhập 2.5 trong ô tham chiếu, số hiển thị là 2.50.
#	Dấu ngăn không biểu diễn thêm số 0	#.## – Tính đến số thập phân thứ 2. Nếu nhập 2.5, số hiển thị là 2.5. Nếu nhập 2.5555, số hiển thị là 2.56.
?	Dấu để khoảng trống cho số 0 vô nghĩa nhưng không biểu diễn chúng. Thường 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 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	Sử dụng để phân tách phần nguyên và phần 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.

Dưới đây là bảng hướng dẫn cách sử dụng công thức hàm TEXT trong Excel để áp dụng các định dạng khác nhau và chuyển đổi chúng thành giá trị tương ứng:

STT	Giá trị gốc	Giá trị định dạng	Công thức
1	5.5	5.50	=TEXT(A2, “0.00”)
2	5.5	550%	=TEXT(A2,”#%”)
3	5.5	$5.50	=TEXT(A2, “$#,##0.00”
4	5.5	+$5.50	=TEXT(A2,”+$#,##0.00;-$#,##0.00;$0.00″)
5	5.5	-$5.50	=TEXT(A2, “- $#,##0.00;- $#,##0.00;$0.00”)
6	5.5	5 ½	=TEXT(A2,”# ?/?”)
7	5.5	5.50E+00	=TEXT(A2,”0.00E+00″)
8	5.5	~6!	=TEXT(A2,”~# !”)

Mã định dạng ngày, giờ trong hàm TEXT

Khi làm việc với ngày và giờ trong hàm TEXT trong Excel, bạn có thể sử dụng các mã định dạng đặc biệt để điều chỉnh cách hiển thị dữ liệu. Những mã định dạng này cho phép bạn dễ dàng chuyển đổi và hiển thị ngày, giờ theo nhiều kiểu khác nhau. Dưới đây là các mã định dạng ngày và giờ phổ biến mà bạn có thể áp dụng trong hàm TEXT:

Mã định dạng	Mô tả	Ví dụ
d (day)	Ngày của tháng hoặc ngày trong năm	d – 1 hoặc 2 chữ số (1 – 31) dd – 2 chữ số (01 – 31) ddd – 3 ký tự viết tắt (Mon – Sun)
m (month)	Tháng (sử dụng như một phần của ngày)	m – 1 hoặc 2 chữ số (1 – 12) mm – 2 chữ số (01 – 12) mmm – 3 ký tự viết tắt (Jan – Dec) mmmm – chữ đầy đủ của tháng
y (year)	Năm	yy: -2 chữ số (06 là 2006) yyyy – 4 chữ số (2006, 2016)
h (hour)	Giờ	h -1 hoặc 2 chữ số (1 – 60) hh – 2 chữ số (01 – 60)
m (minute)	Phút	m – 1 hoặc 2 chữ số (1 – 60) mm – 2 chữ số (01 – 60)
s (second)	Giây	s – 1 hoặc 2 chữ số (1 – 60) ss – 2 chữ số (01 – 60)

Dưới đây là bảng thể hiện một số công thức hàm TEXT được áp dụng cho ngày tháng:

STT	Ngày gốc	Ngày được định dạng	Công thức
1	12/9/2016	Dec 09 2016	=TEXT(A2, “mmm dd yyyy”)
2	12/9/2016	Friday 09 December, 2016	=TEXT(A2, “dddd dd mmmm, yyyy”)
3	12/9/2016	9-Dec-16	=TEXT(A2, “d-mmm-yy”)
4	12/9/2016	Friday	=TEXT(A2,”dddd”)

Xem thêm: Cách Dùng 3 Hàm Lấy Ký Tự Bất Kỳ Trong Excel: LEFT/ RIGHT/ MID

Cách sử dụng hàm TEXT trong Excel và ví dụ minh họa

Khi đã nắm vững các mã định dạng trong hàm TEXT, bạn đã có thể áp dụng chúng vào thực tế. Việc sử dụng hàm lấy text trong Excel không chỉ giúp bạn định dạng dữ liệu số hay ngày tháng theo ý muốn, mà còn hỗ trợ bạn tạo ra các báo cáo và bảng tính dễ nhìn và chuyên nghiệp hơn. Trong phần này, chúng ta sẽ cùng khám phá cách dùng hàm TEXT với các ví dụ minh họa cụ thể, giúp bạn hiểu rõ hơn về cách thức hoạt động và ứng dụng của hàm này.

Kết nối văn bản với số hoặc ngày tháng

Khi sử dụng hàm TEXT trong Excel, bạn có thể dễ dàng kết hợp văn bản với các giá trị số hoặc ngày tháng để tạo ra những chuỗi dữ liệu rõ ràng và dễ hiểu. Việc này rất hữu ích trong các báo cáo hoặc bảng tính cần có chú thích, đơn vị hoặc định dạng đặc biệt để dữ liệu dễ nhìn hơn.

Ví dụ 1: Chèn định dạng số bên trong một chuỗi văn bản

Giả sử bạn muốn tính tổng số tiền mà khách hàng cần thanh toán (Thành tiền) với các số liệu như trong hình dưới đây, bao gồm đơn giá, số lượng và tỷ lệ % giảm giá.

Công thức để tính tổng số tiền là: Thành tiền = Đơn giá x Số lượng x (1 – % giảm). Với công thức này, bạn chỉ cần nhập: =A2*B2*(1-C2). Excel sẽ trả về kết quả là 156.8.

Tuy nhiên, nếu bạn mong muốn kết quả không chỉ là một con số khô khan, mà hiển thị rõ ràng với đơn vị tiền tệ, dấu phân cách hàng nghìn và có dòng chú thích như “Thành tiền: ” trước số tiền, bạn có thể dùng hàm TEXT để định dạng lại. Có hai cách thực hiện như sau:

• Cách 1: Dùng toán tử & để nối chuỗi ký tự với hàm TEXT:

=”Thành tiền: “&TEXT(A2*B2*(1-C2),”$###,###.00”)

• Cách 2: Dùng hàm CONCATENATE để nối chuỗi ký tự với hàm TEXT:

=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à muốn thêm một chú thích như “Ngày khởi tạo dữ liệu: “, đồng thời hiển thị ngày tháng theo thứ tự Ngày/Tháng/Năm (Ví dụ: 08/08/2021). Bạn có thể sử dụng hàm TODAY kết hợp với TEXT để định dạng ngày tháng mong muốn theo định dạng “dd/mm/yyyy”.

• Cách 1: Dùng toán tử & để nối chuỗi ký tự với hàm TEXT:

=”Ngày khởi tạo dữ liệu: ” & TEXT(TODAY(), “dd/mm/yyyy”)

• Cách 2: Dùng hàm CONCATENATE để nối chuỗi ký tự với hàm TEXT:

=CONCATENATE(“Ngày khởi tạo dữ liệu: “, TEXT(TODAY(), “dd/mm/yyyy”))

Thêm số 0 đứng đầu một dãy số

Trong Microsoft Excel, các số 0 đứng đầu dãy số sẽ tự động bị xóa bỏ khi bạn nhập vào một ô. Nếu bạn cần giữ lại số 0 đứng đầu trong một dãy số, hàm TEXT trong Excel sẽ là giải pháp đơn giản và hiệu quả.

Ví dụ 1: Thêm số 0 đứng đầu trong một cột số

Bạn có thể sử dụng hàm TEXT trong Excel để thêm số 0 vào trước các con số trong một cột dữ liệu. Để làm điều này, bạn cần xác định đúng số lượng số 0 mà bạn muốn thêm vào.

Ví dụ: Để biểu diễn số có 6 chữ số với số 0 đứng đầu, bạn dùng công thức sau: =TEXT(A2, “000000”). Trong đó, A2 là ô chứa điện thoại cần định dạng.

Lưu ý: Kết quả trả về của hàm TEXT sẽ là chuỗi văn bản, vì vậy bạn không thể sử dụng dữ liệu này trong các phép tính hay công thức khác.

Ví dụ 2: Thêm số 0 vào trước số điện thoại

Khi nhập liệu số điện thoại vào bảng tính, Excel sẽ tự động xóa bỏ số 0 đứng đầu vì Excel nhận diện số điện thoại là dãy số thông thường và loại bỏ số 0 không cần thiết. Để khắc phục tình trạng này và thêm lại số 0 đứng đầu, bạn có thể sử dụng hàm TEXT định dạng số điện thoại với công thức như sau: =”0″&TEXT(C2, “#### ### ###”). Trong đó, C2 là ô chứa điện thoại cần định dạng.

Lưu ý: Cách làm này chỉ áp dụng khi tất cả các số điện thoại trong dữ liệu có cùng số chữ số (ví dụ: tất cả đều là số điện thoại 10 chữ số).

Chuyển giá trị thành số điện thoại theo định dạng

Hàm TEXT trong Excel không chỉ giúp bạn chuyển đổi giá trị thành văn bản mà còn cho phép bạn tạo các định dạng tùy chỉnh để hiển thị dữ liệu theo cách dễ hiểu và phù hợp với yêu cầu. Đặc biệt, khi làm việc với số điện thoại, bạn có thể sử dụng các ký tự như dấu gạch ngang (-) và dấu ngoặc đơn () trong mã định dạng để tạo ra số điện thoại rõ ràng và dễ đọc.

Ví dụ 1: Định dạng số điện thoại với mã vùng

Giả sử bạn muốn chuyển đổi một số điện thoại có mã vùng thành định dạng như (123) 456-7890. Bạn có thể sử dụng hàm TEXT trong Excel kết hợp với mã định dạng theo công thức sau: =TEXT(A2,”[<=9999999]### ####;(###) ### ####”). Trong đó, A2 là ô chứa số điện thoại 1234567890.

Cách này giúp bạn định dạng số điện thoại theo chuẩn quốc tế với mã vùng trong dấu ngoặc và số phân cách bằng dấu gạch ngang.

Ví dụ 2: Định dạng số điện thoại không có mã vùng

Nếu bạn muốn chuyển một số điện thoại không có dấu phân cách, chẳng hạn như “335263321”, thành định dạng quen thuộc của người Việt là “0335 263 321”, bạn sẽ thực hiện bằng cách nối thêm số “0” vào trước với hàm TEXT theo công thức sau: =”0″&TEXT(C2, “### ### ###”). Trong đó, C2 là ô chứa số điện thoại 335263321.

Ở đây, dấu cách được dùng để phân tách các nhóm số, giúp số điện thoại dễ đọc hơn.

Xem thêm: Cách Dùng Hàm DATE Trong Excel Xử Lý Dữ Liệu Ngày Tháng Năm

Lỗi thường gặp khi sử dụng hàm TEXT và cách khắc phục

Khi sử dụng hàm TEXT trong Excel, người dùng đôi khi sẽ gặp phải một số lỗi phổ biến ảnh hưởng đến kết quả tính toán và định dạng dữ liệu. Những lỗi này có thể khiến cho quá trình làm việc trở nên rối rắm và mất thời gian sửa chữa. Để giúp bạn tiết kiệm thời gian và tránh các sai sót, hãy cùng HỌC TIN tìm hiểu về những lỗi thường gặp khi sử dụng hàm TEXT và cách khắc phục chúng.

Lỗi #NAME?

Trong quá trình thao tác với hàm TEXT, lỗi #NAME? sẽ xuất hiện khi người dùng quên đặt mã định dạng bên trong dấu ngoặc kép (“”). Điều này khiến Excel không thể nhận diện đúng định dạng mong muốn và trả về lỗi.

Ví dụ, công thức =TEXT(D2, mm/dd/yy) là công thức sai, sẽ trả về lỗi #NAME? do mã định dạng không được đặt trong dấu ngoặc kép. Để khắc phục lỗi này, bạn chỉ cần bổ sung dấu ngoặc kép để Excel hiểu rõ mã định dạng, như sau: =TEXT(D2, “mm/dd/yy”).

Lỗi cú pháp

Lỗi cú pháp khi sử dụng hàm TEXT trong Excel thường xảy ra do hai nguyên nhân chính sau đây:

Nguyên nhân 1: Nhập sai cú pháp

Bạn có thể nhập sai cú pháp hoặc khi sao chép công thức từ nguồn khác, dấu ngoặc kép (“) bị sai định dạng, khiến Excel không thực hiện được lệnh và báo lỗi “There’s a problem with this formula.”

Cách khắc phục: Bạn hãy xóa các dấu ngoặc kép cũ và nhập lại thủ công để đảm bảo định dạng đúng.

Nguyên nhân 2: Khác biệt trong thiết lập ngôn ngữ của Excel giữa các quốc gia

Một số phiên bản Excel được thiết lập theo ngôn ngữ của từng quốc gia để tối ưu hóa công việc, như ở Đức. Ví dụ, khi sử dụng Excel ở Đức, thay vì nhập công thức =TEXT(A1, “mm/dd/yy”), người dùng sẽ phải thay “d” bằng “t” và “y” bằng “j” để phù hợp với cách viết của địa phương, công thức hàm TEXT sẽ trở thành: =TEXT(A1; “mm/tt/jj”).

Cách khắc phục: Điều chỉnh công thức theo thiết lập ngôn ngữ của phiên bản Excel mình đang sử dụng hoặc cân nhắc đổi sang phiên bản Excel có ngôn ngữ phù hợp hơn với nhu cầu sử dụng.

Tổng kết lại, hàm TEXT trong Excel là một công cụ mạnh mẽ giúp bạn định dạng dữ liệu theo nhu cầu cụ thể. Tuy nhiên, khi sử dụng hàm này, bạn cần lưu ý một số yếu tố quan trọng như đảm bảo cú pháp đúng, chú ý đến khu vực ngôn ngữ của phiên bản Excel và hiểu rõ các đặc điểm của chuỗi văn bản được trả về. Hy vọng rằng qua bài viết này, bạn sẽ có thể sử dụng hàm TEXT hiệu quả và tránh được những lỗi thường gặp.

Xem thêm: Cách Sử Dụng Hàm DATEDIF Trong Excel Tính Chênh Lệch Thời Gian