Replace Trong Excel / Top 18 Xem Nhiều Nhất & Mới Nhất 12/2023 # Top Trend

Learning how to use Find and Replace in Excel 2010, or Excel 2007, can save you a great deal of time if you have to change a lot of values or formulas on a worksheet. Learn how to use the Find and Replace function in Excel with this guide and never waste time making changes to a worksheet again…

What is Find and Replace

The Find and Replace function in Excel does exactly what it says, it finds a value, text string or formula and replaces it with a different value, text string or formula as specified by you the user.

For example you may have a worksheet that contains raw employee data with one of the fields being the team of each employee. If one, or more, of those team names suddenly get renamed by the business then using Find and Replace in Excel can save you the hassle of manually amending all the cells individually, you can just get Excel to do the hard work and change all occurrences of Team X to Team Y.

How to access the Find and Replace function in Excel

This method works the same for Excel 2010 and Excel 2007, even earlier versions should work similar if you are still working on Excel 2003 for example.

There are 2 ways to access the Find and Replace function in Excel, the first is by means of the Excel ribbon on the top of the screen.

To access the Find and Replace function using the Ribbon:

1) Make sure you are on the Home tab

Basic Changes

If like in the previously mentioned example you just want to change a team name within a dataset, i.e. change “Marketing” to “Marketing and Advertising”, you can simply enter the change on the basic view of the Find and Replace Function like so:

Advanced Options Explained

Formats: In the top right of the Find and Replace function window there is now an option button for “Format”.This can be used if you want to only search for a particular format, or if you want to replace using a particular format.Examples of this might be changing certain text to a different colour or to a different font style, like italic or bold.

Within: The drop down box next to “Within” allows you to change whether Excel looks at the active worksheet (as identified by “Sheet”) or you can also change this to “Workbook” if you want Excel to Find and Replace throughout the whole workbook.

Search: This drop down box allows you to change the way Excel searches from “By Rows” to “By Columns”.It’s data dependent on what is best but usually you can leave this set to “By Rows” as Excel will still search the entire worksheet or workbook.

Look in: This is always set to “Formulas” making it kind of redundant, it just means Excel will look in the formula as well as just text strings or numerical values when it completes the Find and Replace.

Match case: If there are times where you need Excel to be case specific, i.e. only change “MARKETING” and not “Marketing” then check this box.

Match Entire Cell Contents: Again this is another very specific option which tells Excel to only Find and Replace cells where the entire cell matches what it is trying to chúng tôi example if you want to only find “Marketing” and not cases where the cell is “Marketing team” then you would check this box.

Access the Find and Replace function from the “Home” tab or use the shortcut command CTRL+H.

Advanced changes, i.e. searching for certain formats or exact matches can be made by setting additional options in the Find and Replace function.

As always I would recommend completing any changes in a copy of your workbook first so if you make any mistakes you can revert back to the original.

That is really all there is to the Find and Replace function in Excel 2010, or Excel chúng tôi is an amazingly powerful tool which all analysts and Excel users should understand as it can enable you to make multiple changes to an Excel file in no time at chúng tôi can use that extra time to grab yourself a nice cup of coffee!

Keep Excelling,

/en/excel2023/working-with-multiple-worksheets/content/

Introduction

When working with a lot of data in Excel, it can be difficult and time consuming to locate specific information. You can easily search your workbook using the Find feature, which also allows you to modify content using the Replace feature.

Optional: Download our practice workbook.

Watch the video below to learn more about using Find & Replace.

To find content:

In our example, we’ll use the Find command to locate a specific department in this list.

The Find and Replace dialog box will appear. Enter the content you want to find. In our example, we’ll type the department’s name.

You can also access the Find command by pressing Ctrl+F on your keyboard.

To replace cell content:

At times, you may discover that you’ve repeatedly made a mistake throughout your workbook (such as misspelling someone’s name) or that you need to exchange a particular word or phrase for another. You can use Excel’s Find and Replace feature to make quick revisions. In our example, we’ll use Find and Replace to correct a list of department names.

The Find and Replace dialog box will appear. Type the text you want to find in the Find what: field.

If the content is found, the cell containing that content will be selected.

Review the text to make sure you want to replace it.

If you want to replace it, select one of the replace options. Choosing Replace will replace individual instances, while Replace All will replace every instance of the text throughout the workbook. In our example, we’ll choose this option to save time.

The selected cell content will be replaced.

Generally, it’s best to avoid using Replace All because it doesn’t give you the option of skipping anything you don’t want to change. You should only use this option if you’re absolutely sure it won’t replace anything you didn’t intend it to.

Challenge!

Open our practice workbook.

Crystal Lewis was married and changed her last name to Taylor. Use Find and Replace to change Crystal’s last name from Lewis to Taylor. Be careful to only change Crystal’s last name!

Find and replace Bio with Biology. Be careful not to change the major Biomedical Engineering!

Use Find and Replace All to replace the Physics major to Physical Science.

When you’re finished, your worksheet should look like this:

/en/excel2023/checking-spelling/content/

The Advanced Find and Replace add-in for Excel is designed to search across multiple worksheets and replace the found entries, select, or export them.

Start Find & Replace

Tip.You can also press the Ctrl+Alt+F hotkey on your keyboard. To change the default hotkey, use the Options icon at the bottom of the add-in window.

Search in Excel workbooks

You will see the add-in window:

Enter the value you are going to search for into this field.

Pick the Search within option:

Select Entire Sheet if you need to scan the whole document to find the value.

Tick Selection Only if your area of interest is just a certain range rather than the entire worksheet.

Decide if the add-in should go through Values, Formulas, Comments, and/or Hyperlinks and check the needed option(s). You can tick all these boxes to perform search in all types of records at the same time.

The Match options section adds certain flexibility to defining the values to look for:

If you want to distinguish between uppercase and lowercase characters, select Match case. For example, to find the name “Robert” and exclude entries like “[email protected]”, enter “Robert” into the field at the top, check the Match case box, and you’ll see all entries that begin with an uppercase letter.

Check the Entire cell box to search for an exact and complete match of the characters specified in the top field. I.e. if you select this option with the value ‘Robert’, you’ll find only those cells that don’t contain any other values.

Tick the boxes near the workbooks and worksheets you want to scan.

Replace, select, or export the found entries

Now you can work with the search results:

If you want to replace the found entries, enter the new value into the Replace field.

If you need to replace only certain found entries, select a single search result or pick multiple cells by holding the Ctrl key and use the Replace button to update only the highlighted records.

You can sort the search results by the Value column and change the width of each column right in the add-in window.

You can export only rows or only columns with the found values by choosing to Export rows with all found entries or Export columns with all found entries.

Feel free to export only certain entries by highlighting them and selecting to Export the highlighted entries in the drop-down list.

Export rows or columns with the highlighted entries by picking one of the last two options. Press the Ctrl key, select the entries in the add-in pane, and choose to export rows or columns that contain them.

Select only rows or only columns by choosing to Select rows with all found entries or Select columns with all found entries.

You can get only certain entries selected by picking them in the tree view and choosing to Select the highlighted entries.

Select rows or columns with the entries of interest by picking one of the last two options. Press the Ctrl key, highlight the entries in the add-in pane, and choose to select rows or columns that contain them.

The Advanced Find and Replace add-in for Excel is designed to search across multiple worksheets and replace the found entries, select, or export them.

Start Find & Replace

Tip.You can also press the Ctrl+Alt+F hotkey on your keyboard. To change the default hotkey, use the Options icon at the bottom of the add-in window.

Search in Excel workbooks

You will see the add-in window:

Enter the value you are going to search for into this field.

Pick the Search within option:

Select Entire Sheet if you need to scan the whole document to find the value.

Tick Selection Only if your area of interest is just a certain range rather than the entire worksheet.

Decide if the add-in should go through Values, Formulas, Comments, and/or Hyperlinks and check the needed option(s). You can tick all these boxes to perform search in all types of records at the same time.

The Match options section adds certain flexibility to defining the values to look for:

If you want to distinguish between uppercase and lowercase characters, select Match case. For example, to find the name “Robert” and exclude entries like “[email protected]”, enter “Robert” into the field at the top, check the Match case box, and you’ll see all entries that begin with an uppercase letter.

Check the Entire cell box to search for an exact and complete match of the characters specified in the top field. I.e. if you select this option with the value ‘Robert’, you’ll find only those cells that don’t contain any other values.

Tick the boxes near the workbooks and worksheets you want to scan.

Replace, select, or export the found entries

Now you can work with the search results:

If you want to replace the found entries, enter the new value into the Replace field.

If you need to replace only certain found entries, select a single search result or pick multiple cells by holding the Ctrl key and use the Replace button to update only the highlighted records.

You can sort the search results by the Value column and change the width of each column right in the add-in window.

You can export only rows or only columns with the found values by choosing to Export rows with all found entries or Export columns with all found entries.

Feel free to export only certain entries by highlighting them and selecting to Export the highlighted entries in the drop-down list.

Export rows or columns with the highlighted entries by picking one of the last two options. Press the Ctrl key, select the entries in the add-in pane, and choose to export rows or columns that contain them.

Select only rows or only columns by choosing to Select rows with all found entries or Select columns with all found entries.

You can get only certain entries selected by picking them in the tree view and choosing to Select the highlighted entries.

Select rows or columns with the entries of interest by picking one of the last two options. Press the Ctrl key, highlight the entries in the add-in pane, and choose to select rows or columns that contain them.

Trong bài viết này, Học Excel Online sẽ giải thích hàm REPLACE và SUBSTITUTE trong Excel với các ví dụ. Cách dùng hàm với chuỗi văn bản, số và ngày, và làm thế nào để lập một công thức với nhiều hàm hoặc SUBSTITUTE.

Hàm REPLACE trong Excel cho phép bạn thay đổi một hoặc nhiều ký tự trong một chuỗi văn bản với một ký tự khác hoặc một chuỗi các ký tự.

Như bạn thấy, hàm REPLACE có 4 đối số, tất cả đều được yêu cầu.

Old_text – văn bản gốc (hoặc tham chiếu đến ô có văn bản gốc) mà bạn muốn thay thế một số ký tự.

Start_num – vị trí của ký tự đầu tiên trong old_text mà bạn muốn thay thế.

Num_chars – số ký tự mà bạn muốn thay thế.

New_text – văn bản thay thế.

Ví dụ: để thay đổi từ “sun” thành “son”, bạn có thể sử dụng công thức sau:

= REPLACE (“sun”, 2, 1, “o”)

Và nếu bạn đặt từ muốn thay trong một ô, ví như ô A2, bạn có thể cung cấp ô tham chiếu tương ứng chứa old_text:

Sử dụng hàm REPLACE với các giá trị số

Hàm REPLACE được thiết kế để làm việc với chuỗi văn bản. Tất nhiên, bạn có thể sử dụng nó để thay thế các ký tự số là một phần của một chuỗi văn bản, ví dụ:

Theo cách tương tự, bạn có thể thay thế một hoặc nhiều chữ số trong một số. Ví dụ:

= REPLACE (A4, 4, 4, “6”)

Và một lần nữa, bạn phải đặt giá trị thay thế trong dấu nháy kép (“6”).

Như bạn đã thấy, hàm REPLACE hoạt động tốt với các con số, ngoại trừ việc nó trả kết quả là chuỗi văn bản 🙂 Trong hệ thống Excel, các ngày được lưu dưới dạng số, bạn có thể thử sử dụng hàm với ngày tháng. Nhưng kết quả sẽ không như mong đợi.

Ví dụ, bạn có một ngày trong A2, là 1-Oct-14, và bạn muốn thay đổi “Oct” thành “Nov”. Vì vậy, bạn viết công thức REPLACE (A2, 4, 3, “Nov”) để nói Excel thay thế 3 ký tự trong ô A2, bắt đầu với kí tự thứ 4 … và có kết quả sau đây:

Để hàm REPLACE làm việc đúng với ngày, đầu tiên bạn có thể chuyển các ngày thành chuỗi văn bản bằng cách sử dụng hàm hoặc bất kỳ phương pháp nào được trình bày trong Cách chuyển đổi ngày sang văn bản. Ngoài ra, bạn có thể đưa hàm TEXT trực tiếp vào old_text của hàm REPLACE:

= DATEVALUE (REPLACE (TEXT (A2, “dd-mmm-yy”), 4, 3, “Nov”))

Các hàm REPLACE lồng nhau để thực hiện nhiều thay thế trong một ô:

Bạn thường phải thay thế nhiều đoạn trong cùng một ô. Bạn có thể sử dụng các chức năng REPLACE lồng nhau cho phép bạn thực hiện nhiều thay thế chỉ bằng một công thức. Trong ngữ cảnh này, “lồng” có nghĩa là đặt một hàm bên trong một hàm khác.

Xem xét ví dụ sau: Giả sử bạn có một danh sách các số điện thoại trong cột A được định dạng là “123456789” và bạn muốn làm cho chúng trông giống như số điện thoại bằng cách thêm các dấu nối. Nói cách khác, mục tiêu của bạn là chuyển “123456789” thành “123-456-789”.

Chèn dấu nối đầu tiên là dễ dàng. Bạn viết một công thức thông thường để thay thế không ký tự nào ở vị trí thứ 4 bằng dấu gạch ngang, tức là thêm một dấu gạch nối vào vị trí thứ 4 trong ô:

= REPLACE (A2,4,0, “-“)

Kết quả của công thức Replace ở trên là như sau:

= REPLACE (REPLACE (A2,4,0, “-“), 8,0, “-“)

Kết quả là, bạn sẽ nhận được số điện thoại ở định dạng mong muốn:

= DATEVALUE (REPLACE (REPLACE (A2,3,0, “/”), 6,0, “/”))

Bạn không giới hạn số lượng hàm mà bạn có thể lồng trong một công thức (các phiên bản hiện đại của Excel 2010, 2013 và 2023 cho phép tối đa 8192 ký tự và 64 chức năng lồng vào nhau trong một công thức).

Đăng ký ngay: Khóa học Word Excel cơ bản

Ví dụ, bạn có thể sử dụng 3 hàm REPLACE lồng nhau để có một số trong A2 xuất hiện như ngày tháng và thời gian:

Cho đến nay, trong tất cả các ví dụ chúng ta đã làm việc với các giá trị có tính chất giống nhau và đã thay thế ở cùng một vị trí trong mỗi ô. Nhưng công việc thực tế thường phức tạp hơn thế. Trong bảng tính của bạn, các ký tự được thay thế có thể không nhất thiết xuất hiện ở cùng một nơi trong mỗi ô và do đó bạn sẽ phải tìm vị trí của ký tự đầu tiên cần được thay thế. Ví dụ sau đây sẽ chứng minh những điều đó.

Giả sử bạn có một danh sách địa chỉ email trong cột A. Và tên miền của một công ty đã thay đổi từ “ABC” sang, nói “BCA”. Vì vậy, bạn phải cập nhật tất cả địa chỉ email của khách hàng theo.

Nhưng vấn đề là tên khách hàng có chiều dài khác nhau, và đó là lý do tại sao bạn không thể xác định chính xác vị trí bắt đầu của tên công ty. Nói cách khác, bạn không biết giá trị cung cấp trong đối số start_num của hàm . Để tìm ra nó, sử dụng hàm để xác định vị trí của kí tự đầu tiên trong chuỗi “@abc”:

= FIND(“@ abc”, A2)

Và sau đó, cung cấp hàm ở trên trong đối số start_num của công thức :

= REPLACE (A2, FIND(“@ abc”, A2), 4 “@bca”)

Mẹo. Chúng tôi bao gồm “@” trong công thức và để tránh thay thế tình cờ trong phần tên của địa chỉ thư điện tử. Tất nhiên, khả năng này xảy ra rất ít.

Như bạn thấy trong hình sau, công thức không có vấn đề gì khi tìm và thay thế văn bản cũ bằng cái mới. Tuy nhiên, nếu chuỗi văn bản được thay thế không được tìm thấy, công thức trả về kết quả lỗi #VALUE!:

= IFERROR (REPLACE (A2, FIND (“@ abc”, A2), 4 “@bca”), A2)

Và công thức cải tiến này hoạt động khá hoàn hảo, phải không?

Hàm SUBSTITUTE trong Excel

Hàm SUBSTITUTE trong Excel thay thế một hoặc nhiều kí tự cho trước tại một hoặc nhiều vị trí bằng kí tự cụ thể.

Cú pháp của hàm SUBSTITUTE như sau:

SUBSTITUTE (text, old_text, new_text, [instance_num])

Ba đối số đầu tiên được yêu cầu và đối số cuối cùng là tùy chọn.

Text– văn bản ban đầu mà bạn muốn thay thế các ký tự. Có thể được cung cấp như là một chuỗi, ô tham chiếu, hoặc kết quả của một công thức khác.

Old_text – ký tự mà bạn muốn thay thế.

New_text – ký tự mới để thay thế old_text.

Instance_num – vị trí thực hiện thay thế old_text. Nếu bỏ qua, mỗi lần văn bản cũ xuất hiện sẽ được thay đổi thành văn bản mới.

= SUBSTITUTE (A2, “1”, “2”, 1) – Thay thế sự xuất hiện đầu tiên của “1” bằng “2”.

= SUBSTITUTE (A2, “1”, “2”, 2) – Thay thế lần xuất hiện thứ hai của “1” bằng “2”.

Thay thế các ký tự khác nhau bằng một công thức (lồng các hàm SUBSTITUTE)

Như trường hợp với hàm , bạn có thể lồng các SUBSTITUTE trong một công thức để thực hiện một số thay thế cùng lúc, tức là thay thế nhiều ký tự bằng một công thức.

= SUBSTITUTE (A2, “PR”, “Project “) = SUBSTITUTE (A2, “ML”, “Milestone “) = SUBSTITUTE (A2, “T”, “Task “)

Giả sử bạn có một chuỗi văn bản như “PR1, ML1, T1” trong ô A2, trong đó “PR” là viết tắt của “Project”, ML “viết tắt của” Milestone “và” T “có nghĩa là” Task “. Bạn muốn thay thế 3 ba mã này với tên đầy đủ. Để đạt được điều này, bạn có thể viết 3 công thức SUBSTITUTE khác nhau:

= SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (A2, “PR”, “Project “), “ML”, “Milestone “), “T”, “Task “)

Và sau đó lồng với nhau trong 1 công thức:

Các hàm REPLACE và SUBSTITUTE của Excel rất giống nhau và cả hai đều được thiết kế để thay đổi chuỗi văn bản. Sự khác biệt của hai hàm như sau:

SUBSTITUTE thay thế một hoặc nhiều vị trí của kí tự hoặc chuỗi văn bản nhất định. Vì vậy, nếu bạn chỉ biết đoạn cần thay thế, hãy sử dụng hàm SUBSTITUTE.

REPLACE thay thế các ký tự tại một vị trí xác định của một chuỗi văn bản. Vì vậy, nếu bạn biết vị trí của kí tự được thay thế, sử dụng hàm .

Hàm SUBSTITUTE cho phép thêm một tham số tùy chọn (instance_num) xác định vị trí thay thế của old_text thành new_text.

Nguồn: Ablebits, dịch và biên tập bởi Hocexcel Online.