Sử dụng hàm TEXT của Excel để chuyển đổi bất kỳ dữ liệu nào (ví dụ: số, ngày, v.v.) thành văn bản, ở định dạng do người dùng chỉ định.
Hàm TEXT được phân loại là Hàm chuỗi / văn bản, hàm này chuyển đổi giá trị số thành chuỗi văn bản ở định dạng do người dùng chỉ định. Ví dụ: nếu bạn muốn chuyển đổi một ngày ở định dạng ’15 / 03/2020 ′ thành định dạng ngày 15 tháng 3 năm 2020, bạn sử dụng hàm TEXT để thực hiện điều đó.
Hướng dẫn này giải thích cách sử dụng hàm TEXT trong Excel với sự trợ giúp của một số công thức và ví dụ.
Cú pháp
Cú pháp chung của hàm TEXT:
= TEXT (giá trị, định dạng_text)Hàm TEXT yêu cầu hai đối số / tham số:
giá trị- Giá trị số mà bạn muốn chuyển đổi thành chuỗi văn bản. Giá trị này có thể là giá trị số, ngày tháng hoặc tham chiếu ô của giá trị số.format_text- Mã định dạng mà bạn muốn áp dụng cho giá trị cụ thể. Nó phải luôn được đặt trong dấu ngoặc kép.
Mã định dạng hàm TEXT
Hàm Text chỉ có hai đối số. Đối số đầu tiên chỉ yêu cầu giá trị bạn muốn chuyển đổi, thật dễ dàng. Nhưng bạn nên chèn mã định dạng chính xác sẽ cung cấp cho bạn số đầu ra ở định dạng bạn muốn. Bảng sau đây chứa các định dạng phổ biến nhất và thường được sử dụng.
| Định dạng mã | Sự miêu tả | Thí dụ |
|---|---|---|
0 | Số không là phần giữ chỗ chữ số chỉ hiển thị các chữ số mà không có bất kỳ vị trí thập phân nào. | # .0 - luôn hiển thị 1 chữ số thập phân. Nếu bạn nhập 5,50 vào ô được tham chiếu, nó sẽ hiển thị là 5,5. |
# | Hiển thị chữ số mà không có thêm số 0 nào. | #. ## - hiển thị đến hai chữ số thập phân. khi bạn nhập 3,777, nó trả về 3,78. |
? | Chỉ hiển thị chữ số không có chữ số thập phân. Nó thường được sử dụng để căn chỉnh các giá trị số trong một cột ở vị trí thập phân. | #.? - nó sẽ hiển thị một vị trí thập phân và sẽ căn chỉnh điểm thập phân. |
. | Dấu thập phân | |
, | Dấu phân cách hàng nghìn. | ###, ### - Nó sẽ hiển thị dấu phân cách hàng nghìn. Nếu bạn nhập 195200, nó trả về 195,200 |
0% | Hiển thị số dưới dạng phần trăm. | Nếu bạn nhập 0,285, nó trả về 28,5 |
Ngoài mã định dạng ở trên, bạn cũng có thể thêm bất kỳ ký hiệu nào sau đây vào mã định dạng của công thức và chúng sẽ được hiển thị chính xác như đã nhập.
| Symbol | Sự miêu tả |
|---|---|
+ và - | Dấu cộng và dấu trừ |
() | Dấu ngoặc đơn trái và phải |
: | Đại tràng |
^ | Con mũ |
' | Apostrophe |
{} | Dấu ngoặc nhọn |
<> | Dấu hiệu nhỏ hơn và lớn hơn |
= | Dấu bằng |
/ | Dấu gạch chéo |
! | Dấu chấm than |
& | Ký hiệu và |
~ | Dấu ngã |
| Nhân vật không gian |
Mã định dạng hàm văn bản cho ngày và giờ
Khi bạn muốn chuyển đổi ngày và giờ bằng hàm TEXT, hãy sử dụng bất kỳ mã định dạng nào dưới đây.
| Định dạng mã | Mô tả và Ví dụ |
|---|---|
d | Chỉ định Ngày trong tháng ở dạng số có một hoặc hai chữ số không có số 0 ở đầu (ví dụ: 2 đến 25) |
dd | Chỉ định Ngày trong tháng dưới dạng biểu diễn hai chữ số với số 0 ở đầu (ví dụ: 02 đến 25) |
ddd | Chỉ định Ngày trong tuần dưới dạng viết tắt ba chữ cái (ví dụ: Thứ Hai đến Chủ Nhật) |
dddd | Chỉ định tên đầy đủ của Ngày trong tuần. (ví dụ: Thứ Hai, Thứ Tư) |
m | Chỉ định Tháng trong năm, ở một số có một hoặc hai chữ số không có số 0 ở đầu (ví dụ: 02 đến 12) |
mm | Chỉ định Tháng ở dạng biểu diễn hai chữ số với số 0 ở đầu. (ví dụ: 01, 12) |
mmm | Chỉ định Tháng ở dạng viết tắt ba chữ cái (ví dụ: tháng 1, tháng 11) |
mmmm | Chỉ định tên đầy đủ của Tháng. (ví dụ: tháng 1, tháng 11) |
yy | Chỉ định Năm ở dạng số có hai chữ số (ví dụ: 08 nghĩa là 2008, 19 nghĩa là 2019) |
yyyy | Chỉ định Năm ở dạng số có bốn chữ số (ví dụ: 2008, 2019) |
h | Chỉ định giờ ở dạng biểu diễn một hoặc hai chữ số không có số 0 ở đầu (ví dụ: 6, 12) |
hh | Chỉ định giờ ở dạng biểu diễn hai chữ số với số 0 ở đầu (06 đến 12) |
m | Chỉ định Phút bằng một hoặc hai chữ số không có số 0 ở đầu (ví dụ: 5, 45) |
mm | Chỉ định Phút ở một số có một hoặc hai chữ số, số 0 ở đầu (ví dụ: 05, 45) |
S | Chỉ định Giây bằng một hoặc hai chữ số không có số 0 ở đầu (ví dụ: 5, 45) |
ss | Chỉ định Giây trong một số có một hoặc hai chữ số, số 0 ở đầu (ví dụ: 05, 45) |
LÀ / BUỔI CHIỀU | Chỉ định rằng thời gian sẽ được hiển thị dưới dạng đồng hồ 12 giờ, tiếp theo là “SA” hoặc “CH” |
Cách sử dụng hàm TEXT trong Excel
Bạn đã học được cú pháp và mã định dạng của hàm TEXT, bây giờ, hãy cùng chúng tôi khám phá cách sử dụng hàm trong Excel với sự trợ giúp của một số ví dụ.
Sử dụng công thức văn bản để hiển thị số nguyên thành số trong ô A1.
Để làm điều này, chúng ta có thể sử dụng công thức văn bản như sau:
= TEXT (A1, "0")
Sử dụng công thức này để hiển thị một chữ số thập phân:
= TEXT (A1, "0.0")
Bảng dưới đây hiển thị các công thức Văn bản với các mã định dạng khác nhau để áp dụng các kiểu định dạng khác nhau cho các giá trị số khác nhau. Bạn có thể sao chép trực tiếp các công thức này vào bảng tính của mình để tự thử.
| Giá trị | Công thức | Giá trị được định dạng |
|---|---|---|
| 4963.34 | = TEXT (A2, "0,000") | 4963.340 |
| 5300.52 | = TEXT (A3, "#, ## 0") | 5,301 |
| 5.12 | = TEXT (A4, "#? /?") | 5 1/8 |
| 0.4963 | = TEXT (A5, "#%") 50% | 50% |
| 9600.60 | = TEXT (A6, "$ #, ## 0.0") | $9,600.6 |
| 20 | = TEXT (A7, "~ #!") ~ 20! | ~20! |
| 5656 | = TEXT (A8, "00000000") | 00005656 |
Các công thức trên được áp dụng trong cột C của bảng tính như thể hiện trong ảnh chụp màn hình bên dưới:
Hàm TEXT với công thức
Bạn cũng có thể sử dụng các công thức và hàm khác cả bên trong và bên ngoài hàm TEXT.
Giả sử bạn có số tiền Gộp và Chi phí, và bạn muốn tính lợi nhuận ròng và hiển thị lợi nhuận trong ô A9 với chuỗi “Lợi nhuận ròng của bạn là”. Bạn có thể sử dụng công thức bên dưới cho điều đó:
= "Lợi nhuận ròng của bạn là" & TEXT (C6-C7, "$ #, ###. 00")Đầu tiên, công thức tính toán lợi nhuận theo công thức (C6-C7) trong hàm TEXT và nó kết hợp giá trị được định dạng với chuỗi “Lợi nhuận ròng của bạn là” bằng cách sử dụng công thức nối (&) và cuối cùng nó hiển thị kết quả trong ô A9.
Định dạng số điện thoại di động bằng hàm TEXT
Thông thường, khi bạn nhập bất kỳ số nào dài hơn 11 chữ số trong bảng tính, chẳng hạn như số điện thoại di động, Excel sẽ tự động chuyển nó sang ký hiệu khoa học. Và bạn có thể sử dụng các ký hiệu khoa học này đúng cách, vì vậy bạn có thể muốn chuyển đổi chúng thành các giá trị số bình thường. Bạn có thể sử dụng chức năng TEXT để chuyển đổi những ký hiệu khoa học khó chịu đó thành số điện thoại di động.
Như được hiển thị trong ảnh chụp màn hình bên dưới, khi bạn nhập số điện thoại di động (dài 12 chữ số bao gồm mã quốc gia của chúng) trong Excel, nó sẽ tự động chuyển đổi các số điện thoại di động này thành định dạng ký hiệu khoa học.
Với chức năng TEXT, bạn có thể định dạng định dạng ký hiệu khoa học này thành các số điện thoại di động có thể đọc được.
Thông thường, một số điện thoại di động có 12 chữ số (điều này có thể khác nhau đối với một số quốc gia). Hai chữ số đầu tiên là mã quốc gia và 10 chữ số còn lại là số điện thoại di động.
Vì vậy, để chuyển đổi các ký hiệu khoa học ở trên thành số điện thoại di động, hãy sử dụng công thức sau:
= TEXT (A1, "+ #############")Chúng tôi đang sử dụng ‘#############’ làm mã định dạng cho ví dụ này để chuyển đổi ký hiệu thành số điện thoại di động:
Bây giờ, hãy làm cho nó dễ đọc hơn bằng cách tách mã quốc gia khỏi số điện thoại di động. Để làm điều đó, hãy đặt dấu gạch ngang (-) sau hai lần băm.
= TEXT (A1, "+ ## - ##########")
Định dạng ngày bằng cách sử dụng hàm TEXT
Theo mặc định, Excel lưu trữ Ngày dưới dạng số sê-ri. Số sê-ri của ngày 1 tháng 1 năm 1900 là 1 và ngày 1 tháng 1 năm 2001 là 36892 vì nó là 36891 ngày kể từ ngày 1 tháng 1 năm 1900.
Vì hầu hết các chức năng tự động chuyển đổi các giá trị ngày tháng thành số sê-ri, nên rất khó để hiển thị chúng ở định dạng có thể đọc được. Nhưng với hàm TEXT trong Excel có thể dễ dàng chuyển đổi chúng thành các giá trị văn bản và hiển thị chúng ở định dạng mong muốn của bạn.
Ví dụ: nếu bạn muốn lấy ngày từ ô A1 (05-03-2015) và hiển thị nó ở định dạng ngày tiêu chuẩn như ‘Ngày 5 tháng 3 năm 2015’ trong ô B1, bạn sử dụng công thức sau:
= TEXT (A1, "mmm d, yyyy")mmmchỉ định 3 chữ cái viết tắt thángdchỉ định ngày trong tháng bằng một hoặc hai chữ sốyyyychỉ định hiển thị các số có bốn chữ số trong năm.
Ảnh chụp màn hình bên dưới cho thấy cách bạn có thể áp dụng các loại định dạng khác nhau cho cùng một ngày bằng công thức Văn bản:
Ngày và văn bản kết hợp
Giả sử chúng tôi muốn kết hợp tên (cột A) và ngày sinh (cột B) và hiển thị nó trong cột C. Bạn sẽ nhận được kết quả như sau:
Nếu chúng ta nối trực tiếp văn bản trong ô A1 và ngày trong ô B1, thì Excel sẽ nối văn bản và số sê-ri cho ngày chứ không phải ngày thực.
Để nối văn bản và ngày tháng và hiển thị đúng Ngày ở định dạng mong muốn, hãy sử dụng chức năng TEXT với chức năng CONCAT.
Công thức:
= CONCAT (A2, "-", TEXT (B2, "dd / m / yy"))Kết quả:
Bây giờ, chúng ta sẽ nối một chuỗi văn bản "được sinh ra từ ngày" vào đầu ra và định dạng ngày ở định dạng khác.
Sau đó, công thức được sao chép vào ô A2: A5 bằng cách sử dụng chốt điền.
Trong một ví dụ khác, chúng tôi đang sử dụng HÔM NAY() chức năng lấy ngày hiện tại và nối ngày với một số văn bản liên quan.
Giờ đây, bạn có thể dễ dàng chuyển đổi bất kỳ giá trị nào (ví dụ: số, ngày, v.v.) thành văn bản ở định dạng bạn muốn.