Bỏ qua nội dung chính
 

Excel loại bỏ các ký tự, từ, số khỏi chuỗi văn bản

Giả sử, bạn có một danh sách dài các chuỗi văn bản chứa các ký tự, số hoặc các ký hiệu cụ thể khác. Trong một số trường hợp nhất định, bạn có thể cần xóa một số ký tự dựa trên vị trí, chẳng hạn như từ phải, trái hoặc giữa khỏi chuỗi văn bản hoặc xóa một số ký tự không mong muốn, số khỏi danh sách chuỗi. Tìm từng giải pháp một sẽ khiến bạn đau đầu, hướng dẫn này thu thập tất cả các loại phương pháp xóa ký tự, từ hoặc số trong Excel.

Mục lục:

1. Xóa các ký tự từ trái, phải hoặc giữa các chuỗi văn bản

2. Xóa các ký tự không mong muốn / đặc biệt khỏi chuỗi văn bản

3. Xóa các ký tự / văn bản trước hoặc sau ký tự cụ thể

4. Xóa các từ khỏi chuỗi văn bản

Xóa các ký tự từ trái, phải hoặc giữa các chuỗi văn bản

Hầu hết chúng ta có thể là một công việc phổ biến khi xóa một số ký tự từ trái, phải hoặc giữa các chuỗi văn bản trong trang tính Excel. Phần này sẽ giới thiệu một số thủ thuật nhanh chóng và dễ dàng để giải quyết công việc này.

1.1 Xóa n ký tự đầu tiên khỏi chuỗi văn bản

Nếu bạn cần xóa n ký tự đầu tiên khỏi danh sách chuỗi văn bản, các phương pháp sau có thể giúp bạn.

 Bằng cách sử dụng công thức

Thông thường, để xóa các ký tự khỏi đầu chuỗi văn bản, bạn có thể sử dụng hàm REPLACE hoặc kết hợp các hàm RIGHT và LEN.

Hàm REPLACE để xóa N ký tự đầu tiên:

=REPLACE(string, 1, num_chars, "")
  • chuỗi: Chuỗi văn bản mà bạn muốn xóa các ký tự;
  • số_chars: Số ký tự bạn muốn xóa.

Ví dụ: để xóa 2 ký tự đầu tiên khỏi các ô, vui lòng sử dụng công thức bên dưới, sau đó kéo chốt điền để sao chép công thức sang các ô khác, xem ảnh chụp màn hình:

=REPLACE(A4, 1, 2, "")

Hàm RIGHT và LEN để loại bỏ N ký tự đầu tiên:

=RIGHT(string, LEN(string) - num_chars)
  • chuỗi: Chuỗi văn bản mà bạn muốn xóa các ký tự;
  • số_chars: Số ký tự bạn muốn xóa.

Để xóa 2 ký tự đầu tiên khỏi ô, vui lòng áp dụng công thức sau:

=RIGHT(A4,LEN(A4)-2)

 Bằng cách sử dụng chức năng do người dùng xác định

Để xóa n ký tự đầu tiên khỏi ô, bạn cũng có thể tạo một Hàm do người dùng xác định để giải quyết công việc này. Vui lòng thực hiện theo các bước sau:

1. Giữ Alt + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Cửa sổ mô-đun.

Mã VBA: Xóa n ký tự đầu tiên khỏi chuỗi văn bản

Function removeFirstx(rng As String, cnt As Long)
'Updateby Extendoffice
removeFirstx = Right(rng, Len(rng) - cnt)
End Function

3. Sau đó, quay lại trang tính, rồi nhập công thức này: = removefirstx (A4,2) vào một ô trống, sau đó kéo chốt điền xuống để nhận kết quả như bạn cần, xem ảnh chụp màn hình:

Chú thích: Trong công thức này: A4 là ô mà bạn muốn xóa các ký tự; Con số 2 cho biết số ký tự bạn muốn xóa khỏi đầu chuỗi văn bản.

1.2 Xóa n ký tự cuối cùng khỏi chuỗi văn bản

Để xóa số ký tự cụ thể khỏi phía bên phải của chuỗi văn bản, bạn cũng có thể sử dụng công thức hoặc Hàm do người dùng xác định.

 Bằng cách sử dụng công thức

Để xóa n ký tự cuối cùng khỏi chuỗi văn bản, bạn có thể sử dụng công thức dựa trên hàm LEFT và LEN.

Hàm LEFT và LEN để loại bỏ N ký tự cuối cùng:

=LEFT(string, LEN(string) - num_chars)
  • chuỗi: Chuỗi văn bản mà bạn muốn xóa các ký tự;
  • số_chars: Số ký tự bạn muốn xóa.

Để xóa 3 ký tự khỏi cuối chuỗi văn bản, vui lòng sử dụng công thức này, sau đó kéo chốt điền để sao chép công thức sang các ô khác, xem ảnh chụp màn hình:

=LEFT(A4, LEN(A4) - 3)

 Bằng cách sử dụng chức năng do người dùng xác định

Tại đây, một Hàm do Người dùng Xác định cũng có thể giúp bạn xóa n ký tự cuối cùng khỏi danh sách các ô, vui lòng thực hiện như sau:

1. Giữ Alt + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Cửa sổ mô-đun.

Mã VBA: Xóa n ký tự cuối cùng khỏi chuỗi văn bản

Function removeLastx(rng As String, cnt As Long)
'Updateby Extendoffice
removeLastx = Left(rng, Len(rng) - cnt)
End Function

3. Sau đó, quay lại trang tính và nhập công thức này: = removelastx (A4,3) vào một ô trống, sau đó kéo chốt điền xuống để nhận kết quả như bạn cần, xem ảnh chụp màn hình:

Chú thích: Trong công thức này: A4 là ô mà bạn muốn xóa các ký tự; Con số 3 cho biết số ký tự bạn muốn xóa khỏi phần cuối của chuỗi văn bản.

1.3 Xóa n ký tự đầu tiên, cuối cùng hoặc các ký tự vị trí nhất định bằng cách sử dụng một tính năng mạnh mẽ

Bạn có thể gặp khó khăn khi nhớ nhiều công thức khác nhau, xóa các ký tự từ trái, phải hoặc một vị trí cụ thể của chuỗi văn bản, Kutools cho Excel hỗ trợ một tính năng mạnh mẽ - Xóa theo vị trí. Với công cụ nhỏ gọn này, bạn có thể xử lý các tác vụ này với một vài cú nhấp chuột mà không cần nhớ bất kỳ công thức nào.

Sau khi cài đặt Kutools cho Excel, hãy làm như sau:

1. Chọn các ô bạn muốn xóa ký tự, sau đó bấm Kutools > bản văn > Xóa theo vị trí, xem ảnh chụp màn hình:

2. Trong Xóa theo vị trí hộp thoại, vui lòng thực hiện các thao tác sau:

2.1 Xóa n ký tự đầu tiên khỏi các ô:

  • In Số hộp văn bản, nhập số ký tự bạn muốn xóa khỏi chuỗi. Trong ví dụ này, tôi sẽ loại bỏ 2 ký tự đầu tiên.
  • Chọn Từ trái tùy chọn trong Chức vụ phần.
  • Sau đó nhấn vào Ok or Đăng Nhập để nhận được kết quả như ảnh chụp màn hình bên dưới.

2.2 Xóa n ký tự cuối cùng khỏi các ô:

  • In Số hộp văn bản, nhập số ký tự bạn muốn xóa khỏi chuỗi. Trong ví dụ này, tôi sẽ loại bỏ 3 ký tự cuối cùng.
  • Chọn Từ bên phải tùy chọn trong Chức vụ phần.
  • Sau đó nhấn vào Ok or Đăng Nhập để nhận được kết quả như ảnh chụp màn hình bên dưới.

2.3 Xóa n ký tự khỏi vị trí cụ thể của ô:

Ví dụ: nếu bạn cần xóa một số ký tự cụ thể khỏi một vị trí nhất định của chuỗi văn bản, hãy xóa 3 ký tự bắt đầu từ ký tự thứ ba của chuỗi.

  • In Số hộp văn bản, nhập số ký tự bạn muốn xóa khỏi chuỗi. Trong ví dụ này, tôi sẽ xóa 3 ký tự khỏi một vị trí nhất định.
  • Chọn Chỉ định và nhập số mà bạn muốn xóa các ký tự bắt đầu từ trong hộp văn bản của Chức vụ tiết diện. Ở đây, tôi sẽ loại bỏ các ký tự từ ký tự thứ ba.
  • Sau đó nhấn vào Ok or Đăng Nhập để nhận được kết quả như ảnh chụp màn hình bên dưới.

Tải xuống và dùng thử miễn phí Kutools cho Excel ngay!

1.4 Loại bỏ cả n ký tự đầu tiên và n ký tự cuối cùng khỏi chuỗi văn bản bằng công thức

Khi bạn cần xóa một số ký tự trên cả hai mặt của chuỗi văn bản trong Excel, bạn có thể kết hợp các hàm MID và LEN để tạo công thức xử lý tác vụ này.

=MID(string, left_chars + 1, LEN(string) - (left_chars + right_chars)
  • chuỗi: Chuỗi văn bản mà bạn muốn xóa các ký tự;
  • trái_chars: Số ký tự cần xóa từ bên trái;
  • đúng_chars: Số ký tự cần xóa từ bên phải.

Ví dụ: bạn cần xóa đồng thời 7 ký tự đầu tiên và 5 ký tự cuối cùng khỏi chuỗi văn bản, vui lòng nhập công thức sau vào ô trống:

=MID(A4, 7+1, LEN(A4) - (7+5))

Chú thích: Trong công thức này: A4 là ô mà bạn muốn xóa các ký tự; Con số 7 là số ký tự bạn muốn xóa ở phía bên trái; Con số 5 là số ký tự bạn muốn xóa ở phía bên phải.

Và sau đó, kéo chốt điền xuống nơi bạn muốn áp dụng công thức này và bạn sẽ nhận được kết quả như hình minh họa bên dưới:

Xóa các ký tự không mong muốn / đặc biệt khỏi chuỗi văn bản

Khi nhập dữ liệu từ một nơi khác vào Excel, nhiều ký tự đặc biệt hoặc không mong muốn có thể được dán vào trang tính của bạn. Để loại bỏ các ký tự không mong muốn của luận văn như # @ $% ^ &, dấu cách, số, số không phải số, ngắt dòng, v.v., phần này sẽ cung cấp một số phương pháp hữu ích để giúp bạn.

2.1 Xóa một số ký tự đặc biệt khỏi chuỗi văn bản

Nếu có một số ký tự đặc biệt như% ^ & * () trong chuỗi văn bản, để loại bỏ loại ký tự này, bạn có thể áp dụng ba thủ thuật dưới đây.

 Xóa một số ký tự đặc biệt khỏi chuỗi văn bản bằng hàm SUBSTITUTE

Thông thường, trong Excel, bạn có thể lồng một số hàm SUBSTITUTE để thay thế từng ký tự cụ thể bằng không, cú pháp chung là:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(string_cell, char1, ""), char2, ""), char3, "")
  • ô chuỗi: Ô chứa chuỗi văn bản mà bạn muốn xóa các ký tự đặc biệt khỏi;
  • ký tự1, ký tự2, ký tự3: Các ký tự không mong muốn bạn muốn loại bỏ.

Bây giờ, vui lòng sao chép hoặc nhập công thức dưới đây vào một ô trống:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "#", ""), "&", ""), "*", ""), "%", "")

Và sau đó, kéo chốt điền xuống các ô mà bạn muốn áp dụng công thức này và tất cả các ký tự không mong muốn mà bạn đã chỉ định sẽ bị xóa ngay lập tức, xem ảnh chụp màn hình:

Lời khuyên: Nếu có nhiều ký tự muốn loại bỏ, bạn chỉ cần lồng thêm các hàm SUBSTITUTE vào bên trong công thức.

 Xóa nhiều ký tự đặc biệt khỏi chuỗi văn bản bằng Chức năng do Người dùng Xác định

Các hàm SUBSTITUTE lồng nhau ở trên hoạt động tốt nếu có ít ký tự đặc biệt cần xóa, nhưng nếu bạn có hàng chục ký tự cần xóa, công thức sẽ trở nên quá dài và khó quản lý. Trong trường hợp này, Hàm do Người dùng Xác định sau đây có thể giúp bạn hoàn thành tác vụ này một cách nhanh chóng và dễ dàng.

1. Giữ Alt + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Cửa sổ mô-đun.

Mã VBA: Xóa nhiều ký tự đặc biệt khỏi chuỗi văn bản

Function RemoveUnwantedChars(Str As String, xchars As String)
'Updateby Extendoffice
    For Index = 1 To Len(xchars)
        Str = Replace(Str, Mid(xchars, Index, 1), "")
    Next
    RemoveUnwantedChars = Str
End Function

3. Sau đó, đóng cửa sổ mã và quay lại trang tính, nhập công thức này = RemoveUnwainedChars (A2, $ D $ 2) vào một ô trống nơi xuất kết quả, sau đó kéo chốt điền xuống để nhận kết quả như bạn cần, xem ảnh chụp màn hình:

Chú thích: Trong công thức trên: A2 là ô mà bạn muốn xóa các ký tự; $ D $ 2 chứa các ký tự đặc biệt mà bạn muốn xóa (bạn có thể nhập bất kỳ ký tự đặc biệt nào khác mà bạn cần).

 Loại bỏ nhiều ký tự đặc biệt khỏi chuỗi văn bản với một tính năng tuyệt vời

Nếu bạn đã cài đặt Kutools cho Excel, Với khả Xóa ký tự tính năng, bạn có thể xóa tất cả các loại ký tự, chẳng hạn như ký tự số, ký tự chữ cái, ký tự không in được… khỏi danh sách các ô khi bạn cần.

Sau khi cài đặt Kutools cho Excel, hãy làm như sau:

1. Chọn phạm vi ô bạn muốn xóa các ký tự đặc biệt, sau đó bấm Kutools > bản văn > Xóa ký tự, xem ảnh chụp màn hình:

2. Trong Xóa ký tự hộp thoại:

  • kiểm tra Tuỳ chỉnh tùy chọn theo Xóa ký tự phần.
  • Và sau đó nhập các ký tự đặc biệt vào hộp văn bản mà bạn muốn xóa.
  • Sau đó nhấn vào Ok or Đăng Nhập để xóa các ký tự bạn đã chỉ định cùng một lúc. Xem ảnh chụp màn hình:

Tải xuống và dùng thử miễn phí Kutools cho Excel ngay!

2.2 Xóa tất cả các số khỏi chuỗi văn bản

Nếu bạn có một danh sách các chuỗi văn bản kết hợp với số, chữ cái và ký tự đặc biệt và bây giờ, bạn chỉ muốn xóa tất cả các số và giữ lại các ký tự khác. Phần này sẽ cung cấp một số cách hữu ích để giúp bạn.

 Xóa số khỏi chuỗi văn bản bằng hàm SUBSTITUTE

Trong Excel, hàm SUBSTITUTE lồng nhau có thể giúp thay thế tất cả các số bằng không, vì vậy, bạn có thể sử dụng công thức dưới đây để xóa tất cả các số khỏi ô:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")

Sau đó kéo chốt điền xuống các ô mà bạn muốn áp dụng công thức này và tất cả các số sẽ bị xóa khỏi danh sách các chuỗi văn bản, xem ảnh chụp màn hình:

 Xóa số khỏi chuỗi văn bản bằng hàm TEXTJOIN

Nếu bạn có Excel 2019, 2021 hoặc 365, thì hàm TEXTJOIN mới cũng có thể giúp xóa số khỏi chuỗi văn bản.

Vui lòng sao chép công thức sau vào một ô trống, sau đó nhấn tổ hợp phím Ctrl + Shift + Enter để nhận kết quả đầu tiên:

=TEXTJOIN("", TRUE, IF(ISERR(MID(A2, ROW(INDIRECT( "1:"&LEN(A2) )), 1) *1), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), ""))

Và sau đó sao chép công thức vào các ô khác bên dưới nơi bạn muốn áp dụng công thức này, xem ảnh chụp màn hình:

Chú thích: TEXTJOIN này chỉ khả dụng trong Excel 2019, 2021 và Office 365.

 Xóa số khỏi chuỗi văn bản bằng Hàm do người dùng xác định

Ngoài hai công thức trên, một Hàm do Người dùng Xác định cũng có thể giúp bạn, vui lòng thực hiện theo các bước sau:

1. Giữ Alt + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Cửa sổ mô-đun.

Mã VBA: Xóa số khỏi chuỗi văn bản

Function RemoveNumbers(Txt As String) As String
'Updateby Extendoffice
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
RemoveNumbers = .Replace(Txt, "")
End With
End Function

3. Sau đó, đóng và thoát khỏi cửa sổ mã, quay lại trang tính và nhập công thức này: = RemoveNumbers (A2) vào một ô trống, sau đó kéo chốt điền xuống các ô mà bạn muốn áp dụng công thức này, xem ảnh chụp màn hình:

 Xóa số khỏi chuỗi văn bản với một tùy chọn tiện dụng

Nếu bạn cảm thấy mệt mỏi với những công thức phức tạp, bây giờ, hãy để tôi chỉ cho bạn một công cụ dễ dàng - Kutools cho Excel'S Xóa ký tự. Với tính năng tiện dụng này, bạn có thể hoàn thành công việc này chỉ với một vài cú nhấp chuột.

Sau khi cài đặt Kutools cho Excel, hãy làm như sau:

1. Chọn phạm vi ô mà bạn muốn xóa số, sau đó bấm Kutools > bản văn > Xóa ký tự.

2. Trong Xóa ký tự hộp thoại, vui lòng thực hiện các thao tác sau:

  • kiểm tra Numeric tùy chọn theo Xóa ký tự phần.
  • Sau đó nhấn vào Ok or Đăng Nhập để xóa các số ngay lập tức. Xem ảnh chụp màn hình:

Tải xuống và dùng thử miễn phí Kutools cho Excel ngay!

2.3 Xóa các ký tự không phải số khỏi chuỗi văn bản

Để loại bỏ tất cả các ký tự không phải số và chỉ giữ các số khỏi chuỗi văn bản, phần này sẽ nói về một số cách để giải quyết tác vụ này trong Excel.

 Xóa các ký tự không phải số khỏi chuỗi văn bản bằng công thức trong Excel 2016 và các phiên bản trước đó

Nếu bạn đang sử dụng Excel 2016 hoặc các phiên bản cũ hơn, bạn nên áp dụng một công thức phức tạp để đạt được công việc này, vui lòng sao chép hoặc nhập công thức dưới đây vào một ô trống:

=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)

Và sau đó sao chép công thức vào các ô khác bên dưới nơi bạn muốn áp dụng công thức này, xem ảnh chụp màn hình:

Chú thích: Nếu các số trong chuỗi văn bản bắt đầu bằng 0, thì số 0 sẽ bị mất.

 Xóa các ký tự không phải số khỏi chuỗi văn bản bằng hàm TEXTJOIN trong Excel 2019, 2021, 365

Công thức trên có thể quá khó hiểu đối với hầu hết chúng ta. Nếu bạn có Excel 2019, 2021 hoặc 365, có một công thức gọn gàng có thể giúp bạn.

Vui lòng sao chép hoặc nhập công thức sau vào một ô trống và nhấn Ctrl + Shift + Enter các phím với nhau để nhận được kết quả chính xác đầu tiên:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))

Sau đó, kéo chốt điền xuống các ô mà bạn sẽ áp dụng công thức này và bạn sẽ nhận được kết quả như hình minh họa bên dưới:

Chú thích: Với công thức này, bạn có thể thấy các số 0 đứng đầu sẽ được giữ lại vì các số được trả về dưới dạng văn bản.

 Xóa các ký tự không phải số khỏi chuỗi văn bản bằng Hàm do Người dùng Xác định

Tất nhiên, bạn cũng có thể tạo Hàm do người dùng xác định của riêng mình với cú pháp đơn giản hơn, vui lòng thực hiện như sau:

1. Giữ Alt + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Cửa sổ mô-đun.

Mã VBA: Xóa các ký tự không phải số khỏi chuỗi văn bản

Function Removenonnumeric(str As String) As String
'Updateby Extendoffice
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[^0-9]"
        Removenonnumeric = .Replace(str, "")
    End With
End Function

3. Sau đó, đóng và thoát khỏi cửa sổ mã, quay lại trang tính và nhập công thức này: = Removenonnumeric (A2) vào một ô trống, sau đó kéo chốt điền xuống các ô mà bạn muốn áp dụng công thức này, chỉ các số sẽ được trích xuất như hình minh họa bên dưới:

 Xóa các ký tự không phải số khỏi chuỗi văn bản bằng một tính năng đơn giản

Để xóa trực tiếp các ký tự không phải số trong một dải ô, Kutools cho Excel'S Xóa ký tự tiện ích có thể hoàn thành công việc chỉ với vài cú nhấp chuột.

Sau khi cài đặt Kutools cho Excel, hãy làm như sau:

1. Chọn phạm vi ô mà bạn muốn xóa các ký tự không phải số, sau đó bấm Kutools > bản văn > Xóa ký tự.

2. Trong Xóa ký tự hộp thoại, vui lòng thiết lập các hoạt động sau:

  • kiểm tra Không phải số tùy chọn theo Xóa ký tự phần.
  • Sau đó nhấn vào Ok or Đăng Nhập để xóa tất cả các ký tự không phải số ngay lập tức. Xem ảnh chụp màn hình:

Tải xuống và dùng thử miễn phí Kutools cho Excel ngay!

2.4 Tách văn bản và số từ một ô thành hai cột

Đôi khi, bạn có thể muốn trích xuất văn bản và số từ chuỗi văn bản thành hai cột tách biệt, với sự trợ giúp của các phương pháp sau, bạn có thể hoàn thành công việc này một cách nhanh chóng và dễ dàng.

 Tách văn bản và số từ một ô thành hai cột bằng chức năng do người dùng xác định

Bằng cách sử dụng Hàm do Người dùng Xác định sau, bạn có thể trích xuất văn bản và số cùng một lúc, vui lòng thực hiện theo các bước dưới đây:

1. Giữ Alt + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Cửa sổ mô-đun.

Mã VBA: Tách văn bản và số khỏi chuỗi văn bản thành hai cột

Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby Extendoffice
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

3. Sau đó, đóng và thoát khỏi cửa sổ mã, quay lại trang tính và nhập công thức này: = SplitText (A2, FALSE) vào một ô trống, sau đó kéo chốt điền xuống các ô mà bạn muốn điền vào công thức này để lấy tất cả văn bản, xem ảnh chụp màn hình:

4. Và sau đó, tiếp tục nhập công thức này: = SplitText (A2, TRUE) vào một ô khác và kéo chốt điền xuống các ô mà bạn muốn điền vào công thức này để lấy số, xem ảnh chụp màn hình:

 Tách văn bản và số từ một ô thành hai cột bằng một tính năng dễ dàng

Nếu bạn có Kutools cho ExcelCủa nó, Chia ô tiện ích có thể giúp bạn chia ô thành nhiều cột hoặc hàng dựa trên bất kỳ dấu phân cách nào, chiều rộng hoặc văn bản và số được chỉ định.

Sau khi cài đặt Kutools cho Excel, hãy làm như sau:

1. Chọn phạm vi ô bạn muốn tách, sau đó bấm Kutools > Hợp nhất & Tách > Chia ô, xem ảnh chụp màn hình:

2. Trong Chia ô hộp thoại, chọn Chia thành các cột tùy chọn theo Kiểu và sau đó kiểm tra Văn bản và số từ Chia theo , xem ảnh chụp màn hình:

3. Sau đó nhấn vào Ok và một nút khác Chia ô hộp thoại sẽ xuất hiện, hãy chọn một ô để xuất văn bản và số được phân tách, sau đó bấm vào OK cái nút. Bây giờ, bạn có thể thấy văn bản và số trong các ô đã chọn được chia thành hai cột cùng một lúc như hình minh họa bên dưới:

Tải xuống và dùng thử miễn phí Kutools cho Excel ngay!

2.5 Xóa các ký tự ngắt dòng khỏi chuỗi văn bản

Dấu ngắt dòng là thứ cho phép bạn có nhiều dòng trong cùng một ô trong Excel. Đôi khi, khi bạn sao chép dữ liệu từ trang web hoặc tách nội dung ô bằng Alt + Enter các phím theo cách thủ công, bạn sẽ nhận được dấu ngắt dòng hoặc dấu xuống dòng. Trong một số trường hợp nhất định, bạn có thể muốn xóa các dấu ngắt dòng để làm cho nội dung ô thành một dòng như hình minh họa bên dưới. Ở đây, tôi sẽ giới thiệu một số cách để giải quyết công việc này trong Excel.

 Xóa các ký tự ngắt dòng khỏi chuỗi văn bản bằng tính năng Tìm và Thay thế

Trong Excel, bạn có thể sử dụng Tìm và Thay thế tính năng xóa ngắt dòng, vui lòng làm như sau:

1. Chọn phạm vi dữ liệu mà bạn muốn xóa các dấu ngắt dòng.

2. Sau đó nhấn vào Trang Chủ > Tìm & Chọn > Thay thế (hoặc nhấn Ctrl + H phím) để đi Tìm và Thay thế hộp thoại, xem ảnh chụp màn hình:

3. Trong cửa sổ bật ra Tìm và Thay thế hộp thoại, vui lòng thực hiện các thao tác sau:

  • Đặt con trỏ vào Tìm trường và nhấn Ctrl + J trên bàn phím, bạn có thể không nhìn thấy gì, nhưng ký tự ngắt dòng đã được chèn.
  • Trong tạp chí Với thay thế , hãy để trống trường này để chỉ xóa các dấu ngắt dòng hoặc nhấn vào Không gian bar một lần để thay thế các ngắt dòng bằng các khoảng trống.

4. Sau đó nhấn vào Thay thế Tất cả , tất cả các ngắt dòng trong các ô đã chọn sẽ bị xóa hoặc thay thế bằng các ô trống cùng một lúc. Xem ảnh chụp màn hình:

 Xóa các ký tự ngắt dòng khỏi chuỗi văn bản bằng hàm SUBSTITUTE

Bạn cũng có thể tạo một công thức dựa trên các hàm SUBSTITUTE và CHAR để loại bỏ các dấu ngắt dòng khỏi các chuỗi văn bản.

Vui lòng áp dụng công thức dưới đây để nhận được kết quả:

=SUBSTITUTE(A2,CHAR(10),"")

Lời khuyên: Hàm SUBSTITUTE tìm và thay thế ký tự CHAR (10) đại diện cho ký tự ngắt dòng không có gì. Nếu bạn muốn kết quả được phân tách bằng dấu phẩy và dấu cách, bạn có thể sử dụng công thức dưới đây:

=SUBSTITUTE(A2,CHAR(10),", ")

 Xóa các ký tự ngắt dòng khỏi chuỗi văn bản bằng mã VBA

Nếu bạn cảm thấy thoải mái khi sử dụng mã VBA, tại đây cũng cung cấp mã cho bạn, vui lòng thực hiện theo các bước sau:

1. Giữ Alt + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Cửa sổ mô-đun.

Mã VBA: Xóa ngắt dòng khỏi chuỗi văn bản

Sub RemoveCarriage()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = Replace(Rng.Value, Chr(10), "")
Next
End Sub

3. Sau đó nhấn F5 để chạy mã này và một hộp nhắc sẽ xuất hiện, hãy chọn phạm vi mà bạn muốn xóa ngắt dòng, xem ảnh chụp màn hình:

4. Và sau đó, nhấp vào OK , tất cả các ngắt dòng sẽ bị xóa khỏi phạm vi dữ liệu đã chọn.

 Xóa các ký tự ngắt dòng khỏi chuỗi văn bản bằng một tùy chọn thông minh

Ở đây, Kutools cho Excel'S Xóa ký tự tính năng này cũng có thể giúp bạn loại bỏ các ngắt dòng một cách dễ dàng.

Sau khi cài đặt Kutools cho Excel, hãy làm như sau:

1. Chọn phạm vi ô mà bạn muốn xóa ngắt dòng, sau đó bấm Kutools > bản văn > Xóa ký tự.

2. Trong Xóa ký tự hộp thoại, vui lòng thiết lập các hoạt động sau:

  • kiểm tra Không in tùy chọn theo Xóa ký tự phần.
  • Sau đó nhấn vào Ok or Đăng Nhập để xóa tất cả các ngắt dòng khỏi phạm vi dữ liệu đã chọn. Xem ảnh chụp màn hình:

Tải xuống và dùng thử miễn phí Kutools cho Excel ngay!

2.6 Xóa khoảng trắng (đầu, cuối, thừa hoặc tất cả khoảng trắng) khỏi chuỗi văn bản

Sao chép và dán văn bản từ nguồn bên ngoài vào trang tính Excel thường mang lại một số khoảng trắng còn sót lại gây phiền nhiễu, sẽ rất tẻ nhạt nếu loại bỏ các khoảng trắng đầu, cuối hoặc các khoảng thừa khác theo cách thủ công. May mắn thay, Excel cung cấp một số thủ thuật đơn giản để giải quyết công việc này.

 Loại bỏ khoảng trắng thừa (đầu, cuối, thừa) khỏi chuỗi văn bản bằng hàm TRIM

Trong Excel, để xóa các khoảng trắng đầu, cuối và thừa khỏi chuỗi văn bản, hàm TRIM đơn giản có thể giúp bạn. Hàm này xóa tất cả các khoảng trắng ngoại trừ các khoảng trắng giữa các từ.

Vui lòng nhập công thức dưới đây vào một ô trống:

=TRIM(A2)

Và sau đó kéo chốt điền xuống để sao chép công thức cho các ô khác, bây giờ, bạn có thể thấy tất cả các khoảng trắng đầu, cuối và khoảng trắng thừa giữa các từ được xóa ngay lập tức khỏi ảnh chụp màn hình:

 Xóa tất cả các khoảng trắng khỏi chuỗi văn bản

Nếu bạn muốn xóa tất cả khoảng trắng khỏi chuỗi văn bản, hàm SUBSTITUTE và tính năng Tìm & Thay thế sau đây có thể giúp bạn.

Bằng cách sử dụng hàm SUBSTITUTE

Bạn có thể sử dụng hàm SUBSTITUTE để thay thế tất cả các khoảng trắng bằng không, vui lòng áp dụng công thức dưới đây vào một ô trống:

=SUBSTITUTE(A2," ","")

Sau đó, kéo chốt điền xuống để sao chép công thức này sang các ô khác mà bạn cần và tất cả các khoảng trắng sẽ bị xóa như hình minh họa bên dưới:

Bằng cách sử dụng tính năng Tìm và Thay thế

Trong thực tế, Tìm thay thế tính năng trong Excel cũng có thể giúp loại bỏ tất cả các khoảng trắng khỏi các ô đã chọn, vui lòng thực hiện theo các bước sau:

1. Chọn phạm vi dữ liệu mà bạn muốn xóa tất cả các khoảng trắng.

2. Sau đó nhấn vào Trang Chủ > Tìm & Chọn > Thay thế (hoặc nhấn Ctrl + H phím) để đi đến Tìm và Thay thế trong hộp thoại đã mở Tìm và Thay thế hộp thoại, vui lòng thực hiện các thao tác dưới đây:

  • Ấn Bản Thanh không gian trong Tìm cánh đồng;
  • Trong tạp chí Với thay thế trường này, để trống trường này.

3. Và sau đó, nhấp vào Thay thế Tất cả , tất cả khoảng trắng trong các ô đã chọn sẽ bị xóa cùng một lúc. Xem ảnh chụp màn hình:

 Loại bỏ tất cả các loại khoảng trắng khỏi chuỗi văn bản bằng một tính năng mạnh mẽ

Kutools cho Excel có một tính năng mạnh mẽ - Xóa dấu cách, với tiện ích này, bạn không chỉ có thể loại bỏ khoảng trắng đầu, khoảng trắng cuối, khoảng trắng thừa mà còn tất cả các khoảng trắng từ các phạm vi đã chọn trong một hộp thoại, điều này sẽ nâng cao năng suất làm việc của bạn.

Sau khi cài đặt Kutools cho Excel, hãy làm như sau:

1. Chọn phạm vi dữ liệu mà bạn sẽ xóa khoảng trắng, sau đó nhấp vào Kutools > bản văn > Xóa dấu cách. Xem ảnh chụp màn hình:

2. Trong Xóa dấu cách hộp thoại, chọn một loại khoảng trắng bạn muốn xóa khỏi Loại dấu cách:

  • Xóa các khoảng trắng ở đầu, vui lòng chọn Không gian hàng đầu Tùy chọn;
  • Xóa các dấu cách ở cuối, vui lòng chọn Dấu cách cuối Tùy chọn;
  • Xóa các khoảng trắng đầu và khoảng trắng cuối cùng một lúc, vui lòng chọn Khoảng trống hàng đầu & theo sau Tùy chọn;
  • Loại bỏ tất cả các khoảng trống thừa, vui lòng chọn Tất cả không gian thừa Tùy chọn;
  • Xóa tất cả các khoảng trắng, vui lòng chọn Tất cả các không gian tùy chọn.

3. Sau đó nhấn vào Ok or Đăng Nhập , bạn sẽ nhận được kết quả bạn cần.

Tải xuống và dùng thử miễn phí Kutools cho Excel ngay!

Xóa ký tự / văn bản trước hoặc sau ký tự cụ thể

Trong phần này, tôi sẽ giới thiệu một số thao tác xóa văn bản hoặc ký tự trước hoặc sau lần xuất hiện đầu tiên, cuối cùng hoặc thứ n của một ký tự cụ thể.

3.1 Xóa văn bản trước hoặc sau ký tự cụ thể đầu tiên

Nếu bạn muốn xóa văn bản trước hoặc sau ký tự cụ thể đầu tiên, chẳng hạn như dấu cách, dấu phẩy khỏi danh sách các chuỗi văn bản như ảnh chụp màn hình bên dưới, ở đây tôi sẽ đăng hai phương pháp cho bạn.

 Xóa văn bản trước ký tự cụ thể đầu tiên bằng công thức

Để xóa văn bản hoặc ký tự trước ký tự cụ thể đầu tiên, bạn có thể tạo công thức dựa trên các hàm RIGHT, LEN và FIND, cú pháp chung là:

=RIGHT(cell, LEN(cell)-FIND("char", cell))
  • pin: Tham chiếu ô hoặc chuỗi văn bản mà bạn muốn xóa văn bản khỏi đó;
  • xe tăng: Dấu phân tách cụ thể mà bạn muốn xóa văn bản dựa trên.

Ví dụ: để xóa mọi thứ trước dấu phẩy đầu tiên khỏi chuỗi danh sách, bạn nên áp dụng công thức sau vào một ô trống, sau đó kéo nó xuống các ô bạn cần, xem ảnh chụp màn hình:

=RIGHT(A2,LEN(A2)-FIND(",",A2))

Chú thích: Trong công thức trên: A2 là ô mà bạn muốn xóa văn bản; , là ký tự cụ thể mà bạn muốn xóa văn bản dựa trên, bạn có thể thay đổi nó thành bất kỳ ký tự nào khác nếu bạn cần.

 Xóa văn bản sau ký tự cụ thể đầu tiên bằng một công thức

Để xóa mọi thứ sau ký tự cụ thể đầu tiên, bạn có thể sử dụng hàm LEFT và FIND để lấy kết quả, cú pháp chung là:

=LEFT(cell,FIND("char",cell)-1)
  • pin: Tham chiếu ô hoặc chuỗi văn bản mà bạn muốn xóa văn bản khỏi đó;
  • xe tăng: Dấu phân tách cụ thể mà bạn muốn xóa văn bản dựa trên.

Bây giờ, hãy nhập công thức dưới đây vào một ô trống, sau đó kéo chốt điền xuống các ô khác mà bạn muốn áp dụng công thức này và tất cả các ký tự sau dấu phẩy đầu tiên sẽ bị xóa ngay lập tức, xem ảnh chụp màn hình:

=LEFT(A2,FIND(",",A2)-1)

3.2 Xóa văn bản trước hoặc sau lần xuất hiện thứ N của một ký tự

Đôi khi, các chuỗi văn bản chứa nhiều phiên bản của một dấu phân cách cụ thể, bạn có thể muốn xóa tất cả các ký tự trước hoặc sau một phiên bản cụ thể, chẳng hạn như ký tự thứ hai, thứ ba hoặc thứ tư nếu bạn cần. Để đối phó với kiểu xóa này, bạn có thể sử dụng các thủ thuật sau:

 Xóa văn bản trước lần xuất hiện thứ N của một ký tự có công thức

Để xóa văn bản trước lần xuất hiện thứ N của một ký tự cụ thể, công thức sau có thể giúp bạn, cú pháp chung là:

=RIGHT(cell,LEN(cell)-FIND("#",SUBSTITUTE(cell,"char","#",N)))
  • pin: Tham chiếu ô hoặc chuỗi văn bản mà bạn muốn xóa văn bản khỏi đó;
  • xe tăng: Dấu phân tách cụ thể mà bạn muốn xóa văn bản dựa trên;
  • N: Sự xuất hiện của ký tự trước đó để xóa văn bản.

Ví dụ: để xóa mọi thứ trước dấu phẩy thứ hai khỏi chuỗi văn bản, bạn nên áp dụng công thức dưới đây:

=RIGHT(A2,LEN(A2)-FIND("#",SUBSTITUTE(A2,",","#",2)))

Chú thích: Trong công thức trên: A2 là ô mà bạn muốn xóa văn bản; , là ký tự cụ thể mà bạn muốn xóa văn bản dựa trên, bạn có thể thay đổi nó thành bất kỳ ký tự nào khác nếu bạn cần; 2 cho biết dấu phẩy thứ n trước đó bạn muốn xóa văn bản.

Và sau đó, kéo chốt điền để sao chép công thức sang các ô khác, xem ảnh chụp màn hình:

 Xóa văn bản sau lần xuất hiện thứ N của một ký tự có công thức

Để xóa văn bản sau lần xuất hiện thứ N của một dấu phân tách cụ thể, các hàm LEFT, SUBSTITUTE và FIND có thể giúp bạn. Cú pháp chung là:

=LEFT(cell, FIND("#", SUBSTITUTE(cell, "char", "#", N)) -1)
  • pin: Tham chiếu ô hoặc chuỗi văn bản mà bạn muốn xóa văn bản khỏi đó;
  • xe tăng: Dấu phân tách cụ thể mà bạn muốn xóa văn bản dựa trên;
  • N: Sự xuất hiện của ký tự sau đó để xóa văn bản.

Khi bạn hiểu cú pháp cơ bản, vui lòng sao chép hoặc nhập công thức dưới đây vào ô trống:

=LEFT(A2, FIND("#", SUBSTITUTE(A2, ",", "#", 2)) -1)

Chú thích: Trong công thức trên: A2 là ô mà bạn muốn xóa văn bản; , là ký tự cụ thể mà bạn muốn xóa văn bản dựa trên, bạn có thể thay đổi nó thành bất kỳ ký tự nào khác nếu bạn cần; 2 cho biết dấu phẩy thứ n mà sau đó bạn muốn xóa văn bản.

Sau đó, kéo chốt điền để sao chép công thức sang các ô khác và tất cả các ký tự sau dấu phẩy thứ hai sẽ bị xóa cùng một lúc, xem ảnh chụp màn hình:

 Xóa văn bản trước hoặc sau lần xuất hiện thứ N của một ký tự với chức năng Người dùng xác định

Như bạn có thể thấy, bạn có thể giải quyết các trường hợp xóa văn bản trước hoặc sau lần xuất hiện thứ N của một ký tự bằng cách sử dụng các hàm gốc của Excel trong các kết hợp khác nhau. Vấn đề là bạn cần nhớ những công thức lắt léo này. Trong trường hợp này, tôi sẽ tạo một Hàm do người dùng xác định để bao gồm tất cả các trường hợp, vui lòng làm như sau:

1. Giữ Alt + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Cửa sổ mô-đun.

Mã VBA: Xóa văn bản trước hoặc sau lần xuất hiện thứ N của một ký tự

Function RemoveTextOccurrence(Str As String, Delimiter As String, Occurrence As Integer, IsAfter As Boolean)
Dim xStr As String
Dim xStrLen, xF, xIntStart As Integer
xStr = Str
xStrLen = Len(xStr)
xIntStart = 1
For xF = 1 To Occurrence
xIntStart = InStr(xIntStart + 1, xStr, Delimiter, vbTextCompare)
If (xIntStart = 0) Or (xIntStart < 0) Then
    If IsAfter Then
    RemoveTextOccurrence = xStr
    Else
    RemoveTextOccurrence = ""
    End If
    Exit Function
End If
Next
If IsAfter Then
    RemoveTextOccurrence = Mid(Str, 1, xIntStart - 1)
Else
    RemoveTextOccurrence = Mid(Str, xIntStart + 1)
End If
End Function

3. Sau đó, đóng và thoát khỏi cửa sổ mã, quay lại trang tính, sử dụng các công thức sau:

Xóa văn bản trước lần xuất hiện thứ hai của dấu phẩy:

=RemoveTextOccurrence(A2, ", ", 2, FALSE)

Xóa văn bản sau lần xuất hiện thứ hai của dấu phẩy

=RemoveTextOccurrence(A2, ", ", 2, TRUE)

3.3 Xóa văn bản trước hoặc sau lần xuất hiện cuối cùng của một ký tự

Nếu bạn cần xóa tất cả văn bản trước hoặc sau ký tự cụ thể cuối cùng và chỉ để lại các chuỗi con sau hoặc trước ký tự cụ thể cuối cùng như hình minh họa bên dưới, phần này sẽ nói về một số công thức để giải quyết vấn đề này.

 Xóa văn bản trước lần xuất hiện cuối cùng của một ký tự có công thức

Để xóa tất cả các ký tự trước lần xuất hiện cuối cùng của một ký tự, cú pháp chung là:

=RIGHT(cell,LEN(cell)-SEARCH("#",SUBSTITUTE(cell,"char","#",LEN(cell)-LEN(SUBSTITUTE(cell,"char","")))))
  • pin: Tham chiếu ô hoặc chuỗi văn bản mà bạn muốn xóa văn bản khỏi đó;
  • xe tăng: Dấu phân tách cụ thể mà bạn muốn xóa văn bản dựa trên;

Bây giờ, nếu bạn cần xóa văn bản trước lần xuất hiện cuối cùng của dấu phẩy, vui lòng sao chép hoặc nhập công thức dưới đây vào ô trống:

=RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,",","#",LEN(A2)-LEN(SUBSTITUTE(A2,",","")))))

Chú thích: Trong công thức trên: A2 là ô mà bạn muốn xóa văn bản; , là ký tự cụ thể mà bạn muốn xóa văn bản dựa trên, bạn có thể thay đổi nó thành bất kỳ ký tự nào khác nếu bạn cần.

Sau đó, kéo chốt điền để sao chép công thức sang các ô khác và tất cả các ký tự trước dấu phẩy cuối cùng sẽ bị xóa như hình minh họa bên dưới:

 Xóa văn bản sau lần xuất hiện cuối cùng của một ký tự có công thức

Nếu các giá trị ô được phân tách bằng số lượng dấu phân cách thay đổi, bây giờ, bạn muốn xóa mọi thứ sau phiên bản cuối cùng của dấu phân cách đó, cú pháp chung là:

=LEFT(cell,FIND("#",SUBSTITUTE(cell,"char","#",LEN(cell)-LEN(SUBSTITUTE(cell,"char",""))))-1)
  • pin: Tham chiếu ô hoặc chuỗi văn bản mà bạn muốn xóa văn bản khỏi đó;
  • xe tăng: Dấu phân tách cụ thể mà bạn muốn xóa văn bản dựa trên;

Vui lòng sao chép hoặc nhập công thức bên dưới vào một ô trống, sau đó kéo chốt điền xuống để nhận được các kết quả khác mà bạn cần, xem ảnh chụp màn hình:

=LEFT(A2,FIND("#",SUBSTITUTE(A2,",","#",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))-1)

Chú thích: Trong công thức trên: A2 là ô mà bạn muốn xóa văn bản; , là ký tự cụ thể mà bạn muốn xóa văn bản dựa trên, bạn có thể thay đổi nó thành bất kỳ ký tự nào khác nếu bạn cần.

3.4 Xóa văn bản giữa các dấu ngoặc đơn

Nếu bạn có một danh sách các chuỗi văn bản với một phần các ký tự được đặt trong dấu ngoặc đơn, bây giờ, bạn có thể muốn xóa tất cả các ký tự trong dấu ngoặc đơn bao gồm cả chính dấu ngoặc đơn như hình minh họa bên dưới. Phần này sẽ nói về một số thủ thuật để giải quyết công việc này trong Excel.

 Xóa văn bản giữa các dấu ngoặc đơn bằng tính năng Tìm và Thay thế

Trong Excel, tính năng Tìm và Thay thế được tích hợp sẵn có thể giúp bạn tìm tất cả các văn bản trong dấu ngoặc đơn, sau đó thay thế chúng bằng không. Hãy làm như sau:

1. Chọn danh sách dữ liệu mà bạn muốn xóa văn bản giữa các dấu ngoặc đơn.

2. Sau đó nhấn vào Trang Chủ > Tìm & Chọn > Thay thế (hoặc nhấn Ctrl + H ) để đi đến hộp thoại Tìm và Thay thế, trong Tìm và Thay thế hộp thoại, hãy thực hiện các thao tác sau:

  • Trong tạp chí Tìm Loại lĩnh vực (*) vào hộp văn bản;
  • Trong tạp chí Với thay thế trường này, để trống trường này.

3. Sau đó nhấn vào Thay thế Tất cả , tất cả các ký tự trong dấu ngoặc đơn (bao gồm cả dấu ngoặc đơn) trong các ô đã chọn sẽ bị xóa cùng một lúc. Xem ảnh chụp màn hình:

Lời khuyên: Các Tìm và Thay thế tính năng cũng hoạt động đối với hai hoặc nhiều cặp dấu ngoặc đơn trong chuỗi văn bản.

 Xóa văn bản giữa các dấu ngoặc đơn với công thức

Bên cạnh tính năng Tìm và Thay thế, bạn cũng có thể sử dụng công thức để giải quyết tác vụ này trong Excel, cú pháp chung là:

=SUBSTITUTE(text,MID(LEFT(text,FIND(")",text)),FIND("(",text),LEN(text)),"")
  • văn bản: Chuỗi văn bản hoặc tham chiếu ô mà bạn muốn xóa các ký tự.

Bây giờ, hãy sao chép hoặc nhập công thức sau vào một ô trống mà bạn muốn nhận kết quả:

=SUBSTITUTE(A2,MID(LEFT(A2,FIND(")",A2)),FIND("(",A2),LEN(A2)),"")

Sau đó, kéo chốt điền xuống các ô mà bạn muốn áp dụng công thức này và tất cả văn bản trong dấu ngoặc đơn bao gồm cả dấu ngoặc đơn sẽ bị xóa ngay lập tức, xem ảnh chụp màn hình:

Lời khuyên: Nếu không có dấu ngoặc đơn trong giá trị ô, một lỗi sẽ được hiển thị sau khi áp dụng công thức trên, để bỏ qua lỗi, vui lòng sử dụng công thức dưới đây:

=IFERROR(SUBSTITUTE(A2,MID(LEFT(A2,FIND(")",A2)),FIND("(",A2),LEN(A2)),""),A2)

 Xóa văn bản giữa các dấu ngoặc đơn bằng Hàm do Người dùng Xác định

Công thức trên hoạt động tốt để xóa văn bản khỏi một cặp ngoặc đơn, nếu bạn cần xóa văn bản khỏi nhiều cặp ngoặc trong chuỗi văn bản, công thức sẽ không hoạt động chính xác. Ở đây, tôi sẽ tạo một Hàm do người dùng xác định đơn giản để giải quyết công việc này.

1. Giữ Alt + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Cửa sổ mô-đun.

Mã VBA: Xóa văn bản giữa các dấu ngoặc đơn

Function remtxt(ByVal str As String) As String
'Updateby Extendoffice
  While InStr(str, "(") > 0 And InStr(str, ")") > InStr(str, "(")
    str = Left(str, InStr(str, "(") - 1) & Mid(str, InStr(str, ")") + 1)
  Wend
  remtxt = Trim(str)
End Function

3. Sau đó, quay lại trang tính và nhập công thức này vào một ô trống: = remtxt (A2), sau đó kéo chốt điền xuống các ô mà bạn muốn áp dụng công thức này, tất cả các văn bản trong tất cả các dấu ngoặc đơn bao gồm cả dấu ngoặc đơn sẽ bị xóa như hình minh họa bên dưới:

Xóa các từ khỏi chuỗi văn bản

Trong một số trường hợp nhất định, bạn có thể muốn xóa một số từ khỏi danh sách ô, chẳng hạn như từ đầu tiên hoặc từ cuối cùng, các từ trùng lặp từ một ô. Để giải quyết những kiểu loại bỏ này, phần này sẽ giới thiệu một số phương pháp cho bạn.

4.1 Xóa từ đầu tiên hoặc từ cuối cùng khỏi chuỗi văn bản

Để xóa từ đầu tiên hoặc từ cuối cùng khỏi danh sách chuỗi văn bản, các công thức sau có thể giúp bạn.

 Xóa từ đầu tiên khỏi chuỗi văn bản bằng công thức

Loại bỏ các từ đầu tiên khỏi danh sách các chuỗi văn bản, bạn có thể tạo một công thức đơn giản dựa trên các hàm RIGHT, LEN và FIND, cú pháp chung là:

=RIGHT(text,LEN(text)-FIND(" ",text))
  • văn bản: Chuỗi văn bản hoặc tham chiếu ô mà bạn muốn xóa từ đầu tiên khỏi đó.

Bây giờ, hãy nhập hoặc sao chép công thức sau vào một ô trống:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

Và sau đó, kéo chốt điền xuống để áp dụng công thức cho các ô khác, xem ảnh chụp màn hình:

Lời khuyên: Nếu bạn cần xóa N từ đầu tiên khỏi ô, vui lòng sử dụng công thức dưới đây:

=MID(TRIM(text),1+FIND("~",SUBSTITUTE(TRIM(text)," ","~",N)),255)
  • văn bản: Chuỗi văn bản hoặc tham chiếu ô mà bạn muốn xóa n từ đầu tiên khỏi;
  • N: Cho biết số từ bạn muốn xóa khỏi đầu chuỗi văn bản.

Ví dụ: để xóa hai từ đầu tiên khỏi các ô, vui lòng sao chép hoặc nhập công thức dưới đây vào một ô trống để nhận được kết quả như bạn cần, xem ảnh chụp màn hình:

=MID(TRIM(A2),1+FIND("~",SUBSTITUTE(TRIM(A2)," ","~",2)),255)

  Xóa từ cuối cùng khỏi chuỗi văn bản bằng công thức

Để xóa từ cuối cùng khỏi chuỗi văn bản, bạn cũng có thể sử dụng công thức để giải quyết tác vụ này, cú pháp chung là:

=LEFT(TRIM(text),FIND("~",SUBSTITUTE(text," ","~",LEN(TRIM(text))-LEN(SUBSTITUTE(TRIM(text)," ",""))))-1)
  • văn bản: Chuỗi văn bản hoặc tham chiếu ô mà bạn muốn xóa từ cuối cùng khỏi đó;

Vui lòng sử dụng công thức dưới đây vào một ô trống, sau đó kéo chốt điền xuống để áp dụng công thức cho các ô khác, xem ảnh chụp màn hình:

=LEFT(TRIM(A2),FIND("~",SUBSTITUTE(A2," ","~",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))-1)

Lời khuyên: Để xóa N từ cuối cùng khỏi danh sách ô, cú pháp chung là:

=LEFT(text,FIND("~",SUBSTITUTE(text," ","~",LEN(text)-LEN(SUBSTITUTE(text," ",""))-(N-1))))
  • văn bản: Chuỗi văn bản hoặc tham chiếu ô mà bạn muốn xóa n từ cuối cùng khỏi;
  • N: Cho biết số từ bạn muốn xóa khỏi chuỗi văn bản.

Giả sử, để xóa 3 từ cuối cùng khỏi danh sách ô, vui lòng sử dụng công thức dưới đây để trả về kết quả, xem ảnh chụp màn hình:

=LEFT(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-(3-1))))

4.2 Loại bỏ các ký tự hoặc từ trùng lặp trong một ô

Khi loại bỏ các giá trị hoặc hàng trùng lặp, Excel cung cấp một số tùy chọn khác nhau, nhưng khi xóa một số ký tự hoặc từ trùng lặp trong một ô nhất định, có thể không có bất kỳ tính năng tích hợp nào tốt để giải quyết nó. Trong trường hợp này, phần này sẽ giúp tạo một số Hàm do Người dùng Xác định để giải quyết câu đố này.

 Loại bỏ các ký tự trùng lặp trong một ô bằng cách sử dụng Hàm do Người dùng Xác định

Nếu bạn có nhiều lần xuất hiện của cùng một ký tự trong một ô, để xóa các ký tự trùng lặp trong một ô và chỉ giữ lại các lần xuất hiện đầu tiên như hình minh họa bên dưới, bạn có thể sử dụng Hàm do Người dùng Xác định sau.

1. Giữ Alt + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Cửa sổ mô-đun.

Mã VBA: Loại bỏ các ký tự trùng lặp trong một ô

Function RemoveDupeschars(pWorkRng As Range) As String
'Updateby Extendoffice
Dim xValue As String
Dim xChar As String
Dim xOutValue As String
Set xDic = CreateObject("Scripting.Dictionary")
xValue = pWorkRng.Value
For i = 1 To VBA.Len(xValue)
    xChar = VBA.Mid(xValue, i, 1)
    If xDic.Exists(xChar) Then
    Else
        xDic(xChar) = ""
        xOutValue = xOutValue & xChar
    End If
Next
RemoveDupeschars = xOutValue
End Function

3. Sau đó, đóng cửa sổ mã, quay lại trang tính và nhập công thức này = RemoveDupeschars (A2) vào một ô trống bên cạnh dữ liệu của bạn, rồi kéo chốt điền vào các ô mà bạn muốn áp dụng công thức này, xem ảnh chụp màn hình:

Chú thích: A2 là ô dữ liệu mà bạn muốn xóa các ký tự trùng lặp.

Mẹo: Hàm có phân biệt chữ hoa chữ thường, vì vậy coi chữ thường và chữ hoa là các ký tự khác nhau.

 Loại bỏ các từ trùng lặp trong một ô bằng cách sử dụng chức năng do người dùng xác định

Giả sử, bạn có các từ hoặc chuỗi văn bản giống nhau trong một ô và muốn xóa tất cả các từ giống nhau khỏi ô như hình minh họa bên dưới. Bạn có thể sử dụng Hàm do Người dùng Xác định sau để giải quyết tác vụ này trong Excel.

1. Giữ Alt + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Cửa sổ mô-đun.

Mã VBA: Loại bỏ các từ trùng lặp trong một ô

Function RemoveDupeswords(txt As String, Optional delim As String = " ") As String
'Updateby Extendoffice
    Dim x
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For Each x In Split(txt, delim)
            If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
        Next
        If .Count > 0 Then RemoveDupeswords = Join(.keys, delim)
    End With
End Function

3. Sau đó, đóng cửa sổ mã, quay lại trang tính và nhập công thức này = RemoveDupeswords (A2, ",") vào một ô trống bên cạnh dữ liệu của bạn, rồi kéo chốt điền vào các ô mà bạn muốn áp dụng công thức này, xem ảnh chụp màn hình:

Chú thích: A2 là ô mà bạn muốn xóa các từ trùng lặp, dấu phẩy và dấu cách (, ) là các dấu phân cách để phân tách các chuỗi văn bản, bạn có thể thay đổi chúng thành bất kỳ dấu phân cách nào khác theo nhu cầu của mình.

Mẹo: Hàm này không phân biệt chữ hoa chữ thường, chữ thường và chữ hoa được coi là cùng một ký tự.

4.3 Cắt chuỗi văn bản thành N từ

Nếu bạn có một chuỗi văn bản dài trong một ô, đôi khi, bạn có thể muốn cắt chuỗi văn bản thành một số từ nhất định, có nghĩa là chỉ giữ lại n từ đầu tiên và cắt các từ còn lại. Phần này sẽ nói về một số thủ thuật giúp bạn đạt được công việc này trong Excel.

 Cắt chuỗi văn bản thành N từ với công thức

Để cắt một chuỗi văn bản thành N từ, bạn có thể tạo công thức dựa trên các hàm LEFT, FIND và SUBSTITUTE, cú pháp chung là:

=LEFT(text,FIND("~",SUBSTITUTE(text," ","~",N))-1)
  • văn bản: Chuỗi văn bản hoặc tham chiếu ô mà bạn muốn cắt;
  • N: Số từ bạn muốn giữ lại từ phía bên trái của chuỗi văn bản đã cho.

Để giải quyết công việc này, vui lòng sao chép hoặc nhập công thức dưới đây vào một ô trống:

=LEFT(A2,FIND("~",SUBSTITUTE(A2," ","~",B2))-1)

Và sau đó, kéo chốt điền xuống để áp dụng công thức này cho các ô khác, xem ảnh chụp màn hình:

 Cắt chuỗi văn bản thành N từ với chức năng do người dùng xác định

Ngoại trừ công thức trên, bạn cũng có thể tạo Hàm do người dùng xác định để giải quyết tác vụ này, vui lòng thực hiện như sau:

1. Giữ Alt + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Cửa sổ mô-đun.

Mã VBA: Cắt chuỗi văn bản thành N từ

Function GetNWords(StrWords As String, Num_of_Words As Integer) As String
'Updateby Extendoffice
Dim xArr
Dim xRes As String
Dim xF As Integer
xStr = StrWords
If (Num_of_Words < 1) Then
    GetNWords = ""
    Exit Function
End If
xArr = Split(xStr, " ")
xRes = ""
On Error Resume Next
For xF = 0 To UBound(xArr)
    If Trim(xArr(xF)) <> "" Then
    Num_of_Words = Num_of_Words - 1
        If xRes = "" Then
            xRes = Trim(xArr(xF))
        Else
            xRes = xRes & " " & Trim(xArr(xF))
        End If
    End If
    If Num_of_Words = 0 Then Exit For
Next
If Num_of_Words = 0 Then
    GetNWords = xRes & "..."
Else
    GetNWords = xRes & "..."
End If
End Function

3. Sau đó, đóng và thoát khỏi cửa sổ mã, quay lại trang tính và nhập công thức này: = GetNWords (A2, B2) vào một ô trống, sau đó kéo chốt điền xuống để áp dụng công thức này cho các ô khác, chỉ số từ cụ thể đầu tiên được giữ lại như hình minh họa bên dưới:

  • Thanh siêu công thức (dễ dàng chỉnh sửa nhiều dòng văn bản và công thức); Bố cục đọc (dễ dàng đọc và chỉnh sửa số lượng ô lớn); Dán vào Dải ô đã Lọchữu ích. Cảm ơn !
  • Hợp nhất các ô / hàng / cột và Lưu giữ dữ liệu; Nội dung phân chia ô; Kết hợp các hàng trùng lặp và Tổng / Trung bình... Ngăn chặn các ô trùng lặp; So sánh các dãyhữu ích. Cảm ơn !
  • Chọn trùng lặp hoặc duy nhất Hàng; Chọn hàng trống (tất cả các ô đều trống); Tìm siêu và Tìm mờ trong Nhiều Sổ làm việc; Chọn ngẫu nhiên ...
  • Bản sao chính xác Nhiều ô mà không thay đổi tham chiếu công thức; Tự động tạo tài liệu tham khảo sang Nhiều Trang tính; Chèn Bullets, Hộp kiểm và hơn thế nữa ...
  • Yêu thích và Chèn công thức nhanh chóng, Dãy, Biểu đồ và Hình ảnh; Mã hóa ô với mật khẩu; Tạo danh sách gửi thư và gửi email ...
  • Trích xuất văn bản, Thêm Văn bản, Xóa theo Vị trí, Xóa không gian; Tạo và In Tổng số phân trang; Chuyển đổi giữa nội dung ô và nhận xéthữu ích. Cảm ơn !
  • Siêu lọc (lưu và áp dụng các lược đồ lọc cho các trang tính khác); Sắp xếp nâng cao theo tháng / tuần / ngày, tần suất và hơn thế nữa; Bộ lọc đặc biệt bằng cách in đậm, in nghiêng ...
  • Kết hợp Workbook và WorkSheets; Hợp nhất các bảng dựa trên các cột chính; Chia dữ liệu thành nhiều trang tính; Chuyển đổi hàng loạt xls, xlsx và PDFhữu ích. Cảm ơn !
  • Nhóm bảng tổng hợp theo số tuần, ngày trong tuần và hơn thế nữa ... Hiển thị các ô đã mở khóa, đã khóa bởi các màu sắc khác nhau; Đánh dấu các ô có công thức / tênhữu ích. Cảm ơn !
tab kte 201905
  • Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
  • Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
  • Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
officetab dưới cùng
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations