Hướng dẫn giải thích cách phân chia các ô trong Excel bằng các công thức và tính năng phân chia văn bản. Bạn sẽ học cách tách văn bản bằng dấu phẩy, không gian hoặc bất kỳ dấu phân cách nào khác và cách chia chuỗi thành văn bản và số. Show
Chia văn bản từ một ô thành một số ô là nhiệm vụ mà tất cả người dùng Excel đang xử lý một lần trong một thời gian. Trong một trong những bài viết trước đây của chúng tôi, chúng tôi đã thảo luận về cách phân chia các ô trong Excel bằng tính năng văn bản cho cột và flash điền. Hôm nay, chúng tôi sẽ xem xét sâu về cách bạn có thể phân chia các chuỗi bằng các công thức và công cụ văn bản phân chia. Cách chia văn bản trong Excel bằng công thứcĐể phân chia chuỗi trong Excel, bạn thường sử dụng hàm trái, phải hoặc giữa kết hợp với tìm hoặc tìm kiếm. Thoạt nhìn, một số công thức có thể trông phức tạp, nhưng logic trên thực tế khá đơn giản, và các ví dụ sau đây sẽ cung cấp cho bạn một số manh mối. Chuỗi phân chia theo dấu phẩy, dấu chấm phẩy, dấu gạch chéo, dấu gạch ngang hoặc phân định khácKhi tách các ô trong Excel, chìa khóa là xác định vị trí của dấu phân cách trong chuỗi văn bản. Tùy thuộc vào nhiệm vụ của bạn, điều này có thể được thực hiện bằng cách sử dụng tìm kiếm không nhạy cảm trường hợp hoặc tìm thấy trường hợp nhạy cảm. Khi bạn có vị trí của Delimiter, hãy sử dụng hàm phải, trái hoặc giữa để trích xuất phần tương ứng của chuỗi văn bản. Để hiểu rõ hơn, hãy xem xét ví dụ sau. Giả sử bạn có một danh sách SKU của mẫu kích thước màu vật phẩm và bạn muốn chia cột thành 3 cột riêng biệt: 
Theo cách tương tự, bạn có thể phân chia cột bởi bất kỳ nhân vật nào khác. Tất cả những gì bạn phải làm là thay thế "-" bằng dấu phân cách yêu cầu, ví dụ: không gian (""), dấu phẩy (","), chém ("/"), đại tràng (";"), dấu chấm phẩy (";" ), và như thế.space (" "), comma (","), slash ("/"), colon (";"), semicolon (";"), and so on. Mẹo. Trong các công thức trên, +1 và -1 tương ứng với số lượng ký tự trong dấu phân cách. Trong ví dụ này, đó là một dấu gạch nối (1 ký tự). Nếu dấu phân cách của bạn bao gồm 2 ký tự, ví dụ: Một dấu phẩy và một không gian, sau đó chỉ cung cấp dấu phẩy (",") cho chức năng tìm kiếm và sử dụng +2 và -2 thay vì +1 và -1. In the above formulas, +1 and -1 correspond to the number of characters in the delimiter. In this example, it's a hyphen (1 character). If your delimiter consists of 2 characters, e.g. a comma and a space, then supply only the comma (",") to the SEARCH function, and use +2 and -2 instead of +1 and -1. Cách phân chia chuỗi theo dòng phá vỡ trong ExcelĐể chia văn bản theo không gian, sử dụng các công thức tương tự như các công thức được thể hiện trong ví dụ trước. Sự khác biệt duy nhất là bạn sẽ cần hàm char để cung cấp ký tự ngắt dòng vì bạn không thể gõ trực tiếp vào công thức. Giả sử, các ô bạn muốn phân chia trông giống như thế này: Lấy các công thức từ ví dụ trước và thay thế một dấu gạch nối ("-") bằng char (10) trong đó 10 là mã ASCII cho nguồn cấp dữ liệu.
Và đây là kết quả trông như thế nào: Cách chia văn bản và số trong ExcelĐể bắt đầu, không có giải pháp phổ quát nào sẽ hoạt động cho tất cả các chuỗi chữ và số. Công thức nào để sử dụng phụ thuộc vào mẫu chuỗi cụ thể. Dưới đây bạn sẽ tìm thấy các công thức cho hai kịch bản phổ biến. Chia chuỗi của mẫu 'Text + Number'Giả sử, bạn có một cột các chuỗi với văn bản và số kết hợp, trong đó một số luôn theo dõi văn bản. Bạn muốn phá vỡ các chuỗi gốc để văn bản và số xuất hiện trong các ô riêng biệt, như thế này: Kết quả có thể đạt được theo hai cách khác nhau. Phương pháp 1: Số chữ số và trích xuất mà nhiều ký tựCách dễ nhất để phân chia chuỗi văn bản trong đó số đến sau văn bản là: Để trích xuất số, bạn tìm kiếm chuỗi cho mỗi số có thể từ 0 đến 9, lấy tổng số số và trả về nhiều ký tự từ cuối chuỗi.extract numbers, you search the string for every possible number from 0 to 9, get the numbers total, and return that many characters from the end of the string. Với chuỗi ban đầu trong A2, công thức diễn ra như sau:
Để trích xuất văn bản, bạn tính toán số lượng ký tự văn bản, chuỗi chứa bằng cách trừ đi số chữ số được trích xuất (C2) từ tổng chiều dài của chuỗi gốc trong A2. Sau đó, bạn sử dụng hàm bên trái để trả về nhiều ký tự từ đầu chuỗi.extract text, you calculate how many text characters the string contains by subtracting the number of extracted digits (C2) from the total length of the original string in A2. After that, you use the LEFT function to return that many characters from the beginning of the string.
Trong đó A2 là chuỗi gốc và C2 là số được trích xuất, như được hiển thị trong ảnh chụp màn hình: Phương pháp 2: Tìm ra vị trí của chữ số 1 trong chuỗiMột giải pháp thay thế sẽ là sử dụng công thức sau để xác định vị trí của chữ số đầu tiên trong chuỗi:
Khi vị trí của chữ số đầu tiên được tìm thấy, bạn có thể chia văn bản và số bằng cách sử dụng các công thức rất đơn giản và phải. Để trích xuất văn bản:text:
Để trích xuất số:number:
Trong đó A2 là chuỗi ban đầu và B2 là vị trí của số đầu tiên. Để loại bỏ cột trợ giúp giữ vị trí của chữ số đầu tiên, bạn có thể nhúng công thức tối thiểu vào các chức năng bên trái và bên phải: Công thức để trích xuất văn bản:text:
Công thức để trích xuất số:numbers:
Chuỗi phân chia của mẫu 'số + văn bản'Nếu bạn đang phân tách các ô nơi văn bản xuất hiện sau số, bạn có thể trích xuất các số với công thức sau:extract numbers with the following formula:
Công thức tương tự như mô tả được thảo luận trong ví dụ trước, ngoại trừ việc bạn sử dụng hàm bên trái thay vì bên phải để lấy số từ phía bên trái của chuỗi. Khi bạn có các số, ExtractText bằng cách trừ đi số chữ số từ tổng độ dài của chuỗi gốc:extract text by subtracting the number of digits from the total length of the original string:
Trong đó A2 là chuỗi gốc và B2 là số được trích xuất, như được hiển thị trong ảnh chụp màn hình bên dưới: Mẹo. Để nhận số từ bất kỳ vị trí nào trong chuỗi văn bản, hãy sử dụng công thức này hoặc công cụ trích xuất. To get number from any position in the text string, use either this formula or the Extract tool. Đây là cách bạn có thể chia các chuỗi trong Excel bằng cách sử dụng các kết hợp khác nhau của các chức năng khác nhau. Như bạn thấy, các công thức không rõ ràng, vì vậy bạn có thể muốn tải xuống sổ làm việc của các tế bào phân chia mẫu Excel để kiểm tra chúng gần hơn. Nếu tìm ra các vòng xoắn phức tạp của các công thức excel không phải là nghề nghiệp yêu thích của bạn, bạn có thể thích phương pháp trực quan để phân chia các ô trong Excel, được thể hiện trong phần tiếp theo của hướng dẫn này. Cách phân chia các ô trong Excel với công cụ phân chia văn bảnMột cách khác để phân chia một cột trong Excel là sử dụng tính năng phân chia văn bản bao gồm với Ultimate Suite của chúng tôi cho Excel, cung cấp các tùy chọn sau:
Để làm cho mọi thứ rõ ràng hơn, chúng ta hãy xem xét kỹ hơn từng tùy chọn, mỗi lần một. Tách các tế bào theo ký tựChọn tùy chọn này bất cứ khi nào bạn muốn phân chia nội dung ô tại mỗi lần xuất hiện của ký tự được chỉ định.each occurrence of the specified character. Ví dụ này, chúng ta hãy lấy các chuỗi của mẫu kích thước màu vật phẩm mà chúng ta đã sử dụng trong phần đầu tiên của hướng dẫn này. Như bạn có thể nhớ, chúng tôi đã tách chúng thành 3 cột khác nhau bằng 3 công thức khác nhau. Và đây là cách bạn có thể đạt được kết quả tương tự trong 2 bước nhanh:
Mẹo. Nếu có thể có một số dấu phân cách liên tiếp trong một ô (ví dụ, nhiều hơn một ký tự không gian), hãy chọn các phân định liên tiếp điều trị làm một hộp. If there might be several successive delimiters in a cell (for example, more than one space character), select the Treat consecutive delimiters as one box. Xong! Nhiệm vụ yêu cầu 3 công thức và 5 chức năng khác nhau bây giờ chỉ mất vài giây và nhấp vào nút. Tách các ô theo chuỗiTùy chọn này cho phép bạn phân chia các chuỗi bằng bất kỳ sự kết hợp nào của các ký tự như một dấu phân cách. Về mặt kỹ thuật, bạn chia một chuỗi thành các phần bằng cách sử dụng một hoặc một số chuỗi con khác nhau làm ranh giới của mỗi phần.any combination of characters as a delimiter. Technically, you split a string into parts by using one or several different substrings as the boundaries of each part. Ví dụ: để phân chia một câu theo các liên kết "và" và "hoặc", mở rộng phân chia theo nhóm chuỗi và nhập chuỗi phân cách, một dòng trên mỗi dòng:Split by strings group, and enter the delimiter strings, one per line: Kết quả là, cụm từ được phân tách ở mỗi lần xuất hiện của từng dấu phân cách: Mẹo. Các ký tự "hoặc" cũng như "và" thường có thể là một phần của các từ như "Orange" hoặc "Andalusia", vì vậy hãy chắc chắn gõ một không gian trước và sau và hoặc để ngăn chặn các từ. The characters "or" as well as "and" can often be part of words like "orange" or "Andalusia", so be sure to type a space before and after and and or to prevent splitting words. Và ở đây một ví dụ khác, ngoài đời thực. Giả sử bạn đã nhập một cột ngày từ một nguồn bên ngoài, trông như sau: 5.1.2016 12:20 5.2.2016 14:50 Định dạng này không phải là thông thường cho Excel, và do đó, không có chức năng ngày nào sẽ nhận ra bất kỳ yếu tố ngày hoặc thời gian nào. Để phân chia ngày, tháng, năm, giờ và phút vào các ô riêng biệt, nhập các ký tự sau trong hộp chia đôi:
Nhấn nút chia và bạn sẽ ngay lập tức nhận được kết quả: Các ô phân chia theo mặt nạ (mẫu)Tách một ô bằng mặt nạ có nghĩa là tách một chuỗi dựa trên một mẫu.based on a pattern. Tùy chọn này rất tiện dụng khi bạn cần chia một danh sách các chuỗi đồng nhất thành một số yếu tố hoặc chất nền. Biến chứng là văn bản nguồn không thể được chia ở mỗi lần xuất hiện của một dấu phân cách nhất định, chỉ ở một số trường hợp cụ thể. Ví dụ sau đây sẽ làm cho mọi thứ dễ hiểu hơn. Giả sử bạn có một danh sách các chuỗi được trích xuất từ một số tệp nhật ký: Những gì bạn muốn là có ngày và giờ, nếu có, mã lỗi và chi tiết ngoại lệ trong 3 cột riêng biệt. Bạn không thể sử dụng một không gian làm dấu phân cách vì có khoảng trống giữa ngày và thời gian, sẽ xuất hiện trong một cột và có khoảng trắng trong văn bản ngoại lệ, cũng sẽ xuất hiện trong một cột. Giải pháp là phân tách một chuỗi theo mặt nạ sau:*Lỗi:*Ngoại lệ:* Trong đó dấu hoa thị (*) đại diện cho bất kỳ số lượng ký tự nào. Các dấu chấm (:) được bao gồm trong các phân định vì chúng tôi không muốn chúng xuất hiện trong các ô kết quả. Và bây giờ, mở rộng phần phân chia theo mặt nạ trên ngăn văn bản phân chia, nhập mặt nạ vào hộp Delimiters ENTER và nhấp vào Split:Split by mask section on the Split Text pane, type the mask in the Enter delimiters box, and click Split: Kết quả sẽ trông tương tự như thế này: Ghi chú. Chuỗi phân tách bằng mặt nạ là nhạy cảm trường hợp. Vì vậy, hãy chắc chắn nhập các ký tự trong mặt nạ chính xác khi chúng xuất hiện trong các chuỗi nguồn. Splitting string by mask is case-sensitive. So, be sure to type the characters in the mask exactly as they appear in the source strings. Một lợi thế lớn của phương pháp này là sự linh hoạt. Ví dụ: nếu tất cả các chuỗi ban đầu có giá trị ngày và thời gian và bạn muốn chúng xuất hiện trong các cột khác nhau, hãy sử dụng mặt nạ này: **Lỗi:*Ngoại lệ:* Được dịch sang tiếng Anh đơn giản, mặt nạ hướng dẫn bổ trợ để chia các chuỗi gốc thành 4 phần:
Tôi hy vọng bạn thích cách nhanh chóng và đơn giản này để chia dây trong Excel. Nếu bạn tò mò muốn thử, một phiên bản đánh giá có sẵn để tải xuống dưới đây. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới! Tải xuống có sẵnCông thức phân chia Excel (Tệp .xlsx) Ultimate Suite Phiên bản đầy đủ chức năng 14 ngày (Tệp .exe) Bạn cũng có thể quan tâmLàm thế nào để bạn trích xuất từ thứ n từ một chuỗi trong excel?Chuỗi: Chuỗi văn bản hoặc giá trị ô mà bạn muốn trích xuất một từ từ. N: Số lượng từ bạn muốn trích xuất ... Thay thế (A2, "", Rept ("", len (A2)))): .... . Làm cách nào để phân chia một chuỗi trong Excel của Delimiter?Thử nó!.. Chọn ô hoặc cột chứa văn bản bạn muốn chia .. Chọn Dữ liệu> Văn bản vào cột .. Trong văn bản chuyển đổi thành trình hướng dẫn cột, chọn dấu phân đoạn> Tiếp theo .. Chọn DELIMITERS cho dữ liệu của bạn..... Chọn Tiếp theo .. Chọn điểm đến trong bảng tính của bạn, nơi bạn muốn dữ liệu phân chia xuất hiện .. Làm cách nào để trích xuất từ thứ n đầu tiên đầu tiên từ chuỗi văn bản trong Excel?(1) chọn văn bản từ danh sách thả xuống loại công thức;(2) Bấm để làm nổi bật trích xuất từ thứ n trong ô trong hộp Danh sách Công thức;(3) trong hộp ô, chỉ định ô mà bạn sẽ trích xuất từ từ;(4) Trong hộp thứ n, chỉ định số.
Làm cách nào để trích xuất một ký tự thứ 4 trong Excel?Ví dụ: để có được 4 ký tự đầu tiên từ đầu chuỗi văn bản, hãy sử dụng công thức này:.. =LEFT(A2,4). =RIGHT(A2,4). =MID(A2,6,3). Cho dù chuỗi excel của bạn có bao nhiêu ký tự, công thức chỉ trích xuất văn bản trước dấu gạch nối đầu tiên:. =RIGHT(A2,LEN(A2)-SEARCH("-",A2)). |
