Làm cách nào để xóa 3 chữ cái khỏi một ô trong excel?
|
Đôi khi bạn muốn xóa một số chữ cái cuối cùng khỏi một chuỗi trong bảng tính của mình. Đối với điều này, bạn có thể sử dụng một chức năng rất tiện dụng. Show
Giả sử bạn muốn xóa 3 ký tự cuối cùng khỏi một chuỗi trong ô
Hay tổng quát hơn ________số 8 Chức năng LEFT() này hoạt động như thế nào?Ví dụ, trong ô Trừ khi bạn nằm trong số ít những người may mắn, rất có thể bạn sẽ nhận được dữ liệu của mình ở định dạng cần được làm sạch Một trường hợp sử dụng phổ biến của điều này là khi bạn nhận được tập dữ liệu và bạn phải xóa một số ký tự từ bên trái cho tập dữ liệu này Đây có thể là một số ký tự cố định mà bạn cần xóa từ bên trái hoặc có thể ở trước một ký tự hoặc chuỗi cụ thể Trong hướng dẫn này, tôi sẽ chỉ cho bạn một số ví dụ đơn giản về việc loại bỏ số lượng ký tự bắt buộc từ bên trái của một chuỗi văn bản Hướng dẫn này bao gồm
Loại bỏ số lượng ký tự cố định từ bên tráiNếu bạn nhận được một tập dữ liệu nhất quán và theo cùng một mẫu, thì bạn có thể sử dụng kỹ thuật được hiển thị ở đây để xóa một số ký tự cố định khỏi bên trái của chuỗi trong mỗi ô Bên dưới, tôi có tập dữ liệu chứa id sản phẩm, bao gồm mã gồm hai chữ cái theo sau là một số và tôi chỉ muốn trích xuất số trong mỗi ô (có nghĩa là tôi muốn xóa ba ký tự đầu tiên khỏi mỗi ô Dưới đây là công thức để làm điều này =RIGHT(A2,LEN(A2)-3) Công thức trên sử dụng hàm LEN để lấy tổng số ký tự trong ô ở cột A Từ giá trị mà chúng tôi nhận được từ hàm LEN, chúng tôi trừ 3, vì chúng tôi chỉ muốn trích xuất các số và muốn xóa ba ký tự đầu tiên từ bên trái của chuỗi trong mỗi ô Giá trị này sau đó được sử dụng trong hàm RIGHT để trích xuất mọi thứ trừ ba ký tự đầu tiên từ bên trái Vì chúng tôi đã mã hóa cứng số ký tự mà chúng tôi muốn xóa từ bên trái, phương pháp này sẽ chỉ hoạt động khi bạn luôn muốn xóa số ký tự cố định từ bên trái. Trong ví dụ trên, nếu chúng ta có dữ liệu không nhất quán trong đó có số lượng ký tự khác nhau trước số, thì chúng ta sẽ không thể sử dụng công thức trên (hãy sử dụng công thức ở phần tiếp theo trong trường hợp như vậy) Xóa các ký tự từ bên trái dựa trên Dấu phân cách (Dấu cách, Dấu phẩy, Dấu gạch ngang)Trong hầu hết các trường hợp, bạn sẽ không nhận được dữ liệu nhất quán khi số lượng ký tự bạn muốn xóa từ bên trái sẽ có độ dài cố định Ví dụ: bên dưới tôi có tập dữ liệu tên nơi tôi muốn xóa tên và chỉ lấy họ Và như bạn có thể thấy, độ dài của tên thay đổi, vì vậy tôi không thể sử dụng công thức được đề cập trong phần trước Trong trường hợp này, tôi vẫn cần dựa vào một mẫu nhất quán – đó sẽ là ký tự khoảng trắng phân tách họ và tên Nếu tôi có thể xóa mọi thứ cho đến ký tự khoảng trắng, tôi sẽ nhận được kết quả mong muốn Và nhờ các chức năng tuyệt vời trong Excel, có nhiều cách để thực hiện việc này Sử dụng công thức ĐÚNGTrước tiên chúng ta hãy xem một công thức sẽ xóa mọi thứ trước ký tự khoảng trắng và bạn sẽ chỉ còn lại họ =RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND(" ",TRIM(A2)))Công thức trên sẽ xóa mọi thứ ở bên trái của ký tự khoảng trắng (kể cả ký tự khoảng trắng) và bạn sẽ nhận được phần còn lại của văn bản (họ trong ví dụ này) Hãy để tôi giải thích nhanh cách thức hoạt động của công thức này Đầu tiên, tôi đã sử dụng hàm FIND để lấy vị trí của ký tự khoảng trắng trong ô Trong công thức trên, FIND(” “,TRIM(A2))) sẽ trả về 6 khi ký tự khoảng trắng xuất hiện ở vị trí thứ sáu trong tên trong ô A2 Sau đó, tôi đã sử dụng hàm LEN và trừ đi giá trị mà hàm FIND đã cho tôi để lấy tổng số ký tự sau ký tự khoảng trắng trong ô Và bây giờ tôi đã biết có bao nhiêu ký tự cần trích xuất từ bên phải của chuỗi văn bản, tôi đã sử dụng hàm RIGHT để trích xuất nó Ghi chú. Trong công thức trên, tôi đã sử dụng hàm TRIM để đảm bảo rằng mọi khoảng trắng ở đầu, cuối hoặc kép đều được xử lý Một lợi ích lớn của việc sử dụng công thức là kết quả sẽ tự động cập nhật trong trường hợp bạn thực hiện bất kỳ thay đổi nào đối với dữ liệu trong Cột A Sử dụng Flash FillMột cách thực sự nhanh chóng khác để nhanh chóng xóa văn bản từ bên trái của dấu phân cách là sử dụng Flash Fill Flash Fill hoạt động bằng cách xác định các mẫu từ một vài đầu vào từ người dùng. Trong ví dụ của chúng tôi, tôi sẽ phải nhập thủ công kết quả dự kiến cho một hoặc hai ô, sau đó tôi có thể sử dụng Flash Fill để thực hiện theo cùng một mẫu cho tất cả các ô còn lại Giả sử tôi có một tập dữ liệu như hình bên dưới, nơi tôi muốn xóa tất cả các ký tự trước ký tự khoảng trắng Dưới đây là các bước sử dụng flash fill để xóa các ký tự ở bên trái của dấu phân cách
Các bước trên sẽ xóa mọi thứ ở bên trái của ký tự khoảng trắng và bạn sẽ chỉ còn lại họ Lưu ý rằng Control + E (hoặc Command + E trong Mac) là phím tắt cho Flash Fill trong Excel Bây giờ hãy để tôi giải thích nhanh những gì đang xảy ra ở đây Khi tôi nhập kết quả dự kiến vào ô B2 theo cách thủ công, sau đó chọn tất cả các ô và sử dụng Flash Fill, nó sẽ cố xác định mẫu bằng cách sử dụng kết quả tôi đã nhập vào ô B2 Trong ví dụ này, nó có thể xác định rằng tôi đang cố trích xuất họ, điều đó có nghĩa là tôi đang cố xóa mọi thứ ở bên trái của họ Khi nó có thể xác định mẫu này, nó sẽ áp dụng nó cho tất cả các ô trong cột Trong một số trường hợp, có thể Excel sẽ không thể xác định đúng mẫu khi sử dụng Flash Fill. Trong những tình huống như vậy, bạn có thể thử nhập kết quả vào nhiều ô để excel có thêm dữ liệu để hiểu mẫu Ví dụ: bạn có thể nhập kết quả dự kiến vào các ô B2 và B3, sau đó chọn toàn bộ cột và sử dụng Flash Fill Bạn cũng có thể tìm thấy tùy chọn Flash Fill trong tab Home -> Editing -> Fill -> Flash Fill Lưu ý trong trường hợp dữ liệu gốc ở cột A của bạn thay đổi, bạn sẽ phải thực hiện lại các bước một lần nữa để loại bỏ các ký tự trước dấu phân cách. Không giống như phương pháp công thức, phương pháp này cho kết quả tĩnh không tự động cập nhật Sử dụng văn bản thành cộtMột cách nhanh chóng khác để xóa tất cả các ký tự trước dấu phân cách là sử dụng tính năng Chuyển văn bản thành cột Giả sử tôi có tập dữ liệu như hình bên dưới và tôi muốn xóa mọi thứ trước dấu gạch ngang Dưới đây là các bước để làm điều này
Các bước trên sẽ chia thành các cột riêng biệt dựa trên dấu phân cách đã chỉ định Lưu ý rằng trong ví dụ này, tôi đã sử dụng dấu phân cách để chia dữ liệu thành các cột riêng biệt. Bạn cũng có thể sử dụng Chuyển văn bản thành Cột để xóa một số ký tự cụ thể khỏi bên trái của chuỗi văn bản. Vì thế, bạn nên chọn Độ dài cố định thay vì tùy chọn Dấu phân cách ở Bước 1/3 trong trình hướng dẫn Chuyển văn bản thành cột Lưu ý trong trường hợp dữ liệu gốc ở cột A của bạn thay đổi, bạn sẽ phải thực hiện lại các bước một lần nữa để loại bỏ các ký tự trước dấu phân cách. Không giống như phương pháp công thức, phương pháp này cho kết quả tĩnh không tự động cập nhật Xóa tất cả văn bản bên trái của một chuỗi cụ thểĐôi khi, bạn có thể có một tập dữ liệu mà bạn cần loại bỏ tất cả văn bản trước một chuỗi văn bản cụ thể Ví dụ: bên dưới tôi có một tập dữ liệu trong đó tôi có tên nhân viên theo sau là số điện thoại của họ Tôi chỉ muốn trích xuất số điện thoại từ mỗi ô, điều đó có nghĩa là tôi muốn xóa mọi thứ trước số điện thoại Như với hầu hết các phương pháp làm sạch dữ liệu trong Excel, tôi cần tìm kiếm một mẫu mà tôi có thể sử dụng để loại bỏ mọi thứ trước số điện thoại Trong trường hợp này, đó là chuỗi văn bản 'Tel. ’ Vì vậy, tất cả những gì tôi cần làm là tìm vị trí của chuỗi 'Tel. ' trong mỗi ô và xóa mọi thứ trước nó (bao gồm cả nó) Mặc dù bạn có thể xây dựng một công thức nâng cao trong Excel để làm điều này, nhưng hãy để tôi chỉ cho bạn một cách thực sự thú vị bằng cách sử dụng phương pháp Tìm và Thay thế Dưới đây là các bước để xóa tất cả văn bản trước một chuỗi văn bản cụ thể
Các bước trên sẽ xóa mọi thứ trước chuỗi 'Tel. ’, và bạn sẽ chỉ còn lại số điện thoại Cái này hoạt động ra sao? Trong ví dụ trên, tôi đã sử dụng ký tự đại diện – dấu hoa thị (*) Vì tôi muốn xóa mọi thứ ở bên trái của chuỗi 'Tel. ’, tôi đã thêm dấu hoa thị trước dấu hoa thị và sử dụng dấu hoa thị đó trong trường ‘Tìm nội dung’ Dấu hoa thị (*) là một ký tự đại diện có thể đại diện cho bất kỳ số lượng ký tự nào trong Excel Điều này có nghĩa là khi tôi yêu cầu Excel tìm ‘*Tel. ’, Nó sẽ tìm chuỗi ‘Tel. ' trong mỗi ô và nếu nó tìm thấy chuỗi này trong bất kỳ ô nào, bất kể vị trí của nó, mọi thứ cho đến thời điểm đó sẽ được xem xét khi thay thế văn bản Và vì tôi đã thay thế cái này bằng không có gì (bằng cách để trống trường 'Thay thế bằng'), nên nó chỉ cần xóa mọi thứ cho đến chuỗi đó trong ô Điều này có nghĩa là tôi chỉ còn lại các ký tự sau chuỗi văn bản đó và mọi thứ trước chuỗi văn bản đó bao gồm cả chính chuỗi đó đều bị xóa Xóa tất cả văn bản từ bên trái (và giữ lại các số)Đôi khi, bạn có thể nhận được một tập dữ liệu trong đó bạn có văn bản và số cùng nhau trong một ô, như minh họa bên dưới và bạn muốn xóa tất cả văn bản nhưng chỉ giữ lại số Điều này có thể dễ dàng được thực hiện bằng cách sử dụng một công thức đơn giản Dưới đây là công thức sẽ xóa tất cả văn bản ở bên trái của các số, do đó bạn chỉ còn lại phần số của chuỗi =RIGHT(A2,LEN(A2)-MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),LEN(A2)))+1)Công thức trên sẽ xóa tất cả các phần văn bản khỏi phần bên trái của ô để bạn chỉ còn lại các số Bây giờ hãy để tôi giải thích nhanh cách thức hoạt động của công thức này Phần FIND({0,1,2,3,4,5,6,7,8,9},A2) của công thức sẽ tìm kiếm 10 chữ số này trong ô và sẽ trả về vị trí của các chữ số này dưới dạng một Ví dụ: để bán A2, kết quả trả về theo công thức này sẽ là {8,#VALUE. ,#GIÁ TRỊ. ,#GIÁ TRỊ. ,6,#VALUE. ,#GIÁ TRỊ. ,#GIÁ TRỊ. ,#GIÁ TRỊ. ,7} Như bạn có thể thấy, nó trả về 10 giá trị trong đó mỗi giá trị sẽ biểu thị vị trí của số đó trong ô Vì chữ số 0 xuất hiện ở vị trí thứ tám, nên giá trị đầu tiên được trả về là 8 và vì ô không chứa chữ số 1 nên nó trả về lỗi Giá trị Tương tự, tất cả các chữ số được phân tích và một số được trả về nếu chữ số đó có trong ô và lỗi giá trị được trả về nếu chữ số đó không có trong ô Bây giờ, công thức TÌM này được bao bọc trong công thức IFERROR, để thay vì lỗi giá trị, chúng tôi nhận được một giá trị có ý nghĩa hơn Trong trường hợp này, công thức IFERROR sẽ trả về một số khi nó tìm thấy chữ số trong ô và trong trường hợp không tìm thấy chữ số đó, nó sẽ trả về độ dài tối đa của các ký tự trong ô (điều này được thực hiện bằng cách sử dụng công thức LEN Điều này được thực hiện để đảm bảo rằng trong trường hợp có các ô không có số, công thức IFERROR sẽ vẫn trả về một chữ số có thể được sử dụng trong công thức (nếu không, nó sẽ trả về lỗi) Bây giờ với các mảng số này, tôi đã sử dụng hàm MIN để tìm ra giá trị nhỏ nhất trong mảng đó. điều này sẽ cho tôi biết vị trí bắt đầu nơi các số bắt đầu trong ô Ví dụ ở ô A2 nó cho mình là 6 nghĩa là phần chữ trong ô kết thúc ở ký tự thứ 5 và các số bắt đầu từ ký tự thứ 6 trở đi Bây giờ tôi đã biết các số bắt đầu ở vị trí nào trong ô, tôi cần biết có bao nhiêu ký tự từ bên trái tôi cần xóa Để làm điều này, tôi lại sử dụng hàm LEN để tìm tổng độ dài của các ký tự trong một ô và từ đó, tôi đã trừ đi kết quả của hàm main để tôi biết có bao nhiêu ký tự văn bản ở bên trái Lưu ý rằng tôi cũng đã trừ 1 vì tôi muốn loại trừ số đầu tiên trong ô (nếu tôi không trừ 1 trong công thức này, nó cũng sẽ xóa số đầu tiên cùng với văn bản) Và cuối cùng, tôi đã sử dụng hàm RIGHT để trích xuất tất cả các số từ bên phải, về cơ bản có nghĩa là tất cả các ký tự văn bản bên trái đều bị xóa Xóa tất cả các số từ bên tráiTrong phần trước, tôi đã hướng dẫn bạn cách loại bỏ tất cả các ký tự văn bản từ bên trái để chúng ta chỉ còn lại các số trong ô Nhưng nếu tình thế đảo ngược Điều gì sẽ xảy ra nếu tôi có một tập dữ liệu như hình bên dưới, nơi tôi muốn xóa tất cả các số ở bên trái và giữ lại tất cả các ký tự văn bản Chúng ta có thể sử dụng một công thức tương tự với một số điều chỉnh Dưới đây là công thức sẽ loại bỏ tất cả các số ở bên trái để bạn chỉ nhận được phần văn bản của ô =MID(A2,MIN(IFERROR(FIND({"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"},LOWER(A2)),LEN(A2))),1000)Mặc dù công thức này có thể trông lớn hơn và đáng sợ hơn một chút so với công thức trước, nhưng logic hoàn toàn giống nhau Trong công thức này, chúng ta đã sử dụng hàm FIND để tìm ra vị trí của tất cả 26 bảng chữ cái trong tiếng Anh Phần TÌM của công thức sẽ kiểm tra ô và cung cấp cho chúng ta vị trí của mỗi trong số 26 bảng chữ cái Lưu ý rằng tôi cũng đã sử dụng LOWER(A2) thay vì A2, bởi vì bảng chữ cái mà tôi đang sử dụng trong công thức FIND là chữ thường Sau đó, tôi đã bọc công thức tốt trong IFERROR, để trong trường hợp công thức không thể tìm thấy vị trí của một bảng chữ cái cụ thể, thay vì trả về lỗi giá trị, nó sẽ trả về độ dài của nội dung trong ô (được đưa ra Điều này là để đảm bảo rằng trong trường hợp tôi có một ô không có phần văn bản mà chỉ có các số, thì tôi vẫn nhận được một giá trị số Sau đó, tôi đã sử dụng hàm MIN để tìm ra vị trí nhỏ nhất mà văn bản bắt đầu Điều này sẽ cho tôi biết vị trí của các số sẽ kết thúc và vị trí bắt đầu của văn bản để tôi có thể tách nội dung của ô để xóa tất cả các số ở bên trái và giữ lại phần văn bản Bây giờ tôi đã biết vị trí bắt đầu của các ký tự văn bản trong ô, tôi đã sử dụng hàm MID để trích xuất mọi thứ bắt đầu từ vị trí đó cho đến hết Lưu ý rằng tôi đã sử dụng 1000 ký tự được trích xuất trong hàm mid, nhưng trong trường hợp ô của bạn có ít ký tự hơn, thì chỉ có bấy nhiêu ký tự được trích xuất Vì vậy, đây là một số ví dụ mà chúng tôi đã xóa các ký tự từ bên trái trong một ô trong Excel Tôi đã chỉ cho bạn các công thức để loại bỏ một số ký tự cố định từ bên trái hoặc loại bỏ các ký tự bên trái dựa trên dấu phân cách Tôi cũng đã chỉ cho bạn cách sử dụng kỹ thuật tìm và thay thế đơn giản để xóa tất cả các ký tự bên trái trước một chuỗi cụ thể Và cuối cùng tôi đã chỉ cho bạn hai công thức mà bạn có thể sử dụng để xóa chỉ các số hoặc chỉ văn bản từ bên trái |
