Sử dụng Error Checking để biến đổi định dạng:
1. Chọn ngẫu nhiên ô tính nào trong bảng với nhấn những tổ hợp phím dưới đây để lựa chọn 1 dãy.
Bạn đang xem: Hàm định dạng số trong excel
Ctrl + A – chọn cục bộ dãy lập tức kề.
Ctrl + A Twice – chọn toàn bộ trang tính.
Ctrl + phím cách – highlight toàn bộ cột.
Shift + Mũi tên trái/phải – chọn nhiều cột.
Shift + Phím cách – chọn cục bộ hàng.
Shift + Mủi tên lên/xuống – chọn các hàng.
2. Nhấp lựa chọn nút lỗi dưới dạng vết chấm than , nằm ko kể ô tính hoặc dãy được chọn.
3. Bạn sẽ thấy một danh sách hiện ra, chọn Convert to lớn Number.
Chú ý. Nếu bạn không muốn dấu cảnh cáo này xuất hiện ở góc trái phía bên trên ô tính hay là không muốn gửi định dạng text sang number, chọn Ignore Error.
Tất cả số lượng được định dạng text sẽ chuyển về dạng number và nội dung sẽ tiến hành căn lề phải. Ngay sau khi chúng vươn lên là những chữ số thông thường, bạn có thể áp dụng ngẫu nhiên định dạng nào đến kiểu số.
Định dạng number mang đến số bao gồm định dạng text
Trước khi lựa chọn định dạng sô, chúng ta hãy chắc chắn rằng rằng giá trị một trong những ô cần chuyển đổi không đựng dấu bí quyết ở vùng trước hoặc phía sau. Chúng ta cũng có thể dùng hàm trim để thải trừ dấu cách ở vùng trước hoặc phía sau giá trị trong ô.
1. Lựa chọn số gồm định dạng text trong bảng.
2. Mở thẻ Home, chọn định dạng bạn muốn từ danh sách Number Format ở nhóm Number.
3. Mở rộng nhóm gạn lọc định dạng số, nhấn vào vào Dialog Box Launcher kế bên Number.
4. Cửa sổ Format Cells hiện ra. Lựa chọn danh mục cần thiết trong danh sách dưới thẻ Number để thấy toàn bộ các tùy chọn.
Sử dụng khả năng Paste Special để chuyển đổi định dạng text thanh lịch number vào Excel:
1. Lựa chọn vùng tài liệu chứa ô tính bạn có nhu cầu chuyển đổi định hình (dữ liệu vào ô đang ở định dạng General)
2. Nhấn Ctrl + C.
3. Nhấn Ctrl + alt + V để hiển thị vỏ hộp thoại Paste Special.
4. Đánh vết chọn Add trong phần Operation, tiếp nối nhấn OK.
Bạn sẽ thấy các ô tính được căn lề phải, có nghĩa là các quý hiếm đã được đưa sang định dạng number.
Sử dụng hàm VALUE để chuyển đổi định dạng text thành number:
Hàm VALUE chuyển thay đổi chuỗi định dạng text thành number. Cách làm hàm: =VALUE(text), trong những số đó text là chuỗi giá bán trị nên được thiết lập định dạng number.
Chú ý. Nếu chuỗi quý giá trong bí quyết không được format là hằng số, ngày hoặc giờ, kết quả nhận được đang phạm lỗi #VALUE!
1. Chế tạo ra một cột phụ và nhấp vào ô tính thứ nhất của cột.
2. Gõ vào thanh công thức hàm =VALUE(range address). Trong lấy ví dụ như sau đây, range address là A1:A10
3. Xào luộc công thức phủ kín đáo cột phụ. Ta thấy cột này sẽ tự động hóa được căn lề yêu cầu và chỉ số lỗi màu xanh lá cây ở góc trên phía bên trái ô tính biến chuyển mất.
Trong bài viết này, Học Excel Online sẽ lý giải các đặc trưng của hàm Text vào Excel còn chỉ ra các cách tối ưu để sử dụng công thức hàm Text trong Excel.
HÀM TEXT vào EXCEL – CÔNG THỨC VÀ CÁCH SỬ DỤNG CƠ BẢN
Hàm Text được sử dụng để đổi khác giá trị số sang chuỗi văn bạn dạng ở một định dạng núm thể.Công thức: TEXT(giá trị, định dạng_văn bản)
Cụ thể:
giá trị: quý hiếm số phải chuyển sang trọng văn bản. Hoàn toàn có thể là số, ngày tháng, là tham chiếu của một ô chứa giá trị số hay như là 1 hàm khác trả về công dụng là số hay ngày. Định dạng văn bản: định dạng đề nghị sử dụng, hỗ trợ 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ả những 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 các trường hòa hợp sau đây:
Hiển thị số theo phong cách dễ gọi hơn hoặc theo một định dạng dễ dàng 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 định phối hợp các số hoặc ngày tháng với văn bạn dạng hoặc các kí tự độc nhất định.Ví dụ, để kéo ngày từ bỏ ô A2 quý phái ô khác 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 tiền trọng: hàm Text trong Excel được dùng để làm chuyển giá trị số thanh lịch chuỗi văn bản. Bởi vì vậy, hiệu quả hàm Text gửi ra cần thiết sử dụng trong số 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ông thức khác.
MÃ ĐỊNH DẠNG HÀM TEXT vào EXCEL
Như sẽ thấy, công thức hàm Text rất đơn 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 đồng ý hầu hết những định dạng mã được sử dụng trong định hình Excel. Bảng dưới đây liệt kê hồ hết mã định hình cơ bản nhất.
| Mã | Mô tả | Định dạng mã mẫu |
| 0 | Chữ số trình bày những số 0 vô nghĩa | #.00 tính đến số thập phân trang bị 2.Nếu nhập 2.5. Vào ô tham chiếu, số hiển thị là 2.50 |
| # | Dấu ngăn không màn biểu diễn thêm số 0 | #.## – tính mang lại số thập phân sản phẩm 2Nếu nhập 2.5, số hiển thị là 2.5Nếu nhập 2.5555, số hiển thị là 2.56 |
| ? | Dấu để không gian cho số 0 vô nghĩa nhưng không màn biểu diễn chúng. Thường được dùng để sắp xếp những số trong cột tại một điểm thập phân | #.?? màn trình diễn tối nhiều 2 phần thập phân cùng 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 hàng nghìn và 2 số thập phân |
Cùng đó, có thể sử dụng thuộc với bất kể kí từ nào tiếp sau đây trong mã định dạng, tác dụng thể hiện nay được vẫn không thay đổi 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ỏ rộng hoặc to hơn | |
| = | Dấu bằng |
| / | Dấu gạch chéo |
Bảng tiếp sau đây chỉ cách sử dụng công thức hàm Text trong Excel để ứng dụng những định dạng không giống nhau ra quý giá 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 áp dụng hàm TEXT trong Excel cùng với ngày cùng giờ, hoàn toàn có thể sử dụng mã format 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 cất số 0 sống đầu (từ 1 mang lại 31)dd – 2 số đựng số 0 sinh hoạt đầu (01 đến 31)ddd – 3 kí tự viết tắt (Mon – Sun (thứ 2 cho 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 sinh sống đầu (1 mang lại 12)mm – 2 số chứa số 0 ở đầ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 chứa số 0 sinh sống đầu (1-60)hh -2 chữ số đựng số 0 sinh sống đầu (01-60) |
| m (minute) | Phút | m – một hoặc nhị chữ số không đựng số 0 sinh hoạt đầu (1-60)mm -2 chữ số chứa số 0 sinh sống đầu (01-60) |
| S (second) | giây | s – một hoặc nhị chữ số không chứa số 0 ở đầu (1-60)ss -2 chữ số cất số 0 nghỉ ngơi đầu (01-60) |
Trang tính bên dưới thể hiện một số công thức hàm Text mang đến ngày:
CÁCH SỬ DỤNG HÀM TEXT trong EXCEL – CÔNG THỨC MẪU
Có thể thấy, áp dụng của hàm Text trong Excel còn tương đối hạn chế (bởi giá trị hiển thị hoàn toàn có thể dễ dàng nuốm đổi bằng cách sử dụng vỏ hộp Định dạng ô có sẵn trải qua chương trình lập trình sẵn ô 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 dùng thường không chỉ là muốn tính tổng vào Excel, xác suất và hầu hết phần tương tự, hơn nữa giải thích cho người dùng chân thành và ý nghĩa của chúng. Để thực hiện, áp dụng hàm Concatenate để nhập văn phiên bản và số, và áp dụng hàm Text để diễn đạt số (hay ngày) theo nguyện vọng của fan dùng.
Ví dụ 1. Định dạng số bên trong một chuỗi văn bản
Giả sử, đề xuất tính tổng số lượng dựa trên giá bán của đối tượng trong ô A2, số lượng đối tượng người sử dụng trong ô B2, và giảm 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 chủ yếu xác chân thành và ý nghĩa của tác dụng 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 chúng ta là”. Cùng đó, người dùng muốn hiển thị hình tượng tiền tệ, lốt phân cách hàng trăm ngàn và hai chữ số thập phân.
Để trả thành, bổ sung công thức tính vào đối số trước tiên của hàm Text, bao gồm mã định dạng tương ứng trong đối số đồ vật 2, với nối hàm Text cùng với chuỗi bằng cách 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ề giống như bảng sau:
Ví dụ 2: kết hợp văn bạn dạng và tháng ngày theo định dạng ý muốn muốn
Khi trở về ngày hiện tại tại bằng phương pháp sử dụng hàm Today() tuyệt Now, có thể người dùng muốn biểu diễn ngày bằng phương pháp kết phù hợp với một vài câu, xuất xắc đoạn nhằm tránh gây nghi ngại hay nhầm lẫn.
Tuy nhiên, nếu người dùng muốn nối văn bản với ngày tháng theo cách thông thường:
=CONCATENATE(“Today is “, TODAY())
Excel đang trả về kết quả rất lạ, như “Today is 42198”.
Đó là do hệ thông lập trình của Excel, tháng ngày được lưu lại bằng những con số. Vì chưng vậy, các con số này mở ra trong chuỗi văn phiên bản nối. Để xung khắc phục tình trạng nay, sử dụng hàm Text để biểu diễn ngày tháng theo nguyện vọng của tín đồ dùng.
Ví dụ, gắn thêm hàm Today trong công thức hàm
Text gồm mã format “dddd d mmm,yyyy”, kết quả trả về vẫn là: “Today is Monday 12 Dec, 2016”
Công thức bao quát 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 hóa xóa phần lớn số 0 cầm đầu mỗi con số trong một ô, vấn đề đó không có ảnh hưởng gì trong phần đông các ngôi trường hợp. Tuy nhiên, nếu người dùng muốn không thay đổi những số 0 đứng trước này thì sao?
Hàm Text trong Excel rất có thể dễ dàng thêm phần lớn số 0 đứng trước những con số trong một cột, trong cả khi những số lượng đó không có giá trị tương đương. Đơn giản bằng cách dùng mã format chưa đa số số 0 vào đầu như “0000000”, vào đó, đầy đủ số 0 phải khớp ứng với đơn vị chức năng số được hiển thị.
Ví dụ, để màn biểu diễn số tất cả 7 chữ số với số 0 đứng đầu, dùng phương pháp sau: (A2 là ô cất giá trị gốc)
=TEXT(A2,”0000000″)
Trong bảng bên dưới đây, cách làm hàm Text thêm đang thêm số 0 dẫn đầu theo yêu cầu để tạo thành chuỗi 7 kí tự. (Lưu ý: công dụng trả về của hàm Text vào Excel luôn là chuỗi văn bản, trong 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 giá trị số vào ô thành phần nhiều số smartphone có vẻ là 1 trong những nhiệm vụ cạnh tranh nhằn, tuy nhiên, hãy hãy nhớ là hàm Text vào Excel chất nhận được sử dụng vết gạch ngang (-) và dấu ngoặc đối kháng () vào mã định dạng.
Bởi vậy, để trình diễn số trong ô A2 theo số điện thoại thông minh 7 chữ số truyền thống lâu đời 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 cất mã khi vực (VD: có thể là 7 chữ số, tuyệt 10 chữ số), bao gồm mã định dạng có điều kiện dưới đây để hiển thị số có 10 chữ số theo format (123) 246-789:
=TEXT(A2,” So với những hàm khác trong Excel, hàm Text đơn giản dễ dàng 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ở. Ví như trong trường hòa hợp hàm Text không triển khai được, lí vị của phần đông trường hợp là do sai mã định dạng. Dưới đó là 2 trường thích hợp điển hình: Ví dụ công thức =TEXT(A2, mm/dd/yy) bị lỗi, cần sửa thành: =TEXT(A2, “mm/dd/yy”) Ví dụ, phương pháp =TEXT(A2, “mm/dd/yy”) hoạt động tốt với người dùng sử dụng giờ Anh, nhưng phần lớn vùng không giống thì không. Điển hình, ngơi nghỉ Đức, người dùng cần thực hiện định dạng sau: “t” núm cho “d” với “j” rứa cho “y”. Vị vậy, trong phần mềm Excel của Đức, công thức bên 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!
Xem thêm: Top 20 Quán Cà Phê Đẹp Sài Gòn Nhất Định Phải Ghé, Top 20 Quán Cafe Đẹp Nhất Sài Gòn
Excel.Online