Cách thay thế, loại bỏ, thêm, phân tách ô... bằng dấu phẩy trong Excel

Trong bài viết này, chúng ta cùng tìm hiểu một số thủ thuật hữu ích để làm việc với dấu phẩy trong ô Excel, bao gồm tìm kiếm, thay thế, thêm và phân tách nội dung thành các ô và hàng riêng biệt trong Excel.

Cách thay thế dấu phẩy cuối cùng bằng chữ Và trong các ô Excel

Giả sử có một ô chứa nội dung “Thành viên nhóm dự án bao gồm Nana, Linda, Natalia, Jim”, bạn làm thế nào để thay thế dấu phẩy cuối cùng bằng chữa “và” để thay đổi nội dung ô thành “Thành viên nhóm dự án bao gồm Nana, Linda, Natalia và Jim” ?

Công thức sau có thể giúp bạn thay thế dấu phẩy cuối cùng bằng và trong các ô. Hãy làm như sau.

1. Chọn một ô trống để đặt kết quả. Sau đó nhập công thức

= SUBSTITUTE (A2, ",", "và", LEN (A2) -LEN (SUBSTITUTE (A2, ",", "")))

vào Thanh công thức rồi nhấn phím  Enter. 

Lưu ý : Trong công thức trên, A2 là ô chứa nội dung bạn cần thay thế dấu phẩy cuối cùng. Vui lòng thay đổi nó khi bạn cần.

2. Tiếp tục chọn ô kết quả, sau đó kéo công thức xuống các ô bên dưới.

Bạn có thể thấy tất cả dấu phẩy cuối cùng trong các ô được chỉ định được thay thế bằng từ “và” như hình minh họa bên dưới.

Cách thay thế dấu phẩy bằng dòng mới (Alt + Enter) trong các ô

Như ảnh chụp màn hình bên trái được hiển thị, làm thế nào để thay thế dấu phẩy bằng dòng mới trong các ô trong Excel? Các phương pháp bên dưới có thể giúp bạn.

Thay dấu phẩy bằng dòng mới với chức năng tìm và thay thế

Bạn có thể áp dụng chức năng Tìm và Thay thế để thay thế tất cả dấu phẩy trong các ô đã chọn bằng các dòng mới. Hãy làm như sau.

1. Chọn các ô bạn sẽ thay thế tất cả các dấu phẩy bằng các dòng mới. Sau đó nhấp vào Home > Find & Select > Replace. Xem ảnh chụp màn hình:

Lưu ý: Bạn cũng có thể nhấn tổ hợp phím Ctrl + H để mở hộp thoại Find and Replace.

2. Trong hộp thoại  Find and Replace đang mở và trong tab Replace, bạn cần:

2.1 Nhập dấu phẩy vào ô Find what;

2.2 Nhấp vào ô Replace with, sau đó nhấn đồng thời các phím Ctrl + Shift + J ;

2.3 Nhấp vào nút Replace All. Xem ảnh chụp màn hình:

3. Sau đó, một hộp nhắc nhở Microsoft Excel bật lên, vui lòng nhấp vào nút OK .

4. Đóng hộp thoại Find and Replace .

Bây giờ tất cả các dấu phẩy được thay thế bằng các dòng mới trong các ô đã chọn.

Thay thế toàn bộ dấu phẩy với code VBA

Phần này sẽ giới thiệu mã VBA để thay thế tất cả dấu phẩy trong các ô đã chọn bằng các dòng mới. Vui lòng làm như sau từng bước.

1. Chọn các ô chứa dấu phẩy bạn cần thay thế bằng các dòng mới, sau đó nhấn đồng thời các phím Alt + F11 để mở cửa sổ Microsoft Visual Basic for Applications .

2. Trong cửa sổ Microsoft Visual Basic for Applications , nhấp vào  Insert > Module. Sau đó sao chép và dán mã VBA vào cửa sổ Mã. Xem ảnh chụp màn hình:

Mã VBA: Thay thế tất cả dấu phẩy trong các ô đã chọn bằng các dòng mới

3. Nhấn phím F5 hoặc nhấp vào nút Run để chạy mã. Sau đó, tất cả các dấu phẩy trong các ô đã chọn sẽ được thay thế bằng các dòng mới ngay lập tức.

Cách thêm dấu phẩy vào cuối ô, cuối văn bản trong Excel

Thêm dấu phẩy vào cuối ô, văn bản bằng công thức

Bạn có thể sử dụng công thức để thêm dấu phẩy vào cuối ô trong Excel. Hãy làm như sau.

Trước tiên bạn cần tạo một cột trợ giúp để sử dụng công thức này.

1. Chọn một ô trống như ô B1 đó là tiếp giáp với các tế bào mà bạn muốn thêm dấu phẩy ở cuối, nhập công thức = A1 & "" , và sau đó nhấn Enter key. Xem ảnh chụp màn hình:

2. Chọn ô B1, kéo công thức xuống phạm vi bạn cần. Bạn sẽ thấy dấu phẩy được thêm vào cuối văn bản trong mỗi ô trong cột trợ giúp.

Ghi chú :

• Bạn có thể thay đổi ô tham chiếu trong công thức dựa trên nhu cầu của mình.
• Khi bạn sao chép công thức sang các ô khác, vui lòng dán chúng dưới dạng giá trị.

Cách loại bỏ tất cả dấu phẩy trong Excel

Để loại bỏ tất cả dấy phẩy trong Excel, vui lòng làm theo các bước sau:

1. Chọn các ô bạn muốn xóa tất cả các dấu phẩy.

2. Nhấp vào Home > Find & Select > Replace. Bạn cũng có thể nhấn tổ hợp phím Ctrl + H để mở hộp thoại Find and Replace.

3. Trong hộp thoại Tìm và Thay thế , nhấp vào tab Replace, nhập dấu phẩy “ , ” vào hộp Find what, giữ trống hộp Replace with và cuối cùng nhấp vào nút Replace All. Xem ảnh chụp màn hình:

Sau đó, tất cả các dấu phẩy trong phạm vi đã chọn này sẽ bị xóa ngay lập tức.

Cách chia các giá trị được phân tách bởi dấu phẩy thành hàng và cột trong Excel

Trong một số trường hợp, bạn có thể cần chia các giá trị được phân tách bằng dấu phẩy của các ô đã chọn thành các hàng hoặc cột. Sau đây chúng tôi sẽ giới thiệu ba phương pháp để bạn giải quyết vấn đề này một cách chi tiết.

Sử dụng chức năng Text To Columns chia các giá trị được phân tách bằng dấu phẩy thành các hàng và cột trong Excel

Bạn có thể áp dụng chức năng Văn bản thành Cột để chia các giá trị được phân tách bằng dấu phẩy của các ô đã chọn thành các cột. Hãy làm như sau.

1. Chọn phạm vi ô bạn muốn tách giá trị thành các cột, sau đó nhấp vào Data > Text to Columns . Xem ảnh chụp màn hình:

2. Trong hộp thoại  Convert Text to Columns Wizard , hãy chọn tùy chọn Delimited, sau đó nhấp vào nút Next.

3. Trong hộp thoại Convert Text to Columns Wizard thứ hai, hãy chọn hộp Comma trong phần Delimiters và nhấp vào nút Next .

4. Trong hộp thoại  Convert Text to Columns Wizard cuối cùng , hãy chọn một ô để định vị các giá trị tách trong hộp Destination, và cuối cùng nhấp vào nút Finish. Xem ảnh chụp màn hình:

Bây giờ tất cả các giá trị trong các ô đã chọn được phân tách bằng dấu phẩy sẽ được chia thành các cột khác nhau như ảnh chụp màn hình dưới đây.

Sử dụng code VBA chia các giá trị được phân tách bằng dấu phẩy thành các hàng và cột trong Excel

Để tách các giá trị được phân tách bằng dấu phẩy thành các hàng, bạn có thể áp dụng mã VBA sau.

1. Nhấn đồng thời các phím Alt + F11 để mở cửa sổ Microsoft Visual Basic for Applications .

2. Trong cửa sổ Microsoft Visual Basic for Applications , nhấp vào  Insert > Module. Sau đó sao chép và dán mã VBA bên dưới vào cửa sổ Module.

Mã VBA: Chia các giá trị được phân tách bằng dấu phẩy thành các hàng

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

Sub SplitAll()     Dim xRg As Range     Dim xRg1 As Range     Dim xCell As Range     Dim I As Long     Dim xAddress As String     Dim xUpdate As Boolean     Dim xRet As Variant     On Error Resume Next     xAddress = Application.ActiveWindow.RangeSelection.Address     Set xRg  = Application.InputBox("Please select a range", "Kutools for Excel", xAddress, , , , , 8)     Set xRg  = Application.Intersect(xRg, xRg.Worksheet.UsedRange)     If xRg Is Nothing Then Exit Sub         If xRg.Columns.Count > 1 Then             MsgBox "You can't select multiple columns", , "Kutools for Excel"             Exit Sub             End If             Set xRg1 = Application.InputBox("Split to (single cell):", "Kutools for Excel", , , , , , 8)             Set xRg1 = xRg1.Range("A1")             If xRg1 Is Nothing Then Exit Sub                 xUpdate = Application.ScreenUpdating                 Application.ScreenUpdating = False                 For Each xCell In xRg                     xRet = Split(xCell.Value, ",")                     xRg1.Worksheet.Range(xRg1.Offset(I, 0), xRg1.Offset(I + UBound(xRet, 1), 0)) = Application.WorksheetFunction.Transpose(xRet)                     I = I + UBound(xRet, 1) + 1                 Next                 Application.ScreenUpdating = xUpdate             End Sub

3. Nhấn phím F5 để chạy mã. Trong hộp thoại bật lên , hãy chọn các ô bạn muốn tách, sau đó nhấp vào nút OK .

4. Hộp thoại thứ hai xuất hiện, bạn hãy chọn một ô để định vị. Sau đó bấm OK.

Sau đó, bạn có thể thấy các giá trị được phân tách bằng dấu phẩy trong các ô đã chọn được chia thành các hàng như ảnh chụp màn hình dưới đây.

Hy vọng với bài viết này, bạn đã biết thêm một số thủ thuật hữu ích để  thêm, loại bỏ dấu phẩy, thay thế dấu phẩy bằng dòng mới và biết cách chia các giá trị được phân tách bằng dấu phẩy thành các hàng và cột trong Excel.

Bên cạnh đó, để không bỏ lỡ những mẹo và thủ thuật tin học văn phòng hữu ích khác. Hãy tham gian cộng đồng Giỏi tin học ngay hôm nay.