Trong nội dung bài viết này, Học Excel Online sẽ phân tích và lý giải các đặc thù của hàm Text vào Excel còn chỉ ra những phương pháp tối ưu để thực hiện công thức hàm Text vào Excel.

Bạn đang xem: Các hàm text trong excel


HÀM TEXT trong EXCEL – CÔNG THỨC VÀ CÁCH SỬ DỤNG CƠ BẢN

Hàm Text được thực hiện để chuyển đổi giá trị số sang trọng chuỗi văn phiên bản ở một định dạng vắt thể.

Công thức: TEXT(giá trị, định dạng_văn bản)

Cụ thể:

Giá trị: giá trị số đề nghị chuyển thanh lịch văn bản. Có thể là số, ngày tháng, là tham chiếu của một ô cất giá trị số hay như là một hàm khác trả về hiệu quả là số hay ngày.Định dạng văn bản: định dạng bắt buộc sử dụng, cung cấp thể thức của mã định dạng được để trong vệt ngoặc kép, như “mm/dd/yy”.

Hàm Text thích hợp với tất cả các phiên bạn dạng Excel 2016, Excel 2013, 2010, 2007, 2003 cùng thấp hơn.

Thông thường, hàm Text vào Excel được sử dụng trong số trường hợp sau đây:

Hiển thị số theo phong cách dễ hiểu hơn hoặc theo một định dạng thuận tiện hơn cho tất cả những người sử dụng.Hiển thị ngày tháng theo một định dạng nhất địnhKết hợp các số hoặc ngày tháng với văn phiên bản hoặc các kí tự độc nhất định.

Ví dụ, nhằm kéo ngày trường đoản cú ô A2 sang ô không giống theo định dạng truyền thống cuội nguồn như “Jan 1, 2016”, thực hiện công thức sau:


*

*

=TEXT(A2, “mmmm d, yyyy”)

Kết trái trả về:

*

Lưu ý quan tiền trọng: hàm Text vào Excel được dùng làm chuyển cực hiếm số quý phái chuỗi văn bản. Bởi vì vậy, tác dụng hàm Text chuyển ra cần yếu sử dụng trong những phép tính khác. Nếu như muốn, có thể giữ nguyên giá chỉ trị nơi bắt đầu và dùng công thức khác.

MÃ ĐỊNH DẠNG HÀM TEXT trong EXCEL

Như đang thấy, công thức hàm Text rất solo giản. Mỗi phần được cấp một mã định dạng phù hợp và kết quả trả về số theo phong cách mong muốn. Về cơ bản, hàm Text đồng ý hầu hết những định dạng mã được áp dụng trong format Excel. Bảng dưới đây liệt kê đều mã format cơ bản nhất.

Mô tảĐịnh dạng mã mẫu
0Chữ số diễn tả những số 0 vô nghĩa#.00 tính mang đến số thập phân lắp thêm 2.

Nếu nhập 2.5. Trong ô tham chiếu, số hiển thị là 2.50

#Dấu chống không màn biểu diễn thêm số 0#.## – tính mang lại số thập phân lắp thêm 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 không cho số 0 vô nghĩa nhưng không trình diễn chúng. Thường được dùng để sắp xếp các số vào cột trên một điểm thập phân#.?? biểu diễn tối đa 2 phần thập phân với căn nhập cho các điểm thập phân trong một cột
.Điểm thập phân
,Ngăn giải pháp hàng nghìn###,###.## biểu hiện dấu phân bóc tách hàng nghìn với 2 số thập phân
Cùng đó, rất có thể sử dụng thuộc với bất kể kí từ nào dưới đây trong mã định dạng, công dụng thể hiện được vẫn giữ nguyên kí tự

+ với –Dấu cùng và trừ
()Dấu ngoặc đơn
:Dấu :
Dấu nháy
Dấu ngoặc nhọn
Nhỏ hơn hoặc bự hơn
=Dấu bằng
/Dấu gạch ốp chéo

Bảng sau đây chỉ cách áp dụng công thức hàm Text trong Excel nhằm ứng dụng các định dạng khác nhau ra cực hiếm tương đương.

ABC
1Giá trị gốcGiá trị định dạngCông thức
25.55.50=TEXT(A2, “0.00”)
3550%=TEXT(A2,”#%”)
4$5.50=TEXT(A2, “$#,##0.00”
5+$5.50=TEXT(A2,”+$#,##0.00;-$#,##0.00;$0.00″)
6-$5.50=TEXT(A2, “- $#,##0.00;- $#,##0.00;$0.00”)
75 ½=TEXT(A2,”# ?/?”)
85.50E+00=TEXT(A2,”0.00E+00″)
9~6!=TEXT(A2,”~# !”)

Khi áp dụng hàm TEXT vào Excel cùng với ngày với giờ, có thể sử dụng mã format sau:

Mã định dạngMô tảVí dụ
d (day)Ngày của tháng hoặc ngày của nămd – một hoặc 2 số không chứa số 0 làm việc đầu (từ 1 mang đến 31)

dd – 2 số đựng số 0 ngơi nghỉ đầu (01 đến 31)

ddd – 3 kí trường đoản cú viết tắt (Mon – Sun (thứ 2 cho chủ nhật)

m (month)Tháng (được sử dụng như một phần của ngày)m – một hoặc 2 số không chứa số 0 làm việc đầu (1 mang đến 12)

mm – 2 số đựng số 0 sinh sống đầu (01 – 12)

mmm – viết tắt của tháng (Jan – Dec)

mmmm – chữ đầy đủ của tháng

y (year)Nămyy- một hoặc 2 số (ví dụ 06 là 2006, giỏi 16 là 2016)

yyyy – 4 chữ số (2006,2016)

h (hour)Giờh – một hoặc nhì chữ số không đựng số 0 nghỉ ngơi đầu (1-60)

hh -2 chữ số đựng số 0 nghỉ ngơi đầu (01-60)

m (minute)Phútm – một hoặc nhị chữ số không cất số 0 sinh sống đầu (1-60)

mm -2 chữ số cất số 0 nghỉ ngơi đầu (01-60)

S (second)giâys – một hoặc nhị chữ số không cất số 0 ở đầu (1-60)

ss -2 chữ số cất số 0 làm việc đầu (01-60)

Trang tính dưới thể hiện một trong những công thức hàm Text mang đến ngày:

CÁCH SỬ DỤNG HÀM TEXT vào EXCEL – CÔNG THỨC MẪU

Có thể thấy, vận dụng của hàm Text vào Excel còn tương đối hạn chế (bởi quý hiếm hiển thị hoàn toàn có thể dễ dàng chũm đổi bằng cách sử dụng hộp Định dạng ô gồm sẵn thông qua chương trình lập trình sẵn ô hoặc phím tắt Ctrl+1.

Xem thêm: Cách Hiện File Trong Usb Bị Ẩn Trong Usb Win 10 Nhanh, Cách Hiện File Ẩn Trong Usb Đơn Giản

KẾT NỐI VĂN BẢN VÀ SỐ (HOẶC NGÀY) THEO ĐỊNH DẠNG TÙY CHỈNH

Khi tạo thành tóm tắt hoặc báo cáo, người tiêu dùng thường không chỉ là muốn tính tổng vào Excel, tỷ lệ và rất nhiều phần tương tự, hơn nữa giải thích cho người dùng ý nghĩa sâu sắc của chúng. Để thực hiện, sử dụng hàm Concatenate nhằm nhập văn bạn dạng và số, và áp dụng hàm Text để mô tả số (hay ngày) theo nhu cầu của người dùng.

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

Giả sử, phải tính tổng số lượng dựa trên giá bán của đối tượng người tiêu dùng trong ô A2, số lượng đối tượng người sử dụng trong ô B2, và bớt tỉ lệ trong ô C2 thực hiện phép tính: =A2*B2*(1-C2). Đối với người tiêu dùng biết chính xác ý nghĩa sâu sắc của tác dụng trả về, người sử dụng muốn chèn thêm câu phân tích và lý giải như: “Your price is” – “giá của công ty là”. Thuộc đó, người tiêu dùng muốn hiển thị hình tượng tiền tệ, lốt phân cách hàng nghìn và nhì chữ số thập phân.

Để hoàn thành, bổ sung cập nhật công thức tính vào đối số đầu tiên của hàm Text, bao hàm mã định dạng khớp ứng trong đối số sản phẩm 2, và nối hàm Text cùng với chuỗi bằng phương pháp sử dụng hệ thống kí hiệu hoặc hàm Concatenate:

=“Your price is “&TEXT(A2*B2*(1-C2), “$###,###.00”)

Hoặc

= CONCATENATE(“Your price is “,TEXT(A2*B2*(1-C2), “$###,###.00”))

Kết trái trả về tương tự bảng sau:

*

Ví dụ 2: phối hợp văn phiên bản và tháng ngày theo định dạng hy vọng muốn

Khi trở về ngày hiện tại bằng phương pháp sử dụng hàm Today() tuyệt Now, có thể người cần sử dụng muốn màn trình diễn ngày bằng phương pháp kết phù hợp với một vài ba câu, hay đoạn để tránh gây ngờ vực hay nhầm lẫn.

Tuy nhiên, nếu người dùng muốn nối văn phiên bản với ngày tháng theo phong cách thông thường:

=CONCATENATE(“Today is “, TODAY())

Excel đã trả về kết quả rất lạ, như “Today is 42198”.

Đó là do hệ thông xây dựng của Excel, tháng ngày được lưu lại bằng các con số. Bởi vậy, những con số này lộ diện trong chuỗi văn bạn dạng nối. Để tự khắc phục chứng trạng nay, thực hiện hàm Text để trình diễn ngày tháng theo nhu cầu của tín đồ dùng.

Ví dụ, gắn hàm Today trong phương pháp hàmText bao gồm mã định dạng “dddd d mmm,yyyy”, công dụng trả về đang là: “Today is Monday 12 Dec, 2016”

Công thức tổng thể như sau:

=CONCATENATE(“Today is “, TEXT(TODAY(), “dddd d mmm, yyyy”))or=”Today is ” và TEXT(TODAY(), “dddd d mmm, yyyy”)

*

THÊM SỐ 0 ĐỨNG ĐẦU MỖI SỐ VỚI ĐỘ DÀI NGẮN KHÁC NHAU

Microsoft Excel auto xóa các số 0 dẫn đầu mỗi con số trong một ô, điều này không có tác động gì trong phần nhiều các trường hợp. Mặc dù nhiên, nếu người dùng muốn giữ nguyên những số 0 đứng trước này thì sao?

Hàm Text vào Excel hoàn toàn có thể dễ dàng thêm phần đông số 0 đứng trước những con số trong một cột, ngay cả khi những số lượng đó không tồn tại giá trị tương đương. Đơn giản bằng phương pháp dùng mã format chưa đông đảo số 0 vào đầu như “0000000”, trong đó, đều số 0 phải tương ứng với đơn vị chức năng số được hiển thị.

Ví dụ, để trình diễn số gồm 7 chữ số cùng với số 0 đứng đầu, dùng công thức sau: (A2 là ô cất giá trị gốc)

=TEXT(A2,”0000000″)

Trong bảng dưới đây, bí quyết hàm Text thêm sẽ thêm số 0 mở màn theo yêu ước để tạo thành chuỗi 7 kí tự. (Lưu ý: công dụng trả về của hàm Text trong Excel luôn là chuỗi văn bản, ngay cả khi kết quả trả về tưởng chừng là số).

*

CHUYỂN GIÁ TRỊ THÀNH SỐ ĐIỆN THOẠI THEO MỘT ĐỊNH DẠNG NHẤT ĐỊNH

Chuyển quý giá số trong ô thành đầy đủ số điện thoại thông minh có vẻ là một trong nhiệm vụ cạnh tranh nhằn, tuy nhiên, hãy đừng quên hàm Text vào Excel cho phép sử dụng lốt gạch ngang (-) và dấu ngoặc đối kháng () trong mã định dạng.

Bởi vậy, để biểu diễn số trong ô A2 theo số điện thoại cảm ứng 7 chữ số truyền thống cuội nguồn theo format 123-456, áp dụng công thức:

=TEXT(A2, “###-####”)

Nếu một vài cực hiếm gốc đựng mã khi vực (VD: có thể là 7 chữ số, hay 10 chữ số), bao hàm mã định dạng có điều kiện tiếp sau đây để hiển thị số gồm 10 chữ số theo format (123) 246-789:

=TEXT(A2,”

So với những hàm không giống trong Excel, hàm Text dễ dàng và đơn giản hơn vô cùng nhiề, tưởng như không tồn tại chút trở ngại nào cản trở. Trường hợp trong trường hợp hàm Text không thực hiện được, lí vì chưng của phần đông trường hợp là vì sai mã định dạng. Dưới đó là 2 trường thích hợp điển hình:

Hàm Text trả về lỗi #NAME? nếu người dùng không cần sử dụng dấu ngoặc kép “” bên cạnh mã định dạng

Ví dụ công thức =TEXT(A2, mm/dd/yy) bị lỗi, bắt buộc sửa thành: =TEXT(A2, “mm/dd/yy”)

Hàm Text trong Excel có ngữ điệu đặc trưng, yêu cầu tất cả mã format ngày với giờ nắm thể.

Ví dụ, phương pháp =TEXT(A2, “mm/dd/yy”) vận động tốt với người tiêu dùng sử dụng giờ đồng hồ Anh, nhưng hồ hết vùng khác thì không. Điển hình, nghỉ ngơi Đức, người dùng cần sử dụng định dạng sau: “t” cố cho “d” cùng “j” thay cho “y”. Bởi vì vậy, trong ứng dụng Excel của Đức, công thức bên trên sẽ thay bằng =TEXT(A2; “mm/tt/jj”).

Trên đó là bài giảng về hàm Text vào Excel, chúc chúng ta thành công!

Ngoài ra để áp dụng Excel vào công việc một cách công dụng thì bạn còn buộc phải sử dụng giỏi các hàm, các công ráng khác của Excel:

Một số hàm cơ bản thường gặp gỡ như:

SUMIF, SUMIFS nhằm tính tổng theo 1 điểm kiện, những điều kiệnCOUNTIF, COUNTIFS nhằm thống kê, đếm theo một điều kiện, các điều kiệnCác hàm xử lý tài liệu dạng chuỗi, dạng ngày tháng, dạng số…Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ hay được sử dụng như:

Định dạng theo điều kiện với Conditional formattingThiết lập điều kiện nhập dữ liệu với Data ValidationCách đặt Name và áp dụng Name trong công thứcLập báo cáo với Pivot Table…

Rất nhiều kỹ năng và kiến thức phải không nào? tổng thể những kiến thức này các bạn đều rất có thể học được trong khóa học EX101 – Excel trường đoản cú cơ phiên bản tới chăm gia của học tập Excel Online. Đây là khóa học giúp đỡ bạn hệ thống kỹ năng một biện pháp đầy đủ, chi tiết. Hơn nữa không còn có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, thuận tiện tra cứu lại kỹ năng và kiến thức khi cần. Bây giờ hệ thống đang sẵn có ưu đãi khôn cùng lớn cho mình khi đk tham gia khóa học. Cụ thể xem tại: socialgame.vn.Online