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

Nội dung được viết bởi Bến Hà Trương

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. 

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

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ế, loại bỏ, thêm, phân tách ô...  bằng dấu phẩy trong Excel

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.

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

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:

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

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:

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

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 .

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

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

1
2
3
4
5
6
Sub ReplaceComma()
Dim rngCell As Range
  For Each rngCell In Selection
    rngCell.Value = Replace(rngCell, ",", vbLf)
  Next
End Sub

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

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:

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

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.

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

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.

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

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:

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

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:

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

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.

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

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 .

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

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:

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

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.

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

Thông thường, tính năng Text to column chỉ chia ô thành các cột riêng biệt, nếu bạn muốn tách ô thành các hàng thì phải làm thế nào? Hãy xêm thêm cách bên dưới


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 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.

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

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.

Hiện gói hội viên đang được ưu đãi giảm 200K. Chỉ với 182K/ tháng - Học hơn 500 khóa bất kỳ tại Gitiho! Nhấn vào banner bên dưới để tìm hiểu thêm!

0/5 - (0 bình chọn)

0/5 - (0 bình chọn)

0 thảo luận

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội
Giấy phép mạng xã hội số: 588, cấp bởi Bộ thông tin và truyền thông