Nhiều SUBSTITUTE Google Trang tính

Khi thực hiện các công việc lặp đi lặp lại trên quy mô lớn, bạn có thể cần phải thay thế nhiều ký tự hoặc giá trị cùng một lúc. Hướng dẫn này xem xét kỹ hơn cách thay thế nhiều ký tự trong excel dựa trên vị trí của chúng bằng một ký tự khác dựa trên nội dung. Chúng tôi sẽ áp dụng một số chức năng và mã Ứng dụng Visual Basic để đạt được nhiệm vụ này

Mục lục ẩn

Tải sách bài tập thực hành

6 cách thích hợp để thay thế nhiều ký tự

1. Sử dụng hàm SUBSTITUTE để thay thế nhiều ký tự

2. Lồng hàm SUBSTITUTE để thay thế nhiều ký tự

3. Thực hiện hàm SUBSTITUTE với hàm INDEX để thay thế nhiều ký tự

4. Áp dụng hàm REPLACE để thay thế nhiều ký tự

5. Lồng hàm REPLACE để thay thế nhiều ký tự

6. Chạy mã VBA để thay thế nhiều ký tự

Phần kết luận

Đọc thêm

Tải sách bài tập thực hành

Tải xuống sách bài tập thực hành này để thực hành trong khi bạn đang đọc bài viết này

trao đổi nhân vật. xlsm

6 cách thích hợp để thay thế nhiều ký tự

1. Sử dụng hàm SUBSTITUTE để thay thế nhiều ký tự

Trong Excel, Hàm SUBSTITUTE thay thế một hoặc nhiều phiên bản của một ký tự hoặc chuỗi văn bản được chỉ định bằng (các) ký tự khác. Trong ảnh chụp màn hình bên dưới, đây là tập hợp dữ liệu tên các phiên bản Microsoft Word. Chẳng hạn, chúng tôi muốn thay thế “Word” bằng “Excel”. Chúng ta sẽ áp dụng Hàm SUBSTITUTE để hoàn thành

Cú pháp của Hàm SUBSTITUTE trong Excel như sau

SUBSTITUTE(văn bản, old_text, new_text, [instance_num])

Văn bản – văn bản gốc mà bạn muốn thay đổi

Old_text – các ký tự bạn muốn thay thế

New_text – các ký tự mới để sử dụng thay cho văn bản cũ

Instance_num – tỷ lệ văn bản cũ mà bạn muốn thay thế Nếu để trống tham số này, mọi phiên bản của văn bản cũ sẽ được thay thế bằng nội dung mới

Chẳng hạn, tất cả các công thức bên dưới thay thế “1” bằng “2” trong ô B5, nhưng kết quả khác nhau dựa trên số bạn cung cấp trong đối số cuối cùng

a) =SUBSTITUTE(B5, “Word”, “Excel”, 1) – Thay thế tỷ lệ xuất hiện đầu tiên của “Word” bằng “Excel“

b) =SUBSTITUTE(B5, “Word”, “Excel”, 2) – Thay thế tỷ lệ xuất hiện thứ hai của “Word” bằng “Excel“

c) =SUBSTITUTE(B5, “Word”, “Excel”) – Thay thế tất cả các trường hợp của “Word” bằng “Excel“

Trong ảnh chụp màn hình bên dưới, chúng tôi đã hiển thị ví dụ cho lần xuất hiện đầu tiên. Để làm như vậy, chỉ cần làm theo các bước

Bước 1

• Nhập công thức sau vào ô,

=SUBSTITUTE(B5,"Word","Excel",1)

Bước 2

• Nhấn Enter để xem kết quả

Bước 3

• Lặp lại các bước trước đó cho hai tiêu chí còn lại

Kết quả là bạn sẽ nhận được các giá trị cho lần xuất hiện đầu tiên, thứ hai và tất cả liên tiếp để thay thế nhiều ký tự trong excel

Ghi chú. Hãy nhớ rằng Hàm SUBSTITUTE phân biệt chữ hoa chữ thường. Đảm bảo nhập chữ hoa và chữ thường hoàn hảo. Như bạn thấy trong hình bên dưới, đối với chữ thường, excel không thể tìm thấy các giá trị. Vì vậy, không có thay thế xảy ra.

Đọc thêm. Cách tìm và thay thế nhiều từ trong danh sách trong Excel

2. Lồng hàm SUBSTITUTE để thay thế nhiều ký tự

Để thực hiện nhiều phép thay thế, trong một công thức, bạn có thể lồng nhiều hàm SUBSTITUTE

Giả sử bạn có một giá trị văn bản như “art. , sửa đổi. , cl. ” trong ô B5, nơi “nghệ thuật. ” là viết tắt của “bài báo”, “sửa đổi. ” là viết tắt của “sửa đổi” và “cl. ” có nghĩa là “mệnh đề“

Những gì bạn muốn là thay thế ba mã bằng tên đầy đủ. Bạn có thể thực hiện điều này bằng cách sử dụng ba công thức SUBSTITUTE riêng biệt

=SUBSTITUTE(B5,”nghệ thuật. ","mạo từ")

=SUBSTITUTE(B5,” sửa đổi. ”,”sửa đổi”)

=SUBSTITUTE(B5, “cl. ”, mệnh đề”)

Sau đó lồng chúng vào trong cái kia

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,”art. ”,”bài viết”),”sửa đổi. ”,”sửa đổi”),”cl. ”, mệnh đề”)

Để hoàn thành, hãy làm theo các bước dưới đây

Bước 1

• Trong ô C5, nhập công thức sau

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,"art.","article"),"amend.","amendments"),"cl.","clause")

Bước 2

• Sau đó, nhấn Enter để xem sự thay đổi

Bước 3

• Sao chép công thức trong các ô cần thiết khác

Do đó, bạn sẽ thấy các giá trị thay thế được hiển thị trong ảnh chụp màn hình bên dưới

Đọc thêm. Cách thay thế văn bản trong công thức Excel (7 cách dễ dàng)

3. Thực hiện hàm SUBSTITUTE với hàm INDEX để thay thế nhiều ký tự

Ngoài các phương pháp trước, bạn cũng có thể sử dụng Hàm SUBSTITUTE với Hàm INDEX để thay thế nhiều ký tự

Ví dụ: bạn muốn thay thế màu đỏ và xanh lam bằng xanh lục và trắng liên tiếp. Nhiều Hàm SUBSTITUTE có thể được lồng vào nhau và hàm INDEX có thể được sử dụng để cung cấp các cặp tìm/thay thế từ một bảng khác

Để thay thế nhiều ký tự áp dụng cả Hàm SUBSTITUTE và Hàm INDEX, hãy làm theo các bước bên dưới

Bước 1

• Đầu tiên, nhập công thức sau vào ô C5,

=SUBSTITUTE(SUBSTITUTE(B5,INDEX(E5:E6,=SUBSTITUTE(SUBSTITUTE(B5,INDEX(E5:E6,1),INDEX(F5:F6,1)),INDEX(E5:E6,2),INDEX(F5:F6,2))

Ở đâu,

Phạm vi tìm INDEX là E5. E6

Phạm vi tìm INDEX là E5. E6

Bước 2

• Sau đó nhấn Enter để xem kết quả

• Cuối cùng, Sao chép công thức cho các ô khác

Đọc thêm. Tìm và thay thế văn bản trong một phạm vi bằng Excel VBA (Macro và UserForm)

bài đọc tương tự

• Tìm và thay thế nhiều giá trị trong Excel (6 phương pháp nhanh)
• Cách thay thế các ký tự đặc biệt trong Excel (6 cách)
• Thay thế văn bản của một ô dựa trên điều kiện trong Excel (5 phương pháp)
• Cách tìm và thay thế trong vùng chọn trong Excel (7 phương pháp)
• Excel VBA để tìm và thay thế văn bản trong một cột (2 ví dụ)

4. Áp dụng hàm REPLACE để thay thế nhiều ký tự

Trong phần sau chúng tôi sẽ hướng dẫn cách áp dụng Hàm REPLACE để thay thế nhiều ký tự trong excel. Hàm REPLACE trong Excel cho phép bạn hoán đổi một hoặc một số ký tự trong chuỗi văn bản với một ký tự khác hoặc một nhóm ký tự

Cú pháp của hàm REPLACE trong Excel như sau

REPLACE(old_text, start_num, num_chars, new_text)

Như bạn thấy, Hàm REPLACE có 4 đối số, tất cả đều là đối số bắt buộc

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

Start_num – vị trí của ký tự đầu tiên trong old_text

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

New_text – văn bản thay thế

Ví dụ: để thay từ “Face” cho “Fact“, bạn có thể làm theo các bước sau

Bước 1

• Đầu tiên, trong ô D5, nhập công thức sau,

=REPLACE(B5, 4, 1,"t")

Bước 2

• Sau đó, nhấn Enter để xem sự thay đổi

Bước 3

• Để thực hiện tất cả các thay đổi được hiển thị trong ảnh chụp màn hình bên dưới, hãy sao chép công thức cho các ô được yêu cầu

Đọc thêm. Cách tìm và thay thế nhiều từ cùng một lúc trong Excel (7 phương pháp)

5. Lồng hàm REPLACE để thay thế nhiều ký tự

Rất thường xuyên, có thể bạn sẽ cần thay đổi nhiều mục trong cùng một ô. Tất nhiên, bạn có thể thực hiện một lần thay thế, xuất kết quả trung gian sang một cột mới, sau đó sử dụng Hàm REPLACE một lần nữa. Tuy nhiên, sử dụng Hàm REPLACE lồng nhau, cho phép bạn thực hiện một số thay thế bằng một công thức duy nhất, là một tùy chọn tốt hơn và chuyên nghiệp hơn. Tương tự như Hàm SUBSTITUTE, bạn cũng có thể áp dụng lồng trong Hàm REPLACE

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 như “123-456-789” và bạn muốn thêm khoảng trống để làm cho chúng trông khác đi. Nói cách khác, bạn muốn đổi “123-456-789” thành “123 456 789“

Để Thay thế nhiều ký tự ở nhiều nơi, hãy làm theo các bước bên dưới

Bước 1

• Nhập công thức sau vào ô C5 lúc đầu,

=REPLACE(REPLACE(B5,4,1," "),8,1," ")

Bước 2

• Thứ hai, nhấn Enter để xem sự thay đổi trong ô D5

Bước 3

• Cuối cùng, sao chép công thức và lặp lại các bước cho các ô cần thiết

Đọc thêm. Cách tìm và thay thế cột trong Excel (6 cách)

6. Chạy mã VBA để thay thế nhiều ký tự

Thật thú vị, bạn có thể áp dụng mã VBA để có được mã giống nhau để thay thế nhiều ký tự. Ngoài ra, bạn có thể thay đổi nó theo ý muốn mà không cần quan tâm đến số ký tự hoặc vị trí như đã thấy trong hai chức năng được mô tả trước đó

Để chạy mã VBA nhằm thay thế nhiều ký tự, chỉ cần làm theo các bước được mô tả bên dưới

Bước 1

• Đầu tiên, nhấn Alt + F11 để mở .
• Chuyển đến tab Chèn
• Sau đó, chọn Mô-đun

Bước 2

• Sao chép mã VBA sau,

Sub replaceAll()

'declare object variable to hold reference to cell you work with
Dim myCell As Range

'declare variables to hold parameters for string replacement (string to replace and replacement string)
Dim myStringToReplace As String
Dim myReplacementString As String

'identify cell you work with
Set myCell = ThisWorkbook.Worksheets("VBA").Range("C5")
'specify parameters for string replacement (string to replace and replacement string)
myStringToReplace = "234-235-5689"
myReplacementString = "234 235 5689"

'replace all occurrences within string in cell you work with, and assign resulting string to Range.
'Value property of cell you work with
myCell.Value = Replace(Expression:=myCell.Value, 
Find:=myStringToReplace, Replace:=myReplacementString)

End Sub

• Ở đâu,

Sổ làm việc này. Trang tính (“tên trang tính hiện tại của bạn”)

Phạm vi ("ô tham chiếu của bạn")

myStringToReplace = “giá trị bạn muốn thay thế”

myReplacementString = “giá trị thay thế của bạn”

• Sau đó, dán nó vào cửa sổ chương trình
• Nhấn Enter để xem định dạng số được thay thế

Đọc thêm. Cách tìm và thay thế bằng công thức trong Excel (4 ví dụ)

Phần kết luận

Để kết thúc, tôi hy vọng bài viết này đã cung cấp hướng dẫn chi tiết để thay thế nhiều ký tự trong Excel. Tất cả các quy trình này nên được học và áp dụng cho tập dữ liệu của bạn. Hãy xem sách bài tập thực hành và kiểm tra những kỹ năng này. Chúng tôi có động lực để tiếp tục thực hiện các hướng dẫn như thế này vì sự hỗ trợ quý báu của bạn

Nếu bạn có bất kỳ câu hỏi nào - Hãy hỏi chúng tôi. Ngoài ra, vui lòng để lại nhận xét trong phần bên dưới