Top 11 # Xem Nhiều Nhất Xóa Text Trong Excel Mới Nhất 4/2023 # Top Like

Trong bài viết này, Blog Học Excel Online sẽ giải thích các đặc trưng của hàm Text trong Excel và chỉ ra những cách tối ưu để sử dụng công thứ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 sử dụng để chuyển đổi giá trị số sang chuỗi văn bản ở một định dạng cụ thể.

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

Cụ thể:

Giá trị: giá trị số cần chuyển sang văn bản. Có thể là số, ngày tháng, là tham chiếu của một ô chứa giá trị số hay 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 cần sử dụng, cung cấp thể thức của mã định dạng được đặt trong dấu 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 Excel 2016, Excel 2013, 2010, 2007, 2003 và thấp hơn.

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

Hiển thị số theo cách dễ đọc hơn hoặc theo một định dạng dễ dàng hơn cho người sử dụng.

Hiển thị ngày tháng theo một định dạng nhất định

Kết hợp các số hoặc ngày tháng với văn bản hoặc các kí tự nhất định.

Ví dụ, để kéo ngày từ ô A2 sang ô khác theo định dạng truyền thống như “Jan 1, 2016”, sử dụng công thức sau:

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

Kết quả trả về:

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

0

Chữ số thể hiện những số 0 vô nghĩa

#.00 tính đế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 ngăn không biểu diễn thêm số 0

#.## – tính đến số thập phân thứ 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 trống cho số 0 vô nghĩa nhưng không biểu diễn chúng. Thường được dùng để sắp xếp các số trong cột tại một điểm thập phân

#.?? Biểu 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

,

Ngăn cách 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

Khi sử dụng hàm TEXT trong Excel với ngày và giờ, có thể sử dụng mã định dạng sau:

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 ở đầu (từ 1 đến 31)

dd – 2 số chứa số 0 ở đầu (01 đến 31)

ddd – 3 kí tự viết tắt (Mon – Sun (thứ 2 đến 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 ở đầu (1 đến 12)

mm – 2 số chứa số 0 ở đầu (01 – 12)

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

mmmm – chữ đầy đủ 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 hai chữ số không chứa số 0 ở đầu (1-60)

hh -2 chữ số chứa số 0 ở đầu (01-60)

m (minute)

Phút

m – một hoặc hai chữ số không chứa số 0 ở đầu (1-60)

mm -2 chữ số chứa số 0 ở đầu (01-60)

S (second)

giây

s – một hoặc hai chữ số không chứa số 0 ở đầu (1-60)

ss -2 chữ số chứa số 0 ở đầu (01-60)

Trang tính dưới thể hiện một số công thức hàm Text cho ngày:

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

Có thể thấy, ứng dụng của hàm Text trong Excel còn khá hạn chế (bởi giá trị hiển thị có thể dễ dàng thay đổi bằng cách sử dụng hộp Định dạng ô có sẵn thông qua chương trình lập trình ô 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 tóm tắt hoặc báo cáo, người dùng thường không chỉ muốn tính tổng, phần trăm và những phần tương tự, mà còn giải thích cho người dùng ý nghĩa của chúng. Để thực hiện, sử dụng hàm Concatenate để nhập văn bản và số, và sử dụng hàm Text để thể hiện số (hay ngày) theo ý muốn 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ử, cần tính tổng số lượng dựa trên giá của đối tượng trong ô A2, số lượng đối tượ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ính xác ý nghĩa của kết 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 bạn là”. Cùng đó, người dùng muốn hiển thị biểu tượng tiền tệ, dấu phân cách hàng nghìn và hai chữ số thập phân.

Để hoàn thành, bổ sung 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ố thứ 2, và nối hàm Text 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 quả trả về tương tự bảng sau:

Khi quay lại ngày hiện tại bằng cách sử dụng hàm Today() hay Now, có thể người dùng muốn biểu diễn ngày bằng cách kết hợp với một vài câu, hay đoạn để tránh gây nghi ngờ 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 sẽ trả về kết quả rất lạ, như “Today is 42198”.

Đó là do hệ thông lập trình của Excel, ngày tháng được lưu bằng các con số. Bởi vậy, các con số này xuất hiện trong chuỗi văn bản nối. Để khắc phục tình trạng nay, sử dụng hàm Text để biểu diễn ngày tháng theo ý muốn của người dùng.

Ví dụ, gắn hàm Today trong công thức hàmText có mã định dạng “dddd d mmm,yyyy”, kết quả trả về sẽ là: “Today is Monday 12 Dec, 2016”

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

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

Microsoft Excel tự động xóa những số 0 đứng đầu mỗi con số trong một ô, điều này không có ảnh hưởng gì trong hầu hết các trường hợp. Tuy 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 trong Excel có thể dễ dàng thêm những số 0 đứng trước những con số trong một cột, ngay cả khi những con số đó không có giá trị tương đương. Đơn giản bằng cách dùng mã định dạng chưa những số 0 vào đầu như “0000000”, trong đó, những số 0 phải tương ứng với đơn vị số được hiển thị.

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

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

Bởi vậy, để biểu diễn số trong ô A2 theo số điện thoại 7 chữ số truyền thống theo định dạng 123-456, sử dụng công thức:

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

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

=TEXT(A2,”[<=9999999]###-####;(###) ###-####”)

Công thức thực hiện như sau:

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

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

Hàm Text trong Excel có ngôn ngữ đặc trưng, yêu cầu có mã định dạng ngày và giờ cụ thể.

Ví dụ, công thức =TEXT(A2, “mm/dd/yy”) hoạt động tốt với người dùng sử dụng tiếng Anh, nhưng những vùng khác thì không. Điển hình, ở Đức, người dùng cần sử dụng định dạng sau: “t” thay cho “d” và “j” thay cho “y”. Bởi 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”).

Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel:

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

SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện

COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều đ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ò tìm tham chiếu Index+Match, hàm SUMPRODUCT…

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

Định dạng theo điều kiện với Conditional formatting

Thiết lập điều kiện nhập dữ liệu với Data Validation

Cách đặt Name và sử dụng Name trong công thức

Lập báo cáo với Pivot Table…

Khi soạn thảo văn bản word, bạn cần chèn nội dung vào một vị trí mình muốn, ví dụ như tên giám đốc trong các văn bản hợp đồng, thì Text Box là lựa chọn phù hợp nhất. Tuy nhiên, khi dùng công cụ Text Box bạn sẽ nhìn thấy đường viền quanh ô mình vẽ. Bài viết sẽ hướng dẫn bạn cách xóa đường viền khi vẽ Text Box.

Lợi ích khi chọn Text Box trong word là bạn dễ dàng di chuyển khung nội dung trong file word, dễ dàng chèn vào bất cứ vị trí nào trong văn bản.

Cách vẽ Text Box trong word

Để chọn mục vẽ Text Box, tại mục Insert trên thanh công cụ word, bạn chọn mục Text Box. Tại đây bạn có thể chọn nhiều định dạng có sẵn khác nhau cho Text Box như: Simple Text Box, Austin Quote, Anstin Side Bar,…Hoặc bạn cũng có thể chọn Draw Text Box để vẽ kích thước theo ý thích. Sau khi vẽ, bạn sẽ thấy có đường viền bao quanh.

Cách tô nền cho Text Box đã vẽ

Để tô màu cho Text Box trong word, bạn vào mục Format, chọn Shape Fill. Sau đó chọn màu sắc mà mình muốn tô nền cho Text Box.

Ngoài ra tại mục Shape Effects, bạn có thể chọn các định dạng đổ bóng, 3D, đóng khung nổi,…cho Text Box.

Cách xóa đường viền Text Box trong word

Sau khi vẽ Text Box, để làm mất đường viền bao quanh khung vẽ, bạn nhấp chọn vào Text Box cần chỉnh sửa. Vào mục Format, chọn Shape Outline, chọn No Outline. Như vậy, đường viền của Text Box đã được xóa.

Ngoài ra tại mục Shape Outline, bạn có thể tùy chọn màu cho đường viền nếu muốn. Chỉnh sửa kiểu đường viền là nét đậm, nét nhạt, nét đứt,…tại mục Weight.

Microsoft Excel là tất cả về các con số và trong bảng tính của bạn, bạn có khả năng xử lý các con số hầu hết thời gian. Tuy nhiên, có những tình huống khi bạn cần chuyển đổi số thành văn bản và đó là nơi hàm TEXT bước vào. Hướng dẫn này sẽ giúp các bạn giải thích các đặc tính của hàm Excel Text và chỉ ra một số cách thông minh để sử dụng các công thức văn bản trong Excel.

1. Chức năng của hàm Text

Về bản chất thuần túy, TEXT trong Excel được sử dụng để chuyển đổi một giá trị số thành chuỗi văn bản theo định dạng cụ thể. Về mục đích của hàm Text nhằm chuyển đổi một số thành văn bản trong một định dạng số

2. Công thức tính của hàm Text

Cú pháp của hàm Text như sau: =TEXT(giá trị, định dạng)

Trong đó:Giá trị: là các giá trị số cần chuyển đổiĐịnh dạng: là các định dạng cần sử dụng

Tổng quát: giá trị: Số cần chuyển đổi format_text: Định dạng số để sử dụng Các lưu ý khí sử dụng hàm Text: Sử dụng hàm TEXT để chuyển đổi một số thành văn bản theo định dạng số cụ thể. + Văn bản đặc biệt hữu ích khi bạn muốn nhúng đầu ra số của công thức hoặc hàm và trình bày nó theo một định dạng cụ thể bên trong văn bản khác. Ví dụ: “Doanh số năm ngoái đã tăng hơn 43.500 đô la”, trong đó số 43500 đã được định dạng bằng ký hiệu tiền tệ và dấu phân cách hàng nghìn. + format_text: phải xuất hiện trong dấu ngoặc kép

3. Ví dụ của hàm Text

+ Ví dụ bằng văn bản:

Để tham gia một ngày với văn bản, bạn có thể sử dụng phép nối chức năng Text để kiểm soát định dạng ngày. Ví dụ: Có bảng sau, công thức tính trong ô E4 là: = ” Ngày là” & TEXT (B4,”dddd, mmmm yyyy”)…

Ví dụ về cách sử dụng của hàm Text. Hình 1

Cụ thể: Công thức chung: = “văn bản” & văn bản (ngày, định dạng) Trong ví dụ hiển thị, công thức trong E4 là: = ” Ngày là” & TEXT (B4, “dddd,mmmm yyyy”)

Ví dụ 2: Định dạng số:

Áp dụng hàm Text, tại ô B4 nhập công thức chuyển đổi sang dạng số ta được kết quả như hình bên dưới.

Ví dụ về cách sử dụng của hàm Text. Hình 2

Ví dụ 3: Định dạng phần trăm.

Từ 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”)

Ví dụ về cách sử dụng của hàm Text. Hình 3

You’ve probably come across this situation before.

You have a list of names and it’s all lower case letter. You need to fix them so they are all properly capitalized.

With hundreds of names in your list, it’s going to be a pain to go through and edit first and last names.

Thankfully, there are some easy ways to change the case of any text data in Excel. We can change text to lower case, upper case or proper case where each word is capitalized.

In this post, we’re going to look at using Excel functions, flash fill, power query, DAX and power pivot to change the case of our text data.

Video Tutorial

Using Excel Formulas To Change Text Case

The first option we’re going to look at is regular Excel functions. These are the functions we can use in any worksheet in Excel.

There’s a whole category of Excel functions to deal with text, and these three will help us to change the text case.

LOWER Excel Worksheet Function

UPPER Excel Worksheet Function

PROPER Excel Worksheet Function

The PROPER function takes one argument which is the bit of Text we want to change into proper case. The function will evaluate to text that is all proper case where each word starts with a capital letter and is followed by lower case letters.

Copy And Paste Formulas As Values

After using the Excel formulas to change the case of our text, we may want to convert these to values.

This can be done by copying the range of formulas and pasting them as values with the paste special command.

Press Ctrl + C to copy the range of cells ➜ press Ctrl + Alt + V to paste special ➜ choose Values from the paste options.

Using Flash Fill To Change Text Case

Flash fill is a tool in Excel that helps with simple data transformations. We only need to provide a couple examples of the results we want, and flash fill will fill in the rest.

We can also use the keyboard shortcut Ctrl + E for flash fill.

Flash fill will work for many types of simple data transformations including changing text between lower case, upper case and proper case.

Using Power Query To Change Text Case

Power query is all about data transformation, so it’s sure there is a way to change the case of text in this tool.

With power query we can transform the case into lower, upper and proper case.

Text.Lower Power Query Function

This will create a new column with all text converted to lower case letters using the Text.Lower power query function.

Text.Upper Power Query Function

This will create a new column with all text converted to upper case letters using the Text.Upper power query function.

Text.Proper Power Query Function

This will create a new column with all text converted to proper case lettering, where each word is capitalized, using the Text.Proper power query function.

Using DAX Formulas To Change Text Case

When we think of pivot tables, we generally think of summarizing numeric data. But pivot tables can also summarize text data when we use the data model and DAX formulas. There are even DAX formula to change text case before we summarize it!

LOWER DAX Function

We can enter the above formula into the Measure editor. Just like the Excel worksheet functions, there is a DAX function to convert text to lower case.

However, in order for the expression to be a valid measure, it will need to be wrapped in a text aggregating function like CONCATENATEX. This is because measures need to evaluate to a single value and the LOWER DAX function does not do this on it’s own. The CONCATENATEX function will aggregate the results of the LOWER function into a single value.

Similarily, we can enter the above formula into the Measure editor to create our upper case DAX formula. Just like the Excel worksheet functions, there is a DAX function to convert text to upper case.

Missing PROPER DAX Function

We might try and create a similar DAX formula to create proper case text. But it turns out there is no function in DAX equivalent to the PROPER worksheet function.

Using Power Pivot Row Level Formulas To Change Text Case

This method will also use pivot tables and the Data Model, but instead of DAX formulas we can create row level calculations using the Power Pivot add-in.

Power pivot formulas can be used to add new calculated columns in our data. Calculations in these columns happen for each row of data similar to our regular Excel worksheet functions.

Not every version of Excel has power pivot available and you will need to enable the add-in before you can use it. To enable the power pivot add-in, go to the File tab ➜ Options ➜ go to the Add-ins tab ➜ Manage COM Add-ins ➜ press Go ➜ check the box for Microsoft Power Pivot for Excel.

This is the same data model as creating a pivot table and using the Add this data to the Data Model checkbox option. So if our data is already in the data model we can use the Manage data model option to create our power pivot calculations.

LOWER Power Pivot Function

Press Enter to accept the new formula.

The formula will appear in each cell of the new column regardless of which cell was selected. This is because each row must use the same calculation within a calculated column.

UPPER Power Pivot Function

=UPPER(ChangeCase[Mixed Case])

We can do the same thing to create a calculated column that converts the text to upper case by adding a new calculated column with the above formula.

Missing PROPER Power Pivot Function

Unfortunately, there is no power pivot function to convert text to proper case. So just like DAX, we won’t be able to do this in a similar fashion to the lower case and upper case power pivot methods.

Conclusions

There are many ways to change the case of any text data between lower, upper and proper case.

Excel Formulas are quick, easy and will dynamically update if the inputs ever change.

Flash fill is great for one-off transformations where you need to quickly fix some text and don’t need to update or change the data after.

Power query is perfect for fixing data that will be imported regularly into Excel from an outside source.

DAX and power pivot are can be used for fixing text to display within a pivot table.

Each option has different strengths and weaknesses so it’s best to become familiar will all methods so you can choose the one that will best suit your needs.