Bài viết này mô tả cú pháp công thức và cách sử dụng hàm REPLACE và REPLACEB trong Microsoft Excel
Sự miêu tả
REPLACE thay thế một phần của chuỗi văn bản, dựa trên số lượng ký tự bạn chỉ định, bằng một chuỗi văn bản khác
REPLACEB thay thế một phần của chuỗi văn bản, dựa trên số byte bạn chỉ định, bằng một chuỗi văn bản khác
Quan trọng.
Các chức năng này có thể không khả dụng ở tất cả các ngôn ngữ
REPLACE được thiết kế để sử dụng với các ngôn ngữ sử dụng bộ ký tự một byte [SBCS], trong khi REPLACEB được thiết kế để sử dụng với các ngôn ngữ sử dụng bộ ký tự hai byte [DBCS]. Cài đặt ngôn ngữ mặc định trên máy tính của bạn ảnh hưởng đến giá trị trả về theo cách sau
REPLACE luôn đếm từng ký tự, dù là byte đơn hay byte kép, là 1, bất kể cài đặt ngôn ngữ mặc định là gì
REPLACEB đếm mỗi ký tự byte kép là 2 khi bạn đã bật tính năng chỉnh sửa ngôn ngữ hỗ trợ DBCS và sau đó đặt ngôn ngữ đó làm ngôn ngữ mặc định. Mặt khác, REPLACEB đếm từng ký tự là 1
Các ngôn ngữ hỗ trợ DBCS bao gồm tiếng Nhật, tiếng Trung [Giản thể], tiếng Trung [Phồn thể] và tiếng Hàn
cú pháp
REPLACE[old_text, start_num, num_chars, new_text]
REPLACEB[old_text, start_num, num_byte, new_text]
Cú pháp hàm REPLACE và REPLACEB có các đối số sau
Old_text Bắt buộc. Văn bản mà bạn muốn thay thế một số ký tự
Start_num Bắt buộc. Vị trí của ký tự trong old_text mà bạn muốn thay thế bằng new_text
Số_ký tự Bắt buộc. Số ký tự trong old_text mà bạn muốn REPLACE thay thế bằng new_text
Số_byte Bắt buộc. Số byte trong old_text mà bạn muốn REPLACEB thay thế bằng new_text
Văn bản mới Bắt buộc. Văn bản sẽ thay thế các ký tự trong old_text
Thí dụ
Sao chép dữ liệu ví dụ trong bảng sau và dán vào ô A1 của trang tính Excel mới. Để các công thức hiển thị kết quả, hãy chọn chúng, nhấn F2, rồi nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để xem tất cả dữ liệu
Dữ liệu
abcdefghijk
2009
123456
Công thức
Mô tả [Kết quả]
Kết quả
=REPLACE[A2,6,5,"*"]
Thay thế năm ký tự trong abcdefghijk bằng một ký tự *, bắt đầu bằng ký tự thứ sáu [f]
Trong hướng dẫn này, bạn sẽ tìm hiểu cách tìm vị trí xuất hiện cuối cùng của một ký tự trong một chuỗi trong Excel
Vài ngày trước, một đồng nghiệp đã đưa ra vấn đề này
Anh ấy có một danh sách các URL như hình bên dưới và anh ấy cần trích xuất tất cả các ký tự sau dấu gạch chéo cuối cùng [“/”]
Ví dụ: từ https. //thí dụ. com/archive/Jan anh ấy phải trích xuất 'Jan'.
Sẽ thực sự dễ dàng nếu chỉ có một dấu gạch chéo trong các URL
Những gì anh ấy có là một danh sách khổng lồ gồm hàng nghìn URL có độ dài khác nhau và số lượng dấu gạch chéo về phía trước khác nhau
Trong những trường hợp như vậy, mẹo là tìm vị trí của lần xuất hiện cuối cùng của dấu gạch chéo lên trong URL
Trong hướng dẫn này, tôi sẽ chỉ cho bạn hai cách để làm điều này
- Sử dụng công thức Excel
- Sử dụng chức năng tùy chỉnh [được tạo qua VBA]
Lấy Vị trí Cuối cùng của Ký tự bằng Công thức Excel
Khi bạn có vị trí của lần xuất hiện cuối cùng, bạn có thể chỉ cần trích xuất bất kỳ thứ gì ở bên phải của nó bằng hàm RIGHT
Đây là công thức tìm vị trí cuối cùng của dấu gạch chéo về phía trước và trích xuất tất cả văn bản ở bên phải của nó
=RIGHT[A2,LEN[A2]-FIND["@",SUBSTITUTE[A2,"/","@",LEN[A2]-LEN[SUBSTITUTE[A2,"/",""]]],1]]
Công thức này hoạt động như thế nào?
Hãy chia nhỏ công thức và giải thích cách thức hoạt động của từng phần của nó
- SUBSTITUTE[A2,”/”, “” ] – Phần này của công thức thay thế . Vì vậy, ví dụ: Trong trường hợp bạn muốn tìm sự xuất hiện của bất kỳ chuỗi nào khác với dấu gạch chéo lên, hãy sử dụng chuỗi đó tại đây.
- LEN[A2]-LEN[SUBSTITUTE[A2,”/”, “” ]] – This part would tell you how many forward slashes are there in the string. It simply subtracts the length of the string without the forward slash from the length of the string with forward-slashes.
- SUBSTITUTE[A2,”/”,”@”,LEN[A2]-LEN[SUBSTITUTE[A2,”/”,””]]] – Phần này của . Ý tưởng là làm cho nhân vật đó trở nên độc đáo. Bạn có thể sử dụng bất kỳ ký tự nào bạn muốn. Chỉ cần đảm bảo rằng nó là duy nhất và chưa xuất hiện trong chuỗi.
- FIND[“@”,SUBSTITUTE[A2,”/”,”@”,LEN[A2]-LEN[SUBSTITUTE[A2,”/”,””]]],1] – This part of the formula would give you the position of the last forward slash.
- LEN[A2]-FIND[“@”,SUBSTITUTE[A2,”/”,”@”,LEN[A2]-LEN[SUBSTITUTE[A2,”/”,””]]],1 . – This part of the formula would tell us how many characters are there after the last forward slash.
- =RIGHT[A2,LEN[A2]-FIND[“@”,SUBSTITUTE[A2,”5. ]”,”@”,[LEN[A2]-LEN[SUBSTITUTE[A2,”5. ]”,””]]]/3],1]] [mới]