Cách dùng hàm TEXT trong Excel chuyển số thành Text chi tiết

>>>> XEM NGAY: 15+ thủ thuật Excel “đỉnh cao” dành cho dân văn phòng, kế toán

Hàm TEXT trong Excel cho phép chuyển đổi các giá trị số thành chuỗi văn bản theo định dạng tùy chỉnh. Nói cách khác, đây là hàm chuyên dùng để xử lý và định dạng văn bản trong Excel. Dưới đây là những chức năng chính của hàm TEXT:

• Định dạng các giá trị số để chúng trở nên trực quan và dễ sử dụng hơn.
• Kết hợp các số hoặc ngày, tháng với văn bản hay ký tự đặc biệt.
• Định dạng ngày tháng theo các kiểu cụ thể

>>>> TÌM HIỂU NGAY: Hàm SUBSTITUTE – Công thức và cách sử dụng hiệu quả

Hàm TEXT trong Excel có công thức như sau:

Trong đó:

• value: Đây là giá trị số mà bạn muốn chuyển đổi thành văn bản. Giá trị này có thể là một số nguyên, một ngày tháng, hoặc một tham chiếu đến ô chứa giá trị số. Ngoài ra, nó cũng có thể là kết quả của một hàm khác trả về số hoặc ngày tháng.

• format_text: Là định dạng bạn muốn áp dụng cho kết quả chuyển đổi, được chỉ định bằng mã định dạng cụ thể. Mã này phải được đặt trong dấu ngoặc kép, ví dụ: “mm/dd/yy” hoặc “# ?/?”.

Kết quả mà hàm TEXT trong Excel trả về luôn là một chuỗi văn bản, mặc dù nó có thể trông như dữ liệu số khi hiển thị. Đối với dữ liệu thập phân, bạn nên 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 (,) để tách các hàng nghìn, vì đây thường là thiết lập mặc định trong Excel.

Dưới đây là một vài ví dụ thông dụng mà bạn có thể copy trực tiếp vào Excel để tự mình kiểm tra. Hãy chú ý đến các mã định dạng nằm trong dấu ngoặc kép.

Công thức	Mô tả
=TEXT(1234.567,”$#,##0.00″)	Tiền tệ với dấu phân tách hàng nghìn và 2 chữ số thập phân, ví dụ: $1.234,57. Lưu ý rằng Excel sẽ làm tròn giá trị đến 2 chữ số thập phân..
=TEXT(TODAY(),”MM/DD/YY”)	Ngày hôm nay với định dạng DD/MM/YY, chẳng hạn: 14/03/12.
=TEXT(TODAY(),”DDDD”)	Ngày trong tuần hiện tại, ví dụ: Thứ 2
=TEXT(NOW(),”H:MM SA/CH”)	Giờ hiện tại theo múi giờ, chẳng hạn: 1:29 CH.
=TEXT(0.285,”0,0%”)	Phần trăm, ví dụ: 28,5 %.
=TEXT(4,34 ,”# ?/?”)	Phân số, như 4 1/3.
=TRIM(TEXT(0,34,”# ?/?”))	Phân số, ví dụ: 1/3. Lưu ý, hàm TRIM được sử dụng để xóa khoảng trắng đầu dòng bằng giá trị thập phân.
=TEXT(12200000,”0.00E+00″)	Ký hiệu khoa học, chẳng hạn: 1,22E + 07
=TEXT(1234567898,”[<=9999999]###-####;(###) ###-####”)	Số điện thoại, chẳng hạn: (123) 456-7898
=TEXT(1234,”0000000″)	Thêm các số 0 đứng đầu, ví dụ: 0001234
=TEXT(123456,”##0° 00′ 00””)	Định dạng tùy chỉnh – Vĩ độ/Kinh độ

Lưu ý: Hàm TEXT trong Excel là một phương pháp để thay đổi định dạng, nhưng không phải là lựa chọn duy nhất. Bạn cũng có thể thay đổi định dạng mà không cần dùng công thức bằng cách nhấn CTRL+1 (hoặc +1 trên Mac), sau đó chọn định dạng mong muốn từ hộp thoại Định dạng Ô > Số.

>>>> THAM KHẢO: Cách sử dụng hàm TODAY trong Excel tính thời gian đơn giản

Dưới đây là một số ví dụ về cách áp dụng phổ biến hàm TEXT để bạn tham khảo:

Ví dụ 1: Thêm định dạng số vào trong chuỗi văn bản

Giả sử bạn muốn tính toán tổng số tiền khách hàng cần trả (Thành tiền) dựa trên số liệu đã có.

Nếu áp dụng công thức đơn giản, bạn sẽ nhập: =A2*B2*(1-C2), kết quả trả về sẽ là 156.8.

Tuy nhiên, bạn muốn hiển thị kết quả với định dạng rõ ràng hơn, bao gồm đơn vị tiền tệ và dấu phân cách hàng nghìn, cùng với dòng chú thích “Thành tiền: “.

Lúc này hàm TEXT sẽ giúp bạn. Bạn có thể thực hiện 1 trong 2 cách sau:

Cách 1: Sử dụng hàm TEXT kết hợp nối chuỗi bằng ký hiệu &. 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:

Trong đó 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 ví dụ trên, bạn nhập:

Ví dụ 1: Thêm số 0 vào đầu các số trong một cột

Bạn có thể sử dụng hàm TEXT để thêm các số 0 đứng đầu cho mỗi giá trị số trong cột, đảm bảo các số này tương ứng với định dạng mong muốn.

Lưu ý: Kết quả của hàm TEXT là chuỗi văn bản, do đó bạn không thể thực hiện phép tính trực tiếp trên dữ liệu này.

Ví dụ 2: Thêm số 0 vào đầu các số điện thoại bị mất

Khi nhập dữ liệu số điện thoại, Excel có thể tự động loại bỏ số 0 ở đầu. Bạn có thể sử dụng hàm TEXT để thêm lại số 0 này.

Lưu ý: Cách này chỉ hiệu quả khi tất cả các số điện thoại trong dữ liệu đều có độ dài ký tự giống nhau (ví dụ: tất cả đều có 10 chữ số).

Hàm TEXT cho phép bạn sử dụng các dấu như dấu gạch ngang (-) và dấu ngoặc đơn () để định dạng số điện thoại theo ý muốn.

Ví dụ: Để định dạng số điện thoại với mã vùng như sau: (123) 456-7890, bạn có thể sử dụng mã định dạng phù hợp.

Bạn cũng có thể định dạng số điện thoại theo kiểu Việt Nam, chẳng hạn như chuyển số 335263321 thành 0335 263 321 bằng cách nối số “0” với hàm TEXT:

Trong trường hợp này, dấu cách được dùng để phân cách các nhóm số.

Trong quá trình sử dụng hàm TEXT trong Excel, bạn có thể gặp phải một số lỗi như sau:

• Nguyên nhân: Do người dùng không đặt mã định dạng trong dấu ngoặc kép (“”).

   Ví dụ, công thức `=TEXT(D2, mm/dd/yy)` sẽ dẫn đến lỗi #NAME?

• Cách khắc phục: Bổ sung dấu ngoặc kép quanh mã định dạng, như sau: `=TEXT(D2, “mm/dd/yy”)`.

• Nguyên nhân 1: Bạn có thể đã nhập cú pháp không chính xác hoặc khi sao chép công thức từ nguồn khác, dấu ngoặc kép (“) có thể bị lỗi định dạng, dẫn đến việc Excel không thể thực thi lệnh. Lỗi công thức sẽ xuất hiện với thông báo “There’s a problem with this formula”.

• Cách khắc phục: Xóa dấu ngoặc kép cũ trong công thức và nhập lại.

• Nguyên nhân 2: Sự khác biệt trong cài đặt phần mềm Excel giữa các quốc gia có thể gây ra lỗi. Ví dụ, ở Đức, các thiết lập Excel có thể yêu cầu sử dụng các ký hiệu khác trong cú pháp công thức so với các quốc gia khác. Trong trường hợp này, thay vì nhập công thức `=TEXT(A1, “mm/dd/yy”)`, người dùng ở Đức sẽ phải sử dụng `“t”` thay vì `“d”` và `“j”` thay vì `“y”`. Do đó, công thức sẽ là `=TEXT(A1; “mm/tt/jj”)`.

• Cách khắc phục: Bạn nên xác định cú pháp hàm phù hợp với phiên bản Excel đang sử dụng hoặc chọn phiên bản Excel phù hợp hơn với ngôn ngữ của bạn.

Khi làm việc với hàm TEXT trong Excel, có một số điểm quan trọng cần lưu ý để đảm bảo bạn sử dụng hàm này một cách hiệu quả. Việc nắm rõ những lưu ý này không chỉ giúp bạn tránh được các lỗi phổ biến mà còn tối ưu hóa cách định dạng dữ liệu để phục vụ mục đích phân tích và báo cáo. Dưới đây là những điều cần chú ý để bạn có thể tận dụng tối đa khả năng của hàm TEXT.

• Cú pháp đúng: Đặt mã định dạng trong dấu ngoặc kép (“”)
• Thiết lập ngôn ngữ: Chú ý đến thiết lập ngôn ngữ và khu vực của Excel, vì cú pháp có thể khác nhau giữa các quốc gia.
• Loại dữ liệu: Đảm bảo dữ liệu đầu vào phù hợp với mã định dạng. Hàm TEXT luôn trả về chuỗi văn bản, không dùng cho phép toán số học.
• Dữ liệu kết quả: Hiểu rằng kết quả từ hàm TEXT là văn bản và không thể dùng cho các phép tính số học.
• Thử nghiệm: Thử nghiệm với các mã định dạng để tìm ra cách trình bày phù hợp cho dữ liệu của bạn.
• Xử lý lỗi: Kiểm tra lỗi phổ biến như #NAME? để đảm bảo cú pháp và dấu ngoặc kép chính xác.