Trong quá trình sử dụng cũng như học Excel online bạn thường xuyên phải sử dụng các định dạng để chuyển đổi giá trị số liệu, chuỗi văn bản hiển thị đúng chuẩn. Trong đó phải nhắc đến hàm Text trong Excel được sử dụng để chuyển đổi giá trị số sang chuỗi văn bản. Giúp cho các công thức được thực hiện tính toán đúng chuẩn. Hãy cùng Box.edu tìm hiểu về hàm Text này nhé!
Xem thêm: Bật mí cách sử dụng hàm Right trong Excel mới nhất
Xem thêm: Cách sử dụng hàm Offset trong Excel để tham chiếu dữ liệu
Mục lục bài viết
Hàm TEXT là gì? Công thức và ứng dụng trong Excel
Định nghĩa
Trong Excel hàm Text là một trong những hàm giúp bạn chuyển đổi giá trị số sang chuỗi văn bản. Đây là một kiểu chuyển đổi định dạng cụ thể, phổ biến.
Hàm Text trong Excel được ứng dụng để giúp hiển thị các số liệu dưới dạng định dạng dễ nhìn và dễ dàng sử dụng hơn. Hàm cũng được kết hợp các số ngay tháng với văn bản hoặc một số ký tự nhất định cho người dùng sử dụng.
Công thức
=TEXT(value, format_text)
Trong đó:
- value: Chuỗi giá trị số hoặc ô chứa giá trị cần chuyển sang văn bản. (Có thể là số, ngày, tháng).
- format_text: Định dạng bạn muốn áp dụng cho chuỗi giá trị. Sử dụng các mã để khai báo cho hàm. Mã định dạng có thể được đặt trong dấu ngoặc kép “mm/dd/yy”,”# ?/?”
Lưu ý:
Hàm Text chứa chuỗi giá trị cần chuyển đổi trả về kết quả là chuỗi văn bản. Ngay cả khi hiển thị dưới dạng hình thức không giống với dữ liệu số.
Đối với trường hợp dữ liệu là các số thập phân thì bạn nên sử dụng thống nhất dấu chấm. Dấu chấm được sử dụng để phân cách phần nguyên và phần thập phân. Với dấu phẩy sẽ phân cách số hàng nghìn. Với các bản Excel thường sẽ thiết lập mặc định sẵn như vậy.
Phân loại mã định dạng hàm TEXT
Trong hàm Text sử dụng các mã định dạng như sau:
Cách sử dụng hàm TEXT trong Excel
Sử dụng hàm TEXT để kết nối văn bản và số hoặc ngày
Để giúp bạn hiểu rõ hơn về cách sử dụng hàm TEXT để kết nối các đoạn văn bản gồm số và ngày chúng tôi có 2 ví dụ minh hoạ chi tiết giúp bạn dễ hiểu, dễ thao tác theo:
Ví dụ minh hoạ 1
Sử dụng hàm Text để chèn định dạng số ngay bên trong chuỗi văn bản dưới đây.
Bảng số liệu yêu cần bạn tính tổng số tiền khách cần trả trong cột thành tiền với công thức:
Thành tiền = Đơn giá x Số lượng x % còn lại sau khi được giảm giá
(ở đây sẽ lấy 1 – % giảm)
Nếu bạn thực hiện tính toán đơn giản, ngắn gọn, dễ thao tác thì chỉ cần nhập theo cú pháp bình thường:
=A2*B2*(1-C2)
Như vậy bảng số liệu sẽ trả về kết quả là 156.8
Nếu bạn chưa hài lòng với kết quả hiển thị dưới dạng một con số, không có đơn vị đo. Hiển thị này cũng không được định dạng đẹp mắt. Không có dòng chú thích khiến thứ tự các con số lẫn lộn với nhau gây rối mắt người xem.
Để khắc phục được điều này bạn có thể thực hiện thêm đoạn Text “Thành tiền” trước giá tiền. Và để giá tiền hiển thị thêm biểu tượng tiền tệ. Thêm dấu phân cách hàng nghìn và hàng thập phân là dấu chấm.
Sử dụng hàm Text để khắc phục lỗi này. Bạn có thể sử dụng 1 trong 2 cách sau:
Cách 1: Thực hiện nối chuỗi ký tự với hàm Text để hệ thống ký hiệu:
Sử dụng công thức
=”Thành tiền: “&TEXT(A2*B2*(1-C2),”$###,###.00”)
Kết quả hiển thị như sau:
Cách 2: Nối chuỗi ký tự hàm Text bằng hàm CONCATENATE
Hàm CONCATENATE có công thức như sau:
=CONCATENATE (text1, text2,…)
Trong đó
text: Chuỗi văn bản, tham chiếu ô hoặc giá trị trong công thức
Áp dụng công thức hàm và nhập công thức sau:
=CONCATENATE(“Thành tiền: “,TEXT(A2*B2*(1-C2),”$###,###.00”))
Kết quả hiển thị như sau:
Ví dụ minh hoạ 2
Sử dụng hàm trong Excel để kết hợp văn bản và ngày tháng trong bảng dữ liệu dưới đây. Yêu cầu bạn thêm một dòng chú thích “Ngày khởi tạo dữ liệu: “. Sau đó đồng thời muốn hiển thị ngày tháng theo thứ tự Ngày, tháng, năm.
Để giải quyết yêu cầu trên bạn có thể sử dụng hàm Text lồng với hàm Today. Sau đó thực hiện khai báo mã định dạng “dd/mm/yyyy”.
Bạn có thể thực hiện theo 1 trong 2 cách sau:
Cách 1: Sử dụng hệ thống các ký hiệu để nối các đoạn chuỗi ký tự với hàm Text theo công thức sau:
=”Ngày khởi tạo dữ liệu: ” & TEXT(TODAY(), “dd/mm/yyyy”)
Kết quả hiển thị như sau:
Cách 2: Sử dụng hàm Text kết hợp với hàm CONCATENATE
Nhập công thức sau:
=CONCATENATE(“Ngày khởi tạo dữ liệu: “, TEXT(TODAY(), “dd/mm/yyyy”))
Kết quả hiển thị như sau:
Thêm số 0 đứng đầu mỗi dãy số
Ví dụ minh hoạ 1:
Bạn thêm số 0 đứng đầu mỗi dãy số trong một cột dữ liệu. Những con số này cần phải tương ứng với đơn vị số được hiển thị trong dãy.
Nhập công thức sau:
= Text(A2,”000000″)
Kết quả hiển thị như sau:
Lưu ý: Với dữ liệu có thể trả về của hàm Text trong chuỗi văn bản. Bạn có thể không dùng đến dữ liệu này để thực hiện phép tính.
Ví dụ minh hoạ 2:
Thực hiện thêm số 0 vào trước chuỗi dữ liệu là số điện thoại đã bị Excel làm mất chữ số 0 đầu tiên.
Bạn có thể áp dụng cách làm này khi thu về một bảng dữ liệu gồm các số điện thoại. Nhưng các số này đều bị mất số 0 đầu dãy ký tự.
Lý do xảy ra trường hợp này là do Excel hiểu lầm dãy số điện thoại là một chuỗi giá trị số (có số 0 ở đầu là dư thừa, nên xoá đi. Các số liệu bên trong vẫn được giữ nguyên giá trị của nó)
Cách này gây khó khăn, nhầm lẫn cho người xem. Khiến việc thu nhập và sử dụng số điện thoại bị sai, thiếu sót.
Bạn có thể sử dụng công thức sau để khắc phục lỗi này:
=”0″&Text(C2,”#### ### ###”)
Lưu ý: Với cách làm này bạn có thể chỉ áp dụng được các số lượng chữ số ngay trong điện thoại của các ô dữ liệu bằng nhau.
Chuyển giá trị thành số điện thoại theo định dạng
Trong Excel hàm Text cho phép người dùng sử dụng dấu gạch ngang (-) và dấu ngoặc đơn () trong mã định dạng. Các dấu ký tự này tạo thành những chuỗi văn bản có định dạng phù hợp với yêu cầu sử dụng của người dùng.
Với các số điện thoại có chứa mã vùng thì bạn có thể sử dụng định dạng như hình minh hoạ. Với thiết lập định dạng số điện thoại như sau: (123) 456-7890
Bên cạnh đó bạn có thể chuyển số điện thoại được nhập tại ô C2 thành 0335 263 321 chứa khoảng cách. Bạn sử dụng công thức sau:
=”0″&TEXT(C2, “### ### ###”)
Bạn có thể sử dụng dấu cách để thực hiện phân cách các nhóm số. Sau khi áp dụng công thức kết quả hiển thị như sau:
Một số lỗi khi sử dụng hàm TEXT
Lỗi #NAME
Nguyên nhân gây ra lỗi #NAME? này là do người dùng không sử dụng dấu ngoặc kép “” ở mã định dạng.
Ví dụ bạn nhập công thức
=TEXT(D2, mm/dd/yy)
Công thức trên không có dấu “” ở mã định dạng nên kết quả trả về hiển thị lỗi #NAME?.
Để khắc phục lỗi này bạn chỉ cần thêm dấu ngoặc kép vào mã định dạng. Công thức hoàn chỉnh như sau:
=TEXT(D2, “mm/dd/yy”)
Hàm báo sai cú pháp
Có 2 nguyên nhân khiến hàm báo sai cú pháp:
Nguyên nhân 1:
Trong quá trình nhập công thức bạn nhập sai chính tả. Hoặc có thể do bạn copy từ nguồn tin khác về bị sai khiến Excel không thể thực hiện được lệnh.
Trường hợp các dấu ngoặc kép “” trong mã định dạng bị định dạng sai cũng sẽ hiển thị báo lỗi. Excel sẽ hiển thị Lỗi như sau: There’s a problem with this formula
Để khắc phục lỗi này bạn chỉ cần xoá dấu ngoặc kép cũ có trong công thức và nhập lại bằng tay cho đúng định dạng để hàm thực hiện.
Nguyên nhân 2:
Ngoài ra lỗi hàm báo sai cú pháp cũng xuất hiện ngay cả khi bạn nhập đúng công thức hàm rồi. Tuy nhiên vì khác nhau trong phần thiết lập phần mềm ở mỗi phiên bản của từng nước.
Một số nước sử dụng thiết lập phần mềm Excel tối ưu sử dụng theo nhu cầu, ngôn ngữ của nước đó. Nên sẽ có đôi số điểm khác với cài đặt phần mềm của Việt Nam.
Ví dụ trong phiên bản của nước Đức được cài đặt khác với Việt Nam. Nếu bình thường ở Việt Nam nhập công thức
=TEXT(A1, “mm/dd/yy”)
Nhưng người Đức lại nhập theo công thức
=TEXT(A1; “mm/tt/jj”)
Họ thay thế chữ “d” thành “t”và chữ “y” thành “j”
Để khắc phục vấn đề này bạn có thể tìm hiểu để thực hiện khai báo cú pháp theo các thiết lập sẵn ở phiên bản của mình sử dụng. Hoặc có thể chuyển đổi sang một phiên bản khác gần gũi với ngôn ngữ của bạn hơn.
Tổng kết
Hy vọng bài viết vừa chia sẻ đến bạn toàn bộ những kiến thức về hàm Text trong Excel. Với những kiến thức được hướng dẫn trên đã giúp bạn dễ dàng chuyển đổi dữ liệu số thành chuỗi văn bản và tuỳ chỉnh định dạng theo ý muốn. Chúc bạn thành công!
