Trong bài viết này, Tin Học Văn Phòng sẽ hướng dẫn các bạn cách sử dụng hàm TEXT trong Excel. Các bạn không những biết về cú pháp cơ bản, mà cũng sẽ biết được cách ứng dụng thực tế của hàm TEXT thông qua một số ví dụ mình họa. Hãy cũng tìm hiểu nhé.
Nội dung bài viết
Chức năng hàm TEXT trong Excel
Hàm TEXT trong Excel có thể được sử dụng khi bạn muốn chuyển đổi số, ngày tháng,… sang định dạng văn bản với định dạng mà bạn chỉ định.
Hàm TEXT được phân loại vào nhóm hàm xử lý chuỗi, và có thể được sử dụng trong tất cả các phiên bản của Microsoft Excel từ Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, và các phiên bản thấp hơn.
Thông thường, hàm TEXT trong Excel sẽ được sử dụng trong một số trường hợp sau:
- Chuyển đổi định dạng của một số, để số đó hiển thị theo định dạng dễ đọc hơn hoặc theo yêu cầu.
- Chuyển đổi ngày tháng sang các định dạng khác nhau như: “1-Jul-2021” hay “2021-Jul” tùy theo chúng ta mong muốn.
- Để kết hợp số hoặc ngày tháng với chuỗi văn bản khác.
Cú pháp hàm TEXT trong Excel
Cú pháp của hàm TEXT trong Excel là:
=TEXT(value, format_text)Trong đó:
- value: là giá trị mà bạn muốn chuyển đổi thành chuỗi văn bản. Giá trị này có thể là một số, ngày tháng, tham chiếu đến ô khác.
- format_text: là định dạng mà bạn muốn áp dụng cho văn bản khi nó được hiển thị. Tham số này được truyền vào dưới dạng mã định dạng, và được đặt trong dấu ngoặc kép. Ví dụ: “mm / dd / yyyy” hoặc “0.0%”.
Ví dụ:
Với ngày tháng trong ô A2 có giá trị là “8/27/2021”, bạn có thể sử dụng hàm TEXT trong Excel để hiển thị giá trị ngày tháng đó sang ô B2 với định dạng khác theo công thức sau:
=TEXT(A2,"dd / mm / yyyy")
Giải thích công thức:
- A2: là ô chứa giá trị mà bạn muốn chuyển đổi định dạng sang dạng chuỗi.
- “dd / mm / yyyy”: là định dạng mới mà bạn muốn giá trị trong ô A2 hiển thị theo. Trong ví dụ này, do ngày tháng trong ô A2 hiển thị theo “tháng/ngày/năm” sẽ gây khó đọc, nên mình sẽ hiển thị giá trị đó theo định dạng khác là “ngày / tháng / năm” trong ô B2 để dễ đọc hơn.
Lưu ý khi sử dụng hàm TEXT trong Excel
- Hàm TEXT trong Excel sẽ chuyển đổi các giá trị số, ngày tháng,… thành chuỗi văn bản. Và do đó, các giá trị do hàm TEXT trả về sẽ không thể sử dụng được trong các phép tính.
- Nếu bạn cần sử dụng giá trị trả về của hàm TEXT trong công thức / phép tính, hãy sử dụng chức năng tùy chỉnh định dạng số, ngày tháng trong Excel để định dạng.
- Giá trị format_text phải được đặt trong dấu ngoặc kép.
Mã định dạng được sử dụng trong hàm TEXT
Như các bạn đã thấy, cú pháp của hàm TEXT trong Excel rất đơn giản.
Tuy nhiên, có một số khó khăn khi sử dụng hàm TEXT, đó chính là tham số format_text. Bạn phải cung cấp mã định dạng thích hợp cho tham số đó để hàm TEXT mới có thể trả về giá trị theo định dạng mà bạn muốn.
Về cơ bản, hàm TEXT chấp nhận hầu hết các mã định dạng được sử dụng trong Excel. Trong bảng dưới đây, mình sẽ liệt kê một số mã định dạng phổ biến và thường được sử dụng nhất để các bạn có thể tham khảo.
| Mã định dạng | Mô tả | Ví dụ cách sử dụng |
| 0 | + Thường dùng để đại diện cho sô 0 vô nghĩa (phần nguyên thì điền số 0 vào trước, phần thập phân thì điền vào sau). + Đối với phần nguyên: +++ Nếu số lượng số phần nguyên ít hơn số 0, sẽ thêm số 0 vào đầu cho bằng số lượng số 0. +++ Nếu số lượng số phần nguyên nhiều hơn số 0, phần nguyên không thay đổi. + Đối với phần thập phân: +++ Nếu số lượng số phần thập phân ít hơn số 0, sẽ thêm số 0 vào cuối cho bằng số lượng số 0. +++ Nếu số lượng số phần thập phân nhiều hơn số 0, sẽ làm tròn cho bằng số lượng số 0. | Định dạng “#.00”, sẽ hiển thị 2 số thập phân. Ví dụ: =TEXT(25,”00.00″) // sẽ có kết quả là 25.00 =TEXT(5.1,”00.00″) // sẽ có kết quả là 05.10 =TEXT(125.1267,”00.00″)// sẽ có kết quả là 125.13 |
| # | Tương tự như mã 0, nhưng: + Bắt buộc phải có số thập phân phía sau, nếu là số nguyên thì sẽ thêm dấu thập phân (.) + Nếu số lượng số thập phân ít hơn số lượng dấu #, sẽ giữ nguyên số mà không thêm số 0 vào cuối như mã 0. + Nếu số lượng số thập phân nhiều hơn số lượng dấu #, sẽ làm tròn cho bằng số lượng dấu # (tương tự mã 0). | Ví dụ: =TEXT(25,”#.##”) // sẽ có kết quả là 25. =TEXT(25.1,”#.##”) // sẽ có kết quả là 25.1 =TEXT(25.1267,”#.##”)// sẽ có kết quả là 25.13 |
| d | Đại diện cho ngày của tháng, không có số 0 đứng đầu nếu ngày có 1 số. | Ví dụ định dạng ngày 1 tháng 12 năm 2021: =TEXT(“12/1/2021″,”d”) // sẽ có kết quả là 1 Ví dụ định dạng ngày 15 tháng 12 năm 2021: =TEXT(“12/1/52021″,”d”) // sẽ có kết quả là 15 |
| dd | Đại diện cho ngày của tháng, thêm số 0 đứng đầu. | Ví dụ định dạng ngày 1 tháng 12 năm 2021: =TEXT(“12/1/2021″,”dd”) // sẽ có kết quả là 01 Ví dụ định dạng ngày 15 tháng 12 năm 2021: =TEXT(“12/1/2021″,”dd”) // sẽ có kết quả là 15 |
| ddd | Đại diện cho thứ, chỉ lấy 3 ký tự tắt đầu tiên. | =TEXT(“12/1/2021″,”ddd”) // sẽ có kết quả là Wed |
| dddd | Đại diện cho thứ, lấy tên thứ đầy đủ. | =TEXT(“12/1/2021″,”dddd”) // sẽ có kết quả là Wednesday |
| m | Đại diện cho tháng, hiển thị số, không thêm số 0 vào đầu nếu tháng có 1 số. | =TEXT(“8/1/2021″,”m”) // sẽ có kết quả là 8 |
| mm | Đại diện cho tháng, hiển thị số, thêm số 0 vào đầu nếu tháng có 1 số. | =TEXT(“8/1/2021″,”mm”) // sẽ có kết quả là 08 |
| mmm | Đại diện cho tháng, hiển thị chữ, lấy chữ viết tắt tên tháng. | =TEXT(“8/1/2021″,”mmm”) // sẽ có kết quả là Aug |
| mmmm | Đại diện cho tháng, hiển thị chữ, lấy tên tháng đầy đủ. | =TEXT(“8/1/2021″,”mmmm”) // sẽ có kết quả là August |
| yy | Đại diện cho năm, chỉ lấy 2 số đại diện. | =TEXT(“8/1/2021″,”yy”) // sẽ có kết quả là 21 |
| yyyy | Đại diện cho năm, lấy 4 số đầy đủ của năm. | =TEXT(“8/1/2021″,”yyyy”) // sẽ có kết quả là 2021 |
| h | Đại diện cho giờ, không có số 0 ở đầu. | =TEXT(“1:2:3″,”h:m:s”) // sẽ có kết quả là 1:2:3 |
| hh | Đại diện cho giờ, có số 0 ở đầu. | =TEXT(“1:2:3″,”hh:m:s”) // sẽ có kết quả là 01:2:3 |
| m | Đại diện cho phút, không có số 0 ở đầu. | =TEXT(“1:2:3″,”h:m:s”) // sẽ có kết quả là 1:2:3 |
| mm | Đại diện cho phút, có số 0 ở đầu. | =TEXT(“1:2:3″,”h:mm:s”) // sẽ có kết quả là 1:02:3 |
| s | Đại diện cho giây, không có số 0 ở đầu. | =TEXT(“1:2:3″,”h:m:s”) // sẽ có kết quả là 1:2:3 |
| ss | Đại diện cho giây, có số 0 ở đầu. | =TEXT(“1:2:3″,”h:m:ss”) // sẽ có kết quả là 1:2:03 |
| AM/PM | Dùng cho thời gian được biểu thị dưới dạng đồng hồ 12 giờ. + Buổi sáng thì hiển thị AM + Buổi sáng thì hiển thị PM | =TEXT(“1:2:3″,”h:m:s AM/PM”) // sẽ có kết quả là 1:2:3 AM =TEXT(“13:2:3″,”h:m:s AM/PM”) // sẽ có kết quả là 1:2:3 PM |
Ví dụ cách sử dụng hàm TEXT trong Excel
Dùng hàm TEXT trong Excel để tùy chỉnh hiển thị ngày tháng
Để hiển thị định dạng ngày tháng chính xác theo cách bạn muốn, bạn có thể sử dụng các mã ngày tháng được đề cập ở trên bằng nhiều cách khác nhau. Dưới đây là một số ví dụ:
| Giá trị ban đầu | Công thức | Kết quả |
| 1/1/2021 | =TEXT(“1/1/2021”, “d/m/yy”) | 1/1/21 |
| 1/1/2021 | =TEXT(“1/1/2021”, “dd/mm/yyyy”) | 01/01/2021 |
| 1/1/2021 | =TEXT(“1/1/2021”, “dd/mm/yy”) | 01/01/21 |
| 1/1/2021 | =TEXT(“1/1/2021”, “dd/mmm/yyyy”) | 01/Jan/2021 |
| 1/1/2021 | =TEXT(“1/1/2021”, “dd/mmmm/yyyy”) | 01/January/2021 |
Dùng hàm TEXT trong Excel để tùy chỉnh hiển thị giờ
Như các bạn có thể đã nhận thấy, mã m được sử dụng để hiển thị tháng và phút. Vì vậy, bạn cần phải biết cách phân biệt giữa chúng để có thể sử dụng chính xác.
Nếu bạn đặt “m” ngay sau mã h (giờ) hoặc ngay trước mã s (giây), Excel hiểu rằng bạn muốn hiển thị phút chứ không phải tháng.
Sau đây là một số ví dụ về định dạng giờ khi dùng chung với ngày tháng trong Excel:
| Giá trị ban đầu | Công thức | Kết quả |
| 1/1/2021 1:2:3 | =TEXT(“1/1/2021 1:2:3”, “dd/mm/yyyy – hh:mm:ss”) | 01/01/2021 – 01:02:03 |
| 1/1/2021 1:2:3 | =TEXT(“1/1/2021 1:2:3”, “dd/mm/yyyy – hh:mm”) | 01/01/2021 – 01:02 |
| 1/1/2021 1:2:3 | =TEXT(“1/1/2021 1:2:3”, “dd/mm/yyyy – hh:mm AM/PM”) | 01/01/2021 – 01:02 AM |
| 1/1/2021 15:2:3 | =TEXT(“1/1/2021 15:2:3”, “dd/mm/yyyy – hh:mm AM/PM”) | 01/01/2021 – 03:02 PM |
Dùng hàm TEXT trong Excel nối chuỗi và số
Giả sử bạn tính toán tổng số tiền dựa trên đơn giá trong ô B2, số lượng trong ô C2 bằng cách sử dụng phép tính này: = B2 * C2.
Để người dùng hiểu được kết quả của công thức đó có nghĩa là gì, bạn muốn hiển thị nó cùng với một câu giải thích như “Tổng tiền: “. Ngoài ra, bạn muốn hiển thị dấu phân cách hàng nghìn và giá tiền có 4 chữ số thập phân.
Để thực hiện, các bạn dùng công thức sau:
="Tổng tiền: " & TEXT(B2*C2,"#,###0.0000")
Dùng hàm TEXT trong Excel để thêm số 0 vào đầu số
Nhưng các bạn có thể đã biết, Microsoft Excel sẽ tự động bỏ đi các số 0 đứng đầu một số, ví dụ như khi bạn nhập 0123 thì Excel sẽ tự động chuyển thành 123.
Vậy trong trường hợp bạn muốn thêm các số 0 vào đầu các số thì làm bằng cách nào?
Nếu bạn dùng hàm TEXT thì vấn đề này sẽ được giải quyết rất đơn giản, đó là sử dụng mã định dạng “0”.
Như đã giải thích trong phần mã định dạng, số 0 khi dùng cho phần nguyên của 1 số thì nó sẽ tự động thêm vào số 0 ở đầu cho bằng số lượng theo định dạng mà chúng ta nhập vào.
Ví dụ, để hiển thị số trong ô A2 thành một chuỗi ký tự gồm 4 con số và có số 0 ở đầu, thì các bạn dùng công thức sau:
=TEXT(A2,"0000")
Dùng hàm TEXT trong Excel để hiển thị số theo định dạng số điện thoại
Để dùng hàm TEXT định dạng một chuỗi số điện thoại bình thường nằm trong ô A2 thành dạng chuẩn thì các bạn dùng công thức dưới đây:
=TEXT(A2, "(+84) ##-###-###")
Bên trên là hướng dẫn cách sử dụng hàm TEXT trong Excel, cùng với một số ví dụ về cách dùng thực tế của hàm này. Hy vọng bài viết sẽ giúp ích cho các bạn.
Chúc các bạn thành công!
Xem thêm:
- Hướng dẫn sử dụng hàm SUBSTITUTE trong Excel
- Hướng dẫn sử dụng hàm TRIM trong Excel
- Hướng dẫn sử dụng hàm LEN trong Excel
- Hướng dẫn sử dụng hàm MID trong Excel
- Hướng dẫn sử dụng hàm LEFT trong Excel
- Hướng dẫn sử dụng hàm RIGHT trong Excel
- Hướng dẫn sử dụng hàm SEARCH trong Excel
- Hướng dẫn sử dụng hàm FIND trong Excel
- Hướng dẫn sử dụng hàm REPLACE trong Excel