Xin chào các chuyên gia. Tôi có một trang tính [Excel] với một số ô có dấu “*”
Tôi cần thay thế ký tự * [dấu sao hoặc shift+8]
Vấn đề là * tất nhiên khớp với tất cả nội dung của ô, đây không phải là điều tôi muốn
Làm cách nào để tắt hành vi này?
Tôi cần trợ giúp tạo công thức để sử dụng cho tình huống mà tôi gặp phải với bảng tính excel mà sếp đưa cho tôi hôm nay. Đây là tình hình. Cột A có danh sách tên nhân viên [hơn 400 người trong số họ] hiện được định dạng bằng các màu ô cụ thể tùy thuộc vào bộ phận của họ. Các cột từ B đến AC [tổng cộng 28 cột] có tên các khóa đào tạo được cung cấp với nhân viên đã tham gia khóa đào tạo đó được liệt kê bên dưới nhưng không có màu ô. Tôi muốn có thể lấy tên từ cột a và thay thế tất cả các trường hợp trùng tên bằng màu ô mới. Tôi đã bắt đầu sử dụng phương pháp tìm và thay thế nhưng với hơn 400 tên sẽ mất nhiều thời gian. Tôi hy vọng có một công thức cho phép tôi thay đổi tất cả chúng cùng một lúc
Cám ơn vì tất cả sự giúp đỡ bất kỳ lúc nào
Thêm. Đây là một lời giải thích tốt hơn tôi hy vọng về những gì tôi muốn làm. Ví dụ. Cột A có Joe Smith [ô màu lam], Jane Doe [ô màu lục] và Harry Potter [ô màu cam]. Tôi muốn thay thế tất cả các phiên bản của những tên này bằng màu ô trong mỗi cột trong số 28 cột nhưng muốn thực hiện tất cả cùng một lúc thay vì từng cái một
Hướng dẫn này sẽ chỉ cho bạn cách xóa tất cả các ký tự đặc biệt khỏi các ô trong Excel và Google Trang tính
Xóa ký tự đặc biệt
Excel không cung cấp bất kỳ chức năng nào để loại bỏ tất cả các ký tự đặc biệt khỏi chuỗi cùng một lúc. Nếu chỉ muốn loại bỏ một ký tự đặc biệt, bạn có thể sử dụng hàm SUBSTITUTE [xem thêm tại bài viết Loại bỏ các ký tự không mong muốn]
Nếu bạn muốn sử dụng một công thức duy nhất để xóa các ký tự đặc biệt, bạn sẽ cần sử dụng Hàm do người dùng xác định [UDF], chẳng hạn như RemoveSpecChar
=RemoveSpecChar[B3]Để làm cho UDF của bạn hoạt động, hãy mở Visual Basic Editor bằng cách nhấn Alt+F11. Nhấp chuột phải vào tên sổ làm việc của bạn trong cửa sổ Dự án ở phía bên trái và chèn một mô-đun mới. Vào mô-đun này, sao chép UDF bên dưới
Function RemoveSpecChar[sInput As String] As String
Dim sSpecChar As String
Dim i As Long
sSpecChar = "\/:*?™""®|.&@# [_+`©~];-+=^$!,'"
For i = 1 To Len[sSpecChar]
sInput = Replace$[sInput, Mid$[sSpecChar, i, 1], ""]
Next
RemoveSpecChar = sInput
End FunctionBây giờ UDF của bạn đã được xác định và sẵn sàng để sử dụng. Đừng quên lưu sổ làm việc của bạn dưới dạng Sổ làm việc hỗ trợ macro Excel [*. xlsm]
Câu trả lời của bạn sẽ thay thế tất cả các dấu '?' . Có những trường hợp tôi muốn có dấu "-" ở vị trí của '?' . Tôi nghĩ rằng bạn đã không đọc câu hỏi của tôi hoàn toàn hoặc tôi đang thiếu một cái gì đó?
Khi làm việc với dữ liệu văn bản trong Excel, chúng ta có thể cần thay thế các ký tự cụ thể không cần thiết. Để đơn giản hóa tác vụ, chúng tôi sẽ thay thế các ký tự đó khỏi dữ liệu của bạn. Hôm nay, trong bài viết này, chúng ta sẽ xem xét bảy kỹ thuật nhanh chóng và dễ dàng mà Excel thay thế các ký tự đặc biệt bằng các hình minh họa phù hợp
Mục lục
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
Ký tự đặc biệt Thay thế. xlsm
6 cách phù hợp để thay thế các ký tự đặc biệt trong Excel
Giả sử, chúng tôi có các Mã sản phẩm khác nhau cho các Sản phẩm khác nhau trong Cột C được bắt đầu bằng ký tự đặc biệt # và tên của sản phẩm được cung cấp trong Cột B. Dưới đây là tổng quan về tập dữ liệu cho nhiệm vụ hôm nay của chúng ta
1. Áp dụng lệnh Find & Select để thay thế các ký tự đặc biệt trong Excel
Trong phương pháp này, chúng ta sẽ tìm hiểu cách thay thế các ký tự đặc biệt trong Excel bằng lệnh Find & Select. Ở đây, chúng tôi muốn thay thế # bằng trống từ tập dữ liệu của mình. Hãy làm theo hướng dẫn bên dưới
bước
- Từ tab Trang chủ của bạn, hãy chuyển đến,
Trang chủ → Chỉnh sửa → Tìm & Chọn → Thay thế
- Sau khi nhấp vào tùy chọn Thay thế, cửa sổ Tìm và Thay thế sẽ bật lên
- Từ cửa sổ Tìm và Thay thế, nhập # vào hộp Tìm gì và Thay thế bằng hộp giữ
- Sau đó, click vào ô Replace All
- Bây giờ, một hộp thoại mới có tên Microsoft Excel xuất hiện trước mặt bạn hiển thị với Tất cả đã xong. Chúng tôi đã thực hiện & thay thế
- Sau đó nhấn OK
- Sau khi hoàn tất quy trình trên, bạn sẽ có thể thay thế ký tự đặc biệt # bằng Empty như trong ảnh chụp màn hình bên dưới
Đọc thêm. Cách tìm và thay thế các giá trị bằng ký tự đại diện trong Excel
2. Chèn hàm REPLACE để thay thế các ký tự đặc biệt trong Excel
Bạn có thể sử dụng hàm REPLACE để thay thế bất kỳ ký tự nào từ bất kỳ ô nào. Để thay thế các ký tự đặc biệt trong Excel bằng hàm REPLACE bạn làm theo các bước dưới đây để tìm hiểu
bước
- Trước hết, chọn một ô trống nơi chúng tôi sẽ nhập hàm REPLACE, từ dữ liệu của chúng tôi, chúng tôi sẽ chọn ô D5
- Sau khi chọn ô D5, hãy nhập công thức sau vào Thanh công thức,
=REPLACE[C5,1,1,""]
- Ở đây, C5 chỉ ô mà bạn muốn thay thế ký tự đặc biệt, Ô đầu tiên cho biết bạn muốn thay thế ký tự từ ký tự đầu tiên của văn bản, ô thứ 2 cho biết bạn muốn thay thế một ký tự và [” ”
- Bây giờ, nhấn Enter trên bàn phím của bạn và bạn sẽ có thể nhận được kết quả trả về của hàm REPLACE và kết quả trả về là 4227
- Sau đó, đặt con trỏ của bạn ở phía dưới bên phải của ô D5 và dấu hiệu Tự động điền sẽ xuất hiện cho chúng tôi. Bây giờ, hãy kéo ký hiệu Tự động điền xuống dưới
- Trong khi hoàn tất quy trình trên, bạn sẽ có thể thay thế # bằng Empty đã được cung cấp trong ảnh chụp màn hình
Đọc thêm. Cách tìm và thay thế bằng công thức trong Excel [4 ví dụ]
3. Sử dụng hàm SUBSTITUTE để thay thế các ký tự đặc biệt trong Excel
Sử dụng Hàm SUBSTITUTE là một cách khác để thay thế các ký tự đặc biệt trong Excel. Từ tập dữ liệu của chúng tôi, chúng tôi muốn thay thế # bằng Empty bằng cách sử dụng Hàm SUBSTITUTE. Thực hiện theo các hướng dẫn dưới đây để tìm hiểu
bước
- Chọn ô D5 trước để áp dụng Hàm SUBSTITUTE
- Sau đó, nhập Hàm SUBSTITUTE trong Thanh công thức. Chức năng là,
=SUBSTITUTE[C5, "#", ""]
- Sau khi nhập Hàm SUBSTITUTE trong Thanh công thức, nhấn Enter trên bàn phím của bạn và bạn sẽ có thể nhận được đầu ra của hàm, đầu ra là 4227 thay thế các ký tự đặc biệt
- Bây giờ, đặt con trỏ của bạn ở phía dưới bên phải của ô D5 và dấu hiệu Tự động điền sẽ xuất hiện cho chúng tôi. Bây giờ, hãy kéo dấu AutoFill xuống dưới, và bạn sẽ nhận được kết quả của Hàm SUBSTITUTE
Đọc thêm. Cách sử dụng hàm thay thế trong Excel VBA [3 ví dụ]
bài đọc tương tự
- Excel VBA. Cách tìm và thay thế văn bản trong tài liệu Word
- 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 thêm văn bản trước công thức trong Excel [5 cách dễ dàng]
- Tìm và thay thế nhiều giá trị trong Excel [6 phương pháp nhanh]
- Cách thay thế văn bản trong công thức Excel [7 cách dễ dàng]
4. Thực hiện lệnh Flash Fill để thay thế các ký tự đặc biệt trong Excel
Cách đơn giản nhất là thay thế ký tự đặc biệt trong Excel bằng Flash Fill Command. Để thay thế các ký tự đặc biệt bằng Flash Fill Command, hãy làm theo hướng dẫn bên dưới
bước
- Đầu tiên, chọn ô D5 và gõ thủ công Mã sản phẩm có tên Tẩy không có ký tự đặc biệt
- Bây giờ, từ Tab Trang chủ, đi đến,
Trang chủ → Chỉnh sửa → Điền → Lấp đầy Flash
- Cuối cùng, bạn sẽ nhận được tất cả các mã sản phẩm không có ký tự đặc biệt bằng cách nhấn vào tùy chọn Flash Fill
Đọc thêm. Excel VBA để tìm và thay thế văn bản trong một cột [2 ví dụ]
5. Áp dụng hàm RIGHT và LEN để thay thế các ký tự đặc biệt trong Excel
Trong phương pháp này, chúng ta sẽ học cách thay thế các ký tự đặc biệt trong Excel Bằng cách sử dụng các hàm RIGHT và LEN. Hãy làm theo các bước dưới đây để tìm hiểu
bước
- Đầu tiên, chọn ô D5 để thay thế các ký tự đặc biệt từ ô C5
- Sau đó, nhập các hàm RIGHT và LEN trong Thanh công thức. Các chức năng trong Thanh công thức là,
=RIGHT[C5,LEN[C5]-1]
- Ở đây, C5 đề cập đến ô mà bạn muốn thay thế ký tự, hàm RIGHT cho biết rằng các ký tự của văn bản sẽ được lấy từ ký tự cuối cùng và LEN[C5]-1 cho biết văn bản kết quả sẽ không có ký tự
- Bây giờ, nhấn Enter trên bàn phím của bạn và bạn sẽ nhận được đầu ra của các chức năng. Đầu ra của các chức năng là 4227
- Sau khi nhấn Enter trên bàn phím, hãy đặt con trỏ của bạn ở Dưới cùng bên phải của ô D5 và ngay lập tức một biểu tượng Tự động điền sẽ xuất hiện trước mặt bạn
- Sau đó, kéo ô tự động điền xuống dưới và bạn sẽ có thể thay thế các ký tự đặc biệt từ cột C
Đọc thêm. Cách tìm và thay thế cột trong Excel [6 cách]
6. Chạy mã VBA để thay thế các ký tự đặc biệt trong Excel
Chúng ta có thể thay thế các ký tự đặc biệt bằng cách chạy Mã VBA. Cách thay thế ký tự đặc biệt trong Excel đơn giản nhất. Hãy làm theo hướng dẫn dưới đây để tìm hiểu
Bước 1
- Trước hết, nhấn các phím ALT + F11 trên bàn phím của bạn để mở Microsoft Visual Basic for Applications
- Từ cửa sổ Microsoft Visual Basic for Applications, đi đến,
Chèn → Mô-đun
- Sau khi nhấp vào Mô-đun, một cửa sổ mới có tên Mô-đun 1 sẽ xuất hiện ngay trước mặt bạn
Bước 2
- Trong cửa sổ Mô-đun 1, Dán đoạn mã sau
Function ReplaceSpecial[Str As String] As String
'updatedbyExceldemy 0043
Dim Char As String
Dim L As Long
Char = "#$%[]^*&"
For L = 1 To Len[Char]
Str = Replace$[Str, Mid$[Char, L, 1], ""]
Next
RemoveSpecial = Str
End Function
- Sau đó lưu và đóng mã này, quay lại trang tính
Bước 3
- Bây giờ, chọn ô D5 để nhập công thức ReplaceSpecial
- Sau đó, nhập công thức ReplaceSpecial trong Thanh công thức. Công thức trong Thanh công thức là,
=ReplaceSpecial[C5]
- Bây giờ, nhấn Enter trên bàn phím của bạn và bạn sẽ nhận được đầu ra 4227 dưới dạng hàm ReplaceSpecial trong ô D5
- Sau đó, đặt con trỏ của bạn ở Dưới cùng bên phải của ô D5 và ngay lập tức một dấu hiệu Tự động điền xuất hiện trước mặt bạn và kéo nó xuống dưới
- Sau khi kéo thanh autoFill xuống phía dưới, bạn sẽ có thể lấy kết quả của hàm ReplaceSpecial cho toàn bộ cột D đã được thay thế bằng các ký tự đặc biệt
Đọc thêm. Cách tìm và thay thế ký tự dấu hoa thị [*] trong Excel
Những điều cần ghi nhớ
👉 Trong khi sử dụng Flash Fill Command, hãy nhập Code of Eraser theo cách thủ công rồi truy cập,
Trang chủ → Chỉnh sửa → Điền → Lấp đầy Flash
👉 Nếu Menu nhà phát triển không xuất hiện trong Menu Bar, hãy nhấn phím ALT + F11 trên bàn phím của bạn để mở cửa sổ Microsoft Visual Basic for Applications
Phần kết luận
Tôi hy vọng tất cả các phương pháp phù hợp được đề cập ở trên để thay thế các ký tự đặc biệt giờ đây sẽ kích thích bạn áp dụng chúng trong bảng tính Excel của mình với năng suất cao hơn. Bạn được hoan nghênh nhất để bình luận nếu bạn có bất kỳ câu hỏi hoặc thắc mắc nào