Nội dung
Ngắt dòng trong cùng một ô, được thêm bằng phím tắt Khác+đăng ký hạng mục thi là một điều hết sức bình thường và phổ biến. Đôi khi chúng được chính người dùng tạo ra để tăng thêm vẻ đẹp cho đoạn văn bản dài. Đôi khi các lần chuyển như vậy được thêm tự động khi dỡ dữ liệu từ bất kỳ chương trình làm việc nào (xin chào 1C, SAP, v.v.). Vấn đề là khi đó bạn không chỉ phải ngưỡng mộ các bảng như vậy mà còn phải làm việc với chúng – và khi đó việc chuyển các ký tự vô hình này có thể là một vấn đề. Và chúng có thể không trở thành – nếu bạn biết cách xử lý chúng một cách chính xác.
Chúng ta hãy xem xét vấn đề này chi tiết hơn.
Loại bỏ ngắt dòng bằng cách thay thế
Nếu chúng ta cần loại bỏ dấu gạch nối, thì điều đầu tiên chúng ta thường nghĩ đến là kỹ thuật “tìm và thay thế” cổ điển. Chọn văn bản rồi gọi cửa sổ thay thế bằng phím tắt Ctrl+H hay qua Trang chủ - Tìm và Chọn - Thay thế (Trang chủ - Tìm & Chọn - Thay thế). Một điểm không nhất quán – không rõ ràng lắm về cách nhập vào trường trên cùng Để tìm (Tìm cái gì) ký tự ngắt dòng vô hình của chúng tôi. Khác+đăng ký hạng mục thi Thật không may, ở đây nó không còn hoạt động nữa, sao chép ký hiệu này trực tiếp từ ô và dán vào đây cũng không thành công.
Sự kết hợp sẽ giúp Ctrl+J - đó là phương án thay thế Khác+đăng ký hạng mục thi trong hộp thoại hoặc trường nhập Excel:
Xin lưu ý rằng sau khi bạn đặt con trỏ nhấp nháy vào trường trên cùng và nhấn Ctrl+J – sẽ không có gì xuất hiện trong trường đó. Đừng sợ – điều này là bình thường, biểu tượng này là vô hình 🙂
Đến trường dưới cùng Thay thế (Thay bằng) hoặc không nhập bất cứ thứ gì hoặc nhập một khoảng trắng (nếu chúng ta không chỉ muốn loại bỏ dấu gạch nối mà còn thay thế chúng bằng khoảng trắng để các dòng không dính vào nhau thành một tổng thể duy nhất). Chỉ cần nhấn nút Thay thế mọi thứ (Thay thế tất cả) và dấu gạch nối của chúng tôi sẽ biến mất:
bóng: sau khi thực hiện thay thế được nhập bằng Ctrl+J ký tự vô hình vẫn còn trong trường Để tìm và có thể gây trở ngại trong tương lai – đừng quên xóa nó bằng cách đặt con trỏ vào trường này và nhấn các phím nhiều lần (để đảm bảo độ tin cậy) Xóa bỏ и Backspace.
Loại bỏ ngắt dòng bằng công thức
Nếu bạn cần giải bài toán bằng công thức thì bạn có thể sử dụng hàm có sẵn IN (LAU DỌN), có thể xóa văn bản của tất cả các ký tự không in được, bao gồm cả các ngắt dòng xấu số của chúng tôi:
Tuy nhiên, tùy chọn này không phải lúc nào cũng thuận tiện vì các đường sau thao tác này có thể được dán lại với nhau. Để ngăn điều này xảy ra, bạn không chỉ cần xóa dấu gạch nối mà còn phải thay thế bằng dấu cách (xem đoạn tiếp theo).
Thay thế ngắt dòng bằng công thức
Và nếu bạn không chỉ muốn xóa mà còn muốn thay thế Khác+đăng ký hạng mục thi chẳng hạn như trên một không gian, thì sẽ cần một công trình khác phức tạp hơn một chút:
Để đặt dấu gạch nối vô hình, chúng tôi sử dụng hàm KÝ HIỆU (CHAR), xuất ra một ký tự theo mã của nó (10). Và sau đó là chức năng THAY THẾ (THAY THẾ) tìm kiếm dấu gạch nối của chúng tôi trong dữ liệu nguồn và thay thế chúng bằng bất kỳ văn bản nào khác, chẳng hạn như bằng dấu cách.
Chia thành các cột theo ngắt dòng
Công cụ quen thuộc với nhiều người và rất tiện dụng Văn bản theo cột từ tab Ngày (Dữ liệu - Văn bản thành Cột) cũng có thể hoạt động hiệu quả với ngắt dòng và chia văn bản từ một ô thành nhiều ô, ngắt văn bản bằng cách Khác+đăng ký hạng mục thi. Để thực hiện việc này, ở bước thứ hai của trình hướng dẫn, bạn cần chọn một biến thể của ký tự phân cách tùy chỉnh Nền tảng khác (Tập quán) và sử dụng phím tắt mà chúng ta đã biết Ctrl+J như một sự thay thế Khác+đăng ký hạng mục thi:
Nếu dữ liệu của bạn có thể chứa nhiều ngắt dòng liên tiếp thì bạn có thể “thu gọn” chúng bằng cách bật hộp kiểm Coi các dấu phân cách liên tiếp là một (Coi các dấu phân cách liên tiếp là một).
Sau khi nhấp vào Sau (Kế tiếp) và trải qua cả ba bước của trình hướng dẫn, chúng ta sẽ nhận được kết quả mong muốn:
Xin lưu ý rằng trước khi thực hiện thao tác này, cần chèn đủ số lượng cột trống vào bên phải cột tách để văn bản thu được không ghi đè lên các giá trị (giá) ở bên phải.
Chia thành các dòng bằng Alt + Enter thông qua Power Query
Một nhiệm vụ thú vị khác là chia văn bản nhiều dòng từ mỗi ô không thành các cột mà thành các dòng:
Việc này làm thủ công mất nhiều thời gian, khó khăn với công thức, không phải ai cũng viết được macro. Nhưng trong thực tế, vấn đề này xảy ra thường xuyên hơn chúng ta mong muốn. Giải pháp đơn giản và dễ dàng nhất là sử dụng phần bổ trợ Power Query cho tác vụ này, phần bổ trợ này đã được tích hợp vào Excel từ năm 2016 và đối với các phiên bản trước 2010-2013, phần bổ trợ này có thể được tải xuống hoàn toàn miễn phí từ trang web của Microsoft.
Để tải dữ liệu nguồn vào Power Query, trước tiên bạn phải chuyển đổi dữ liệu đó thành “bảng thông minh” bằng phím tắt Ctrl+T hoặc bằng nút Định dạng dưới dạng bảng chuyển hướng Trang Chủ (Trang chủ - Định dạng dưới dạng Bảng). Nếu vì lý do nào đó mà bạn không muốn hoặc không thể sử dụng “bảng thông minh”, thì bạn có thể làm việc với những bảng “ngu ngốc”. Trong trường hợp này, chỉ cần chọn phạm vi ban đầu và đặt tên cho nó trên tab Công thức – Trình quản lý tên – Mới (Công thức — Trình quản lý tên — Mới).
Sau đó, trên tab Ngày (nếu bạn có Excel 2016 trở lên) hoặc trên tab Truy vấn nguồn (nếu bạn có Excel 2010-2013), bạn có thể nhấp vào nút Từ bảng / phạm vi (Từ Bảng / Phạm vi)để tải bảng của chúng tôi vào trình soạn thảo Power Query:
Sau khi tải xong chọn cột có chữ nhiều dòng trong ô và chọn lệnh trên tab Main Cột Tách - Theo Dấu phân cách (Trang chủ — Chia cột — Theo dấu phân cách):
Rất có thể Power Query sẽ tự động nhận biết nguyên tắc chia và thay thế chính ký hiệu đó # (lf) ký tự nguồn cấp dòng vô hình (lf = nguồn cấp dòng = nguồn cấp dòng) trong trường đầu vào dấu phân cách. Nếu cần, bạn có thể chọn các ký tự khác từ danh sách thả xuống ở cuối cửa sổ, nếu bạn chọn hộp này lần đầu tiên Tách bằng ký tự đặc biệt (Chia theo ký tự đặc biệt).
Để mọi thứ được chia thành hàng chứ không phải cột – đừng quên chuyển đổi bộ chọn Hàng (Theo hàng) trong nhóm tùy chọn nâng cao.
Tất cả những gì còn lại là nhấp vào OK và có được những gì bạn muốn:
Bảng đã hoàn thành có thể được dỡ trở lại trang tính bằng lệnh Đóng và tải - Đóng và tải vào… chuyển hướng Trang Chủ (Trang chủ - Đóng & Tải - Đóng & Tải vào…).
Điều quan trọng cần lưu ý là khi sử dụng Power Query, bạn phải nhớ rằng khi dữ liệu nguồn thay đổi, kết quả sẽ không được cập nhật tự động, bởi vì. đây không phải là công thức. Để cập nhật, bạn phải nhấp chuột phải vào bảng cuối cùng trên trang tính và chọn lệnh Cập nhật & Lưu (Làm mới) hoặc nhấn nút Cập nhật Tất cả chuyển hướng Ngày (Dữ liệu - Làm mới tất cả).
Macro để chia thành các dòng bằng Alt + Enter
Để hoàn thiện bức tranh, chúng ta cũng hãy đề cập đến giải pháp của vấn đề trước đó với sự trợ giúp của macro. Mở Trình soạn thảo Visual Basic bằng nút cùng tên trên tab Nhà phát triển (Nhà phát triển) hoặc phím tắt Khác+F11. Trong cửa sổ xuất hiện, chèn một mô-đun mới thông qua menu Chèn - Mô-đun và sao chép mã sau vào đó:
Sub Split_By_Rows() Dim cell As Range, n As Integer Set cell = ActiveCell For i = 1 To Selection.Rows.Count ar = Split(cell, Chr(10)) 'xác định số đoạn cell.Offset(1, 0 ).Resize(n, 1).EntireRow.Insert 'chèn các hàng trống bên dưới ô.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar) 'nhập dữ liệu từ mảng vào chúng Set cell = cell.Offset(n + 1, 0)'chuyển sang ô tiếp theo Next i End Sub
Quay lại Excel và chọn các ô có văn bản nhiều dòng mà bạn muốn tách. Sau đó sử dụng nút Macros chuyển hướng nhà phát triển (Nhà phát triển - Macro) hoặc phím tắt Khác+F8để chạy macro đã tạo, macro này sẽ thực hiện tất cả công việc cho bạn:
Thì đấy! Trên thực tế, các lập trình viên chỉ là những người rất lười biếng, thà làm việc chăm chỉ một lần rồi không làm gì 🙂
- Dọn dẹp văn bản khỏi rác và ký tự thừa
- Thay thế văn bản và loại bỏ các khoảng trắng không ngắt bằng hàm SUBSTITUTE
- Cách tách văn bản dính thành nhiều phần trong Excel