Cách sử dụng hàm TEXT trong Excel để chuyển đổi số, ngày thành văn bản

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, …) thành văn bản, ở format 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 ở format do người dùng chỉ định. Ví dụ: nếu bạn muốn chuyển đổi một ngày ở format ’15 / 03/2020′ thành format 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(value,format_text)

Hàm TEXT yêu cầu hai đối số / tham số:

  • value– 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ã format 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ã format 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ã format chính xác sẽ cung cấp cho bạn số đầu ra ở format bạn muốn. Bảng sau đây chứa các format phổ biến nhất và thường được sử dụng.

ĐỊNH DẠNG MÃSỰ MIÊU TẢTHÍ DỤ
0Số 0 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ã format ở trên, bạn cũng có thể thêm bất kỳ ký hiệu nào sau đây vào mã format của công thức và chúng sẽ được hiển thị chính xác như đã nhập.

SYMBO LSỰ MIÊU TẢ
+ và -Dấu cộng và dấu trừ
( )Dấu ngoặc đơn trái và phải
:Dấu hai chấm
^Dấu mũ
'Dấu nháy đơn
{ }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ã
 Ký tự khoảng trắng

Mã format 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ã format 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)
ddChỉ đị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)
dddChỉ đị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)
ddddChỉ định tên đầy đủ của Ngày trong tuần. (ví dụ: Thứ Hai, Thứ Tư)
mChỉ đị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)
mmChỉ định Tháng ở dạng biểu diễn hai chữ số với số 0 ở đầu. (ví dụ: 01, 12)
mmmChỉ định Tháng ở dạng viết tắt ba chữ cái (ví dụ: Jan, Nov)
mmmmChỉ định tên đầy đủ của Tháng. (ví dụ: tháng 1, tháng 11)
yyChỉ định Năm ở dạng số có hai chữ số (ví dụ: 08 nghĩa là 2008, 19 nghĩa là 2019)
yyyyChỉ định Năm ở dạng số có bốn chữ số (ví dụ: 2008, 2019)
hChỉ đị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) 
hhChỉ định giờ ở dạng biểu diễn hai chữ số với số 0 ở đầu (06 đến 12)
mChỉ định Phút bằng một hoặc hai chữ số không có số 0 ở đầu (ví dụ: 5, 45) 
mmChỉ định Phút ở một số có một hoặc hai chữ số, số 0 ở đầu (ví dụ: 05, 45) 
sChỉ định Giây bằng một hoặc hai chữ số không có số 0 ở đầu (ví dụ: 5, 45) 
ssChỉ định Giây trong một số có một hoặc hai chữ số, số 0 ở đầu (ví dụ: 05, 45)
AM / PMChỉ đị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ã format 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ã format khác nhau để áp dụng các kiểu format khác nhau cho các giá trị số khác nhau. Bạn có thể sao chép các công thức này trực tiếp vào bảng tính của mình.

GIÁ TRỊCÔNG THỨCGIÁ 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ư được hiển thị 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 dưới đây cho điều đó:

="Your Net Profit is "&TEXT(C6-C7,"$#,###.00")

Đầu tiên, công thức tính lợi nhuận theo công thức (C6-C7) trong hàm TEXT và nó kết hợp giá trị được format 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.

Format 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 các ký hiệu khoa học gây phiền nhiễu vào 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 format ký hiệu khoa học.

Với chức năng TEXT, bạn có thể format format 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ó chiều dài 12 chữ số (điều này có thể thay đổi đối với một số quốc gia). Hai chữ số đầu là mã quốc gia và 10 chữ số còn lại là những con 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ã format 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 (-).

=TEXT(A1,"+##-##########")

Format 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 hàm đều tự động chuyển đổi giá trị ngày tháng sang số sê-ri, nên rất khó để hiển thị chúng ở format 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 ở format 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ó ở format 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")
  • mmm chỉ định 3 chữ cái viết tắt tháng
  • d chỉ định ngày trong tháng bằng một hoặc hai chữ số
  • yyyy chỉ đị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 format khác nhau cho cùng một ngày bằng cách sử dụng công thức Văn bản:

Hàm Concatenate kết hợp chuỗi ngày và văn bản

Giả sử chúng ta 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 tháng 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 tế.

Để nối văn bản và ngày tháng và hiển thị đúng ngày ở format 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 ‘Adam was born on’ vào đầu ra và format ngày ở format khác.

Sau đó, công thức được sao chép vào ô A2: A5 bằng cách sử dụng tính năng Fill Handle.

Trong một ví dụ khác, chúng tôi đang sử dụng hàmTODAY()để lấy ngày hiện tại và nối ngày với một số văn bản có 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,…) thành văn bản ở format bạn muốn.

Xem thêm về:

Chia sẻ suy nghĩ, quan điểm của bạn

Leave a reply

Sao Hải Vương
Logo