Hàm TEXT trong excel là hàm giúp chuyển đổi định dạng của dữ liệu số sang chuỗi văn bản theo một kiểu định dạng cụ thể. Với mục đích giúp các dữ liệu số được hiển thị dưới dạng khác dễ nhìn hơn hoặc dễ sử dụng hơn trong một vài trường hợp. Hàm TEXT còn được dùng khi bạn muốn kết hợp các số hoặc ngày tháng năm với văn bản hay một số ký tự khác. Vậy cách sử dụng hàm TEXT ra sao? Và hàm TEXT trong excel có những kiểu định dạng nào? Hãy cùng tìm hiểu trong bài viết này của NTP nhé.
Mục lục
Hàm TEXT trong excel là gì?
Định nghĩa hàm TEXT
Hàm TEXT trong excel là hàm giúp chuyển đổi định dạng của dữ liệu số sang chuỗi văn bản theo một kiểu định dạng cụ thể. Với mục đích giúp các dữ liệu số được hiển thị dưới dạng khác dễ nhìn hơn hoặc dễ sử dụng hơn trong một vài trường hợp. Hàm TEXT còn được dùng khi bạn muốn kết hợp các số hoặc ngày tháng năm với văn bản hay một số ký tự khác.
Cú pháp hàm TEXT trong excel
=TEXT(value, format_text)
Chú thích công thức:
- value: Là giá trị số cần chuyển sang dạng văn bản. Giá trị này có thể là số, ngày, tháng, năm, giá trị tham chiếu của ô chứa giá trị số, giá trị tham chiếu của một hàm khác có kết quả là số hay ngày, tháng năm,…
- format_text: Là định dạng bạn muốn chuyển đổi, và cần khai báo cho hàm dưới dạng mã. Mã định dạng được đặt trong dấu ngoặc kép, ví dụ như: “mm/dd/yyyy”,”# ?/?”,…
Các loại mã định dạng của hàm TEXT
| Công thức | Chú thích |
| =TEXT(1654.693,”$#,##0.00″) | Chuyển đổi dữ liệu tiền tệ với dấu cách phần nghìn và 2 số thập phân, ví dụ: $1,654.69. Excel sẽ làm tròn tới 2 chữ số thập phân. |
| =TEXT(TODAY(),”DD/MM/YYYY”) | Chuyển đổi dữ liệu ngày mà bạn nhập vào hàm (theo ngày giờ của máy tính) thành định dạng DD/MM/YYYY, ví dụ: 28/07/2022. |
| =TEXT(TODAY(),”DDDD”) | Chuyển đổi dữ liệu về dạng thứ trong tuần, ví dụ bạn nhập hàm vào Excel vào lúc ngày giờ máy tính của bạn là Thứ 5, ngày 28/07/2022 thì hàm trả về là Thursday (Thứ 5). |
| =TEXT(NOW(),”H:MM AM/PM”) | Chuyển đổi múi giờ hiện tại theo định dạng, ví dụ: 4:12 PM. |
| =TEXT(0.379,”0.0%”) | Chuyển đổi thành dữ liệu phần trăm, ví dụ: 37.9%. |
| =TEXT(4.56,”#?/?”) | Chuyển đổi dữ liệu phân số, ví dụ: 41/9. |
| =TRIM(TEXT(0.39,”# ?/?”)) | Chuyển đổi liệu phân số, ví dụ: 2/5, phân số này sử dụng hàm TRIM để loại bỏ dấu cách đầu dòng bằng một giá trị thập phân. |
| =TEXT(18600000,”0.00E+00″) | Chuyển đổi dữ liệu chứa ký hiệu khoa học: 1.86E+07. |
| =TEXT(142563,”0000000″) | Thêm số các 0 đứng đầu (0), ví dụ: 0142563. |
| =TEXT(168193,”##0° 00′ 00””) | (Tùy chỉnh) – Kinh độ, Vĩ độ địa lý. ví dụ: 16° 81′ 93″. |
Cách dùng hàm TEXT chuyển đổi định dạng
Thêm số 0 vào đầu dãy số được chọn
Ví dụ 1: Từ những số được cho ban đầu bạn hãy định dạng số đó thành dãy có 6 chữ số bằng cách thêm những số 0 vào trước mỗi con số trong một cột.
Bạn có thể dùng hàm TEXT để thêm số 0 vào trước các số được cho trong cột. Những con số 0 này phải tương ứng với đơn vị số được hiển thị.
VD hàm TEXT trong excel
Lưu ý: Kết quả trả về của hàm TEXT là chuỗi văn bản, và bạn không thể dùng dữ liệu này để làm các phép tính trong Excel nữa. Nếu có bạn phải chuyển đổi dữ liệu một lần nữa bằng cách sử dụng hàm VALUE trong excel. Để định dạng được dãy trên theo yêu cầu bạn thực hiện như sau:
Bước 1: Tại vị trí ô C3 bạn nhập cú pháp =TEXT(B4,”000000″) để hàm trong excel tiến hành định dạng số được chọn thành dãy có 6 chữ số.
VD hàm TEXT trong excel
Bước 2: Sau khi nhập xong cú pháp bạn nhấn ENTER để hoàn thành và COPY công thức đến vị trí các ô còn lại để định dạng cho các số còn lại.
VD hàm TEXT trong excel
Chuyển đổi số điện thoại theo định dạng bằng hàm chuyển TEXT thành số được định dạng
Ví dụ 2: Bạn hãy chuyển đổi dãy số điện thoại được cho trong bảng dữ liệu DANH SÁCH SĐT KHÁCH HÀNG bên dưới thành dãy số điện thoại đã được định dạng theo kiểu (#### ### ###).
Trước tiên phải thêm số 0 vào trước dãy số điện thoại đã bị Excel xóa mất số 0 đầu tiên.
Cách này được áp dụng khi bạn muốn thu về một bảng dữ liệu số điện thoại nhưng số bị mất số 0 đầu tiên (Excel hiểu số điện thoại là một dãy các ký tự số bình thường, do đó sẽ loại đi số 0 dư thừa) gây khó khăn trong quá trình xem và thu thập dữ liệu.
VD hàm TEXT trong excel
Lưu ý: Cách này chỉ áp dụng được khi số lượng chữ số trong số điện thoại của tất cả ô dữ liệu đều bằng nhau (ví dụ: Dữ liệu của bạn chỉ chứa số điện thoại có 10 chữ số). Để định dạng được dãy số điện thoại trên bạn thực hiện các bước sau:
Bước 1: Tại vị trí ô D3 bạn nhập cú pháp =”0″&TEXT(C3,”#### ### ###”) để hàm trong excel tiến hành định dạng.
VD hàm TEXT trong excel
Bước 2: Sau khi nhập xong cú pháp bạn ấn ENTER để hoàn thành và COPY công thức đến vị trí các ô còn lại để định dạng cho các số điện thoại còn lại.
VD hàm TEXT trong excel
Liên kết văn bản với số hoặc giá trị khác bằng hàm lồng TEXTJOIN – TEXT
Khái niệm hàm TEXTJOIN trong excel
Trong bản Excel 365 có một hàm với cái tên dễ bị nhầm lẫn với hàm TEXT đó chính là hàm TEXTJOIN có tác dụng dùng để liên kết các đoạn văn bản từ nhiều phạm vi khác nhau hay kết hợp nhiều chuỗi với nhau thành một chuỗi văn bản hoàn thiện.
Cú pháp hàm nối TEXTJOIN trong excel
Cú pháp hàm TEXTJOIN
=TEXTJOIN(delimiter, ignore_text, text1, text2,….)
Chú thích công thức:
- delimiter: Kí tự phân cách giữa các chuỗi văn bản, được khi trong dấu ngoặc kép “”.
- ignore_text: Giá trị TRUE/FALSE. Giá trị TRUE hàm sẽ bỏ qua những ô trống. Giá trị FALSE sẽ bao gồm những ô trống
- text1, text2,…: Các chuỗi văn bản cần được ghép với nhau.
Ứng dụng của hàm TEXTJOIN
Ví dụ 3: Sử dụng hàm TEXTJOIN để nối chuỗi “Thành tiền: “ trước đoạn ký tự tính thành tiền của các sản phẩm trong bảng GIÁ TIỀN SẢN PHẨM BÁCH HÓA (tính thành tiền bằng hàm TEXT với định dạng “$###,###.00”).
VD hàm TEXTJOIN trong excel
Đây là một hàm tương đối rắc rối nên các bạn chú ý thực hiện theo các bước như sau:
Bước 1: Bạn tiến hành nhập cú pháp =TEXTJOIN(” “,TRUE,”Thành tiền: “,TEXT(B3*C3,”$###,###.00”)) vào địa chỉ ô D3 để hàm tiến hành quá trình tính toán và ghép các chuỗi với nhau.
Chú thích công thức:
- ” “: Kí tự phân cách giữa các chuỗi văn bản.
- TRUE: Giá trị TRUE hàm sẽ bỏ qua những ô trống. Trong trường hợp này không có ô trống nên bạn có thể sử dụng TRUE hoặc FALSE đều được.
- “Thành tiền: “: Chuỗi văn bản đầu tiên được ghép trong hàm.
- TEXT(B3*C3,”$###,###.00″): Chuỗi văn bản thứ 2 được ghép trong hàm, với kiểu định dạng “$###,###.00”.
VD hàm TEXTJOIN trong excel
Bước 2: Sau khi nhập xong công thức như trên bạn sẽ ấn ENTER để hàm hiển thị kết quả, sau đó COPY công thức đến vị trí các ô còn lại để tính thành tiền và định dạng cho những ô đó.
VD hàm TEXTJOIN trong excel
Lưu ý khi dùng hàm định dạng TEXT trong excel
- Kết quả trả về của hàm TEXT sẽ luôn là chuỗi văn bản, ngay cả khi hình thức hiển thị của nó trông giống hệt như dữ liệu số.
- Với dữ liệu là số thập phân thì bạn nên thống nhất dùng dấu chấm (.) để làm dấu phân cách giữa phần nguyên và phần thập phân. Còn dấu phẩy (,) dùng làm dấu phân cách hàng nghìn, Vì các bản Excel thường sẽ được thiết lập mặc định như vậy.
Lỗi thường gặp khi sử dụng hàm định dạng TEXT
Một lỗi thường gặp khi các bạn sử dụng hàm TEXT trong excel đó là lỗi #NAME?. Nguyên nhân là do người dùng không sử dụng dấu ngoặc kép (“”) ở mã định dạng. Các bạn cần chú ý điều này khi sử dụng hàm TEXT trong excel để tránh bị lỗi nhé.
Cảm ơn các bạn đã theo dõi bài viết trên của NTP và trong bài viết trên là những kiến thức chi tiết nhất về cách sử dụng hàm TEXT trong excel cùng với đó là sự kết hợp giữa hàm TEXT và TEXTJOIN để giải quyết một số bài toán phức tạp hơn trong excel. Hy vọng rằng với những chia sẻ trên của nguyentrongphuc.net có thể giúp bạn giải quyết tốt các vấn đề trong công việc cũng như trong học tập.
