Trong 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 trong Excel và chỉ ra các cách tối ưu để áp dụng công thức hàm Text vào Excel.
Bạn đang xem: Hàm lấy giá trị 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 sử dụng để chuyển đổi giá trị số sang trọng chuỗi văn bạn dạng ở một định dạng cầm cố thể.Công thức: TEXT(giá trị, định dạng_văn bản)
Cụ thể:
Giá trị: quý giá số bắt buộc chuyển quý phái 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 là một hàm khác trả về kết quả là số hay ngày.Định dạng văn bản: định dạng yêu cầu sử dụng, cung cấp thể thức của mã định hình đượ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 phiên bản 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 những trường vừa lòng sau đây:
Hiển thị số theo phong cách dễ hiểu hơn hoặc theo một định dạng dễ ợt hơn cho tất cả những người sử dụng.Hiển thị ngày tháng theo một định hình nhất địnhKết hợp các số hoặc ngày tháng với văn bản hoặc những kí tự tuyệt nhất định.
Ví dụ, để kéo ngày trường đoản cú ô A2 sang trọng ô không giống theo định dạng truyền thống lâu đời như “Jan 1, 2016”, sử dụng công thức sau:
=TEXT(A2, “mmmm d, yyyy”)
Kết quả trả về:
Lưu ý quan lại trọng: hàm Text trong Excel được dùng làm chuyển cực hiếm số quý phái chuỗi văn bản. Vì vậy, kết quả hàm Text đưa ra bắt buộc sử dụng trong các phép tính khác. Ví như muốn, hoàn toàn có thể giữ nguyên giá bán trị gốc và dùng cách làm khác.
MÃ ĐỊNH DẠNG HÀM TEXT vào EXCEL
Như sẽ thấy, cách làm hàm Text rất đối chọi giản. Mỗi phần được cấp cho 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 chấp nhận hầu hết các định dạng mã được thực hiện trong định dạng Excel. Bảng sau đây liệt kê đều mã định hình cơ bạn dạng nhất.
| Mã | Mô tả | Định dạng mã mẫu |
| 0 | Chữ số bộc lộ những số 0 vô nghĩa | #.00 tính mang đến số thập phân thứ 2. Nếu nhập 2.5. Trong ô tham chiếu, số hiển thị là 2.50 |
| # | Dấu chống không trình diễn thêm số 0 | #.## – tính cho số thập phân trang bị 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 dẫu vậy không trình diễn chúng. Thường được dùng để làm sắp xếp các số vào cột tại một điểm thập phân | #.?? trình diễn tối đa 2 phần thập phân và 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 bí quyết hàng nghìn | ###,###.## biểu hiện dấu phân tách bóc hàng nghìn và 2 số thập phân |
| + và – | 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 khủng hơn | |
| = | Dấu bằng |
| / | Dấu gạch ốp chéo |
Bảng tiếp sau đây chỉ cách sử dụng công thức hàm Text vào Excel để ứng dụng các định dạng khác nhau ra quý hiếm tương đương.
| A | B | C | |
| 1 | Giá trị gốc | Giá trị định dạng | Công thức |
| 2 | 5.5 | 5.50 | =TEXT(A2, “0.00”) |
| 3 | 550% | =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”) | |
| 7 | 5 ½ | =TEXT(A2,”# ?/?”) | |
| 8 | 5.50E+00 | =TEXT(A2,”0.00E+00″) | |
| 9 | ~6! | =TEXT(A2,”~# !”) |
Khi sử dụng hàm TEXT vào Excel cùng với ngày với giờ, hoàn toàn có thể sử dụng mã định hình sau:
| Mã định dạng | Mô tả | Ví dụ |
| d (day) | Ngày của tháng hoặc ngày của năm | d – một hoặc 2 số không chứa số 0 nghỉ ngơi đầu (từ 1 cho 31) dd – 2 số cất số 0 sống đầu (01 mang đến 31) ddd – 3 kí tự viết tắt (Mon – Sun (thứ 2 mang lại chủ nhật) |
| m (month) | Tháng (được thực hiện như một trong những phần của ngày) | m – một hoặc 2 số không đựng số 0 ở đầu (1 mang lại 12) mm – 2 số chứa số 0 nghỉ ngơi đầu (01 – 12) mmm – viết tắt của mon (Jan – Dec) mmmm – chữ không hề thiếu của tháng |
| y (year) | Năm | yy- một hoặc 2 số (ví dụ 06 là 2006, hay 16 là 2016) yyyy – 4 chữ số (2006,2016) |
| h (hour) | Giờ | h – một hoặc nhị chữ số không cất số 0 sinh hoạt đầu (1-60) hh -2 chữ số chứa số 0 ở đầu (01-60) |
| m (minute) | Phút | m – một hoặc nhị chữ số không cất số 0 ngơi nghỉ đầu (1-60) mm -2 chữ số cất số 0 sống đầu (01-60) |
| S (second) | giây | s – một hoặc hai chữ số không cất số 0 làm việc đầu (1-60) ss -2 chữ số chứa số 0 sinh hoạt đầu (01-60) |
CÁCH SỬ DỤNG HÀM TEXT vào EXCEL – CÔNG THỨC MẪU
Có thể thấy, áp dụng của hàm Text vào Excel còn khá hạn chế (bởi quý giá hiển thị hoàn toàn có thể dễ dàng gắng đổi bằng phương pháp sử dụng vỏ hộp Định dạng ô tất cả sẵn trải qua chương trình xây dựng ô hoặc phím tắt Ctrl+1.
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 nên 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, phần trăm và phần đa phần tương tự, bên cạnh đó giải thích cho người dùng chân thành và ý nghĩa của chúng. Để thực hiện, sử dụng hàm Concatenate nhằm nhập văn phiên bản và số, và áp dụng hàm Text để biểu lộ số (hay ngày) theo ý muốn của tín đồ dùng.
Ví dụ 1. Định dạng số phía bên trong một chuỗi văn bản
Giả sử, phải tính tổng con số 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 dùng trong ô B2, và sút tỉ lệ trong ô C2 sử dụng phép tính: =A2*B2*(1-C2). Đối với người sử dụng biết bao gồm xác chân thành và ý nghĩa của hiệu quả trả về, người sử dụng muốn chèn thêm câu giải thích như: “Your price is” – “giá của doanh nghiệp là”. Cùng đó, người tiêu dùng muốn hiển thị biểu tượng tiền tệ, lốt phân cách hàng ngàn và hai 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ố thứ nhất của hàm Text, bao gồm mã định dạng tương ứng trong đối số vật dụng 2, và nối hàm Text cùng với chuỗi bằng phương pháp sử dụng khối 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ề giống như bảng sau:
Ví dụ 2: phối kết hợp văn bạn dạng 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 cách sử dụng hàm Today() giỏi Now, hoàn toàn có thể người dùng muốn màn biểu diễn ngày bằng cách kết hợp với một vài ba câu, tuyệt đoạn nhằm tránh gây nghi vấn hay nhầm lẫn.
Tuy nhiên, nếu người dùng muốn nối văn bạn dạng với ngày tháng theo phong cách thông thường:
=CONCATENATE(“Today is “, TODAY())
Excel đang trả về tác dụng rất lạ, như “Today is 42198”.
Đó là vì hệ thông xây dựng của Excel, ngày tháng được lưu bằng những con số. Vì chưng vậy, những con số này mở ra trong chuỗi văn bản nối. Để tự khắc phục triệu chứng nay, thực hiện hàm Text để màn trình diễn ngày tháng theo nhu cầu của người dùng.
Ví dụ, gắn hàm Today trong bí quyết hàm
Text bao gồm mã định hình “dddd d mmm,yyyy”, hiệu quả trả về đã 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 tự động xóa hồ hết số 0 mở màn mỗi con số trong một ô, vấn đề này không có tác động gì trong số đông các ngôi trường hợp. Mặc dù nhiên, nếu người tiêu dùng muốn không thay đổi những số 0 đứng trước này thì sao?
Hàm Text vào Excel rất có thể dễ dàng thêm đầy đủ số 0 đứng trước những số lượng trong một cột, trong cả khi những số lượng đó không tồn tại giá trị tương đương. Đơn giản bằng cách dùng mã định hình chưa phần nhiều số 0 vào đầu như “0000000”, trong đó, mọi số 0 phải tương ứng với đơn vị số được hiển thị.
Ví dụ, để biểu diễn số tất cả 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 đã thêm số 0 mở màn theo yêu cầu để tạo thành chuỗi 7 kí tự. (Lưu ý: kết quả trả về của hàm Text trong Excel luôn là chuỗi văn bản, ngay cả khi công dụng 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ý hiếm số vào ô thành phần nhiều số smartphone có vẻ là 1 nhiệm vụ khó khăn nhằn, tuy nhiên, hãy hãy nhờ rằng hàm Text trong Excel được cho phép sử dụng dấu gạch ngang (-) với dấu ngoặc đối chọi () vào mã định dạng.
Bởi vậy, để màn biểu diễn số trong ô A2 theo số điện thoại cảm ứng 7 chữ số truyền thống lịch sử theo format 123-456, áp dụng công thức:
=TEXT(A2, “###-####”)
Nếu một vài quý giá gốc cất mã khi vực (VD: hoàn toàn có thể là 7 chữ số, tốt 10 chữ số), bao gồm mã định dạng có điều kiện sau đây để hiển thị số bao gồm 10 chữ số theo định dạng (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 siêu nhiề, tưởng như không tồn tại chút trở ngại nào cản trở. Giả dụ trong trường phù hợp hàm Text không thực hiện được, lí bởi vì của phần nhiều trường hợp là do sai mã định dạng. Dưới đó là 2 trường phù hợp điển hình: Ví dụ công thức =TEXT(A2, mm/dd/yy) bị lỗi, phải sửa thành: =TEXT(A2, “mm/dd/yy”) Ví dụ, bí quyết =TEXT(A2, “mm/dd/yy”) vận động tốt với người dùng sử dụng giờ Anh, nhưng hồ hết vùng không giống thì không. Điển hình, sinh hoạt Đức, người tiêu dùng cần sử dụng định dạng sau: “t” núm cho “d” và “j” nỗ lực cho “y”. Vày vậy, trong phần mềm Excel của Đức, công thức trên sẽ thay bằng =TEXT(A2; “mm/tt/jj”). Trên đây là bài giảng về hàm Text vào Excel, chúc các bạn thành công! Ngoài ra để vận dụng Excel vào công việc một cách công dụng thì chúng ta còn cần sử dụng tốt các hàm, các công cầm cố khác của Excel: Một số hàm cơ phiên bản thường gặp gỡ như: Một số công cụ thường được sử dụng như: Rất nhiều kiến thức và kỹ năng phải ko nào? toàn cục những kỹ năng và kiến thức này các bạn đều hoàn toàn có thể học được trong khóa học EX101 – Excel trường đoản cú cơ bạn dạng 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 và kiến thức một cách đầy đủ, bỏ ra tiết. Hơn nữa không còn có số lượng giới hạn về thời gian học tập nên chúng ta cũng có thể thoải mái học bất kể lúc nào, dễ ợt tra cứu vớt lại kiến thức và kỹ năng khi cần. Hiện nay hệ thống đang sẵn có ưu đãi vô cùng lớn cho chính mình khi đăng ký tham gia khóa học. Chi tiết xem tại: Hoc Microsoft Excel là toàn bộ về những con số cùng trong bảng tính của bạn, bạn có tác dụng xử lý những con số phần đông thời gian. Tuy nhiên, bao gồm tình huống khi bạn cần đổi khác số thành văn bạn dạng và chính là nơi hàm TEXT Excel cách vào. Gợi ý này vẫn giúp chúng ta giải thích các đặc tính của hàm - Về thực chất thuần túy, TEXT vào Excel được thực hiện để chuyển đổi một quý giá số thành chuỗi văn bản theo định dạng cố gắng thể. Trong một số trường phù hợp khác, hàm này còn được áp dụng khi bạn muốn kết hợp với các số hoặc ngày tháng với văn bạn dạng hoặc một ký tự duy nhất định. - Về mục tiêu của hàm Text nhằm biến đổi một số thành văn bản trong một định hình số Cú pháp của lệnh text trong excelnhư sau: =TEXT(value, format_text) Trong đó: - Value:Là quý hiếm số cần chuyển thanh lịch văn bản, giá trị này có thể là số, ngày, tháng. - Format_Text: Định dạng mà bạn có nhu cầu áp dụng Các để ý về biện pháp dùnghàm Text: - sử dụng hàm TEXT để biến hóa một số thành văn bản theo định dạng số cụ thể. + Văn bản đặc biệt có ích khi bạn muốn nhúng cổng đầu ra số của bí quyết text hoặc hàm và trình diễn nó theo một định dạng gắng thể bên trong văn bạn dạng khác. Ví dụ: "Doanh số năm kia đã tăng rộng 43.500 đô la", trong số ấy số 43500 đã làm được định dạng bằng ký hiệu tiền tệ và dấu ngăn cách hàng nghìn. + format_text: phải xuất hiện trong vết ngoặc kép.
COUNTIF, COUNTIFS nhằm thống kê, đếm theo một điều kiện, các điều kiện
Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…Các hàm dò tra cứu tham chiếu Index+Match, hàm SUMPRODUCT…
Thiết lập điều kiện nhập dữ liệu với Data Validation
Cách để Name và thực hiện Name vào công thức
Lập report với Pivot Table…
Excel.Online
Text trong Excel và chỉ ra một số cách thông minh nhằm sử dụng các công thức văn phiên bản trong Excel1. Tính năng của hàm Text
2. Công thức tính của hàm Text
2. Các loại mã định dạng hàm Text
Mã định hình hàm Text
3. Ví dụ như của hàm Text
3.1. Ví dụ bằng văn bản
- Để tham gia một ngày cùng với văn bản, chúng ta có thể sử dụng phép nối tính năng Text để điều hành và kiểm soát định dạng ngày.Ví dụ: gồm bảng sau, cách làm tính vào ô E4 là: = “ Ngày là” và TEXT (B4,”dddd, mmmm yyyy”)...
- vậy thể: công thức chung: = “văn bản” & văn bản (ngày, định dạng)
- Trong ví dụ như hiển thị, phương pháp trong E4 là: = “ Ngày là” & TEXT (B4, “dddd,mmmm yyyy”)
3.2. Ví dụ format số
Áp dụng hàm Text, tại ô B4 nhập công thức biến hóa sang dạng số ta được tác dụng như hình mặt dưới.
3.3. Ví dụ định dạng phần trăm
- từ bỏ bảng dữ liệu trên, hãy tính phần trăm, tại ô C5 gõ công thức: =TEXT(B5, “0.00”)
4. Một số lỗi thường gặp khi sử dụng hàm Text
4.1. Lỗi #NAME
- Lỗi #NAME mở ra trong trường hợp người dùng không cần sử dụng dấu ngoặc kép ("") làm việc mã định dạng.
- Để khắc phục lỗi này, bạn chỉ cần thêm vệt ngoặc kép vào mã định dạng.
Ví dụ: Công thức=TEXT(D2, mm/dd/yy) là cách làm sai vẫn được thay thế sửa chữa bằng=TEXT(D2, "mm/dd/yy")
Lỗi thường gặp khi sử dụng hàm Text - Hình 1
4.2. Hàm không nên cú pháp
Hàm không đúng cú pháp bắt đầu từ hai nguyên nhân:
- tại sao 1: khi bạn copy công thức xuất phát điểm từ 1 dữ liệu khác về,dấu ngoặc kép (") bị sai định dạng, khiến Excel ko thể tiến hành lệnh. Hàm đang báo Lỗi công thức.
Lỗi thường gặp khi sử dụng hàm Text - Hình 2
- Để hạn chế và khắc phục lỗi này, fan dùng chỉ việc xóa lốt ngoặc kép cũ tất cả trong phương pháp và tiến hành nhập tay lại là được.
Lỗi thường chạm chán khi dùng hàm Text - Hình 3
- nguyên nhân 2: vị sự không giống nhau trong việc thiết lập phần mượt Excel giữa những nước. Để khắc phục và hạn chế lỗi này, bạn chỉ việc đổi sang áp dụng phiên bản Excel tương xứng với ngữ điệu mình đang áp dụng là được.
Xem thêm: # những câu nói hay về cuộc sống hôn nhân để luôn hạnh phúc, những câu nói hay nhất về tình nghĩa vợ chồng từ
Trên đây là cách áp dụng hàm Text, các bạn hãy xem kỹ giải pháp làm và thực hành tiếp tục để được công dụng tốt độc nhất nhé.Để biết được thêm nhiều kỹ năng và kiến thức hữu ích để vận dụng hơn vào trong quá trình mời độc giả tham khảo nhiều hơn thế nữa các khoá học tập Excel bên trên eivonline.edu.vn.