Làm cách nào để tách số ký tự trong văn bản trong excel?
|
Đôi khi bạn có thể có dữ liệu văn bản và số trong cùng một ô và bạn có thể cần tách phần văn bản và phần số trong các ô khác nhau Show
Mặc dù không có phương pháp sẵn có để thực hiện điều này một cách cụ thể, nhưng có một số tính năng và công thức Excel mà bạn có thể sử dụng để thực hiện việc này Trong hướng dẫn này, tôi sẽ chỉ cho bạn 4 cách đơn giản và dễ dàng để tách văn bản và số trong Excel Chúng ta hãy đi đến đó Hướng dẫn này bao gồm Tách văn bản và số bằng Flash FillDưới đây tôi có dữ liệu nhân viên trong cột A, trong đó một số bảng chữ cái đầu tiên cho biết bộ phận của nhân viên và các số sau nó cho biết mã số nhân viên Từ dữ liệu này, tôi muốn tách phần văn bản và phần số và chia chúng thành hai cột riêng biệt (cột B và C) Phương pháp đầu tiên mà tôi muốn chỉ cho bạn để tách văn bản và số trong Excel là sử dụng Flash Fill Flash Fill (được giới thiệu trong Excel 2013) hoạt động bằng cách xác định các mẫu dựa trên đầu vào của người dùng Vì vậy, nếu tôi nhập kết quả dự kiến vào cột B theo cách thủ công, Flash Fill sẽ cố gắng giải mã mẫu và cung cấp cho tôi kết quả cho tất cả các ô trong cột đó Dưới đây là các bước tách phần văn bản ra khỏi ô và đưa vào cột B
Các bước trên sẽ trích xuất phần văn bản từ các ô trong cột A và cung cấp cho bạn kết quả trong cột B Lưu ý rằng trong một số trường hợp, Flash Fill có thể không xác định được đúng mẫu. Trong những trường hợp như vậy, tốt nhất bạn nên nhập kết quả dự kiến vào hai hoặc ba ô, sử dụng Fill Handle để điền vào toàn bộ cột, sau đó sử dụng Flash Fill trên dữ liệu này Bạn có thể làm theo quy trình tương tự để trích xuất các số trong cột C. Tất cả những gì bạn cần làm là nhập kết quả dự kiến vào ô C2 (bước 2 trong quy trình đã trình bày ở trên) Lưu ý rằng kết quả bạn nhận được từ Flash Fill là tĩnh và sẽ không cập nhật trong trường hợp bạn thay đổi dữ liệu gốc trong cột A. Nếu bạn muốn kết quả là động, bạn có thể sử dụng phương thức công thức được trình bày tiếp theo Tách văn bản và số bằng công thứcDưới đây tôi có dữ liệu nhân viên ở cột A và tôi muốn sử dụng công thức để chỉ trích xuất phần văn bản và đặt nó vào cột B và trích xuất phần số và đặt nó vào cột C Vì dữ liệu không nhất quán (tôi. e. , các chữ cái trong mã phòng ban và các số trong mã số nhân viên không có độ dài nhất quán), tôi không thể sử dụng hàm LEFT hoặc RIGHT để chỉ trích xuất phần văn bản hoặc chỉ phần số Dưới đây là công thức sẽ chỉ trích xuất phần văn bản từ bên trái =LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),""))-1)Và dưới đây là công thức sẽ trích xuất tất cả các số từ bên phải =MID(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),"")),100)Công thức này hoạt động như thế nào? Trước tiên hãy để tôi giải thích công thức mà chúng tôi sử dụng để tách phần văn bản ở bên trái =LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),””))-1) Phần TÌM trong công thức tìm vị trí các chữ số từ 0 đến 9 trong ô A2. Trường hợp tìm thấy chữ số đó trong ô A2 thì trả về vị trí của chữ số đó, còn trường hợp không tìm được chữ số đó thì trả về giá trị lỗi (#VALUE. ) Đối với ô A2 thì cho kết quả như hình bên dưới {#GIÁ TRỊ. ,4,#VALUE. ,#GIÁ TRỊ. ,#GIÁ TRỊ. ,6,#VALUE. ,5,#VALUE. ,#GIÁ TRỊ. }
Công thức TÌM này sau đó được bao bọc bên trong hàm IFERROR, hàm này sẽ loại bỏ tất cả các lỗi giá trị nhưng để lại các số Kết quả của nó trông giống như hình dưới đây {“”,4,””,””,””,6,””,5,””,””} Sau đó, hàm MIN duyệt qua kết quả trên và cho chúng ta giá trị nhỏ nhất từ các kết quả. Vì mỗi số trong mảng đại diện cho vị trí của số tương ứng đó, nên giá trị tối thiểu cho chúng ta biết vị trí bắt đầu của giá trị số trong ô Bây giờ chúng ta đã biết vị trí bắt đầu của các giá trị số, tôi đã sử dụng hàm LEFT để trích xuất mọi thứ trước vị trí này (có thể là tất cả văn bản trong ô) Tương tự, bạn có thể sử dụng cùng một công thức với một điều chỉnh nhỏ để trích xuất tất cả các số sau văn bản. Để trích xuất các số, nơi chúng ta biết vị trí bắt đầu của chữ số đầu tiên, hãy sử dụng hàm MID để trích xuất mọi thứ bắt đầu từ vị trí đó Và điều gì sẽ xảy ra nếu tình huống đảo ngược – khi chúng ta có số trước và văn bản sau và chúng tôi muốn tách số và văn bản thì sao? Bạn vẫn có thể sử dụng logic tương tự với một thay đổi nhỏ – thay vì tìm giá trị nhỏ nhất cho chúng ta vị trí của chữ số đầu tiên trong ô, bạn cần sử dụng hàm MAX để tìm vị trí của chữ số cuối cùng trong ô này. Sau khi có nó, bạn có thể sử dụng lại hàm LEFT hoặc hàm MID để tách các số và văn bản Phân tách văn bản và số bằng VBA (Chức năng tùy chỉnh)Mặc dù bạn có thể sử dụng các công thức hiển thị ở trên để tách văn bản và số trong một ô và trích xuất chúng vào các ô khác nhau – nếu đây là việc bạn cần làm khá thường xuyên, bạn cũng có tùy chọn để tạo hàm tùy chỉnh của riêng mình bằng VBA Lợi ích của việc tạo hàm của riêng bạn là nó sẽ dễ sử dụng hơn rất nhiều (chỉ với một hàm chỉ nhận một đối số) Bạn cũng có thể lưu mã VBA chức năng tùy chỉnh này vào Sổ làm việc Macro Cá nhân để nó có sẵn trong tất cả các sổ làm việc Excel của bạn Bên dưới mã VBA có thể tạo một hàm 'GetNumber' sẽ lấy tham chiếu ô làm đối số đầu vào, trích xuất tất cả các số trong ô và đưa ra kết quả 'Code created by Sumit Bansal from https://trumpexcel.com 'This code will create a function that can separate numbers from a cell Function GetNumber(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumber = Result End Function Và bên dưới mã VBA sẽ tạo một hàm khác 'GetText' sẽ lấy tham chiếu ô làm đối số đầu vào và cung cấp cho bạn tất cả văn bản từ ô đó 'Code created by Sumit Bansal from https://trumpexcel.com ''This code will create a function that can separate text from a cell Function GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1) Next i GetText = Result End Function Dưới đây là các bước để thêm mã này vào sổ làm việc excel của bạn để chức năng này có sẵn cho bạn sử dụng trong trang tính
Với các bước trên chúng ta đã thêm mã hàm tùy chỉnh vào workbook Excel Giờ đây, bạn có thể sử dụng các hàm =GETNUMBER hoặc =GETTEXT giống như bất kỳ hàm trang tính nào khác Lưu ý – Khi bạn có mã macro trong cửa sổ mã mô-đun, bạn cần lưu tệp dưới dạng tệp Đã bật Macro (với. phần mở rộng xlsm thay vì. phần mở rộng xlsx) Nếu bạn thường có nhu cầu tách các số văn bản khỏi các ô trong Excel, sẽ hiệu quả hơn nếu bạn sao chép các mã VBA này để tạo các hàm tùy chỉnh này và lưu chúng trong Sổ làm việc Macro Cá nhân của bạn Bạn có thể tìm hiểu cách tạo và sử dụng Sổ làm việc Macro cá nhân trong hướng dẫn này mà tôi đã tạo trước đó Sau khi bạn có các hàm này trong Sổ làm việc Macro Cá nhân, bạn sẽ có thể sử dụng chúng trên bất kỳ sổ làm việc Excel nào trên hệ thống của mình Một điều quan trọng cần nhớ khi sử dụng các hàm được lưu trữ trong Sổ làm việc Macro Cá nhân – bạn cần thêm tiền tố vào tên hàm bằng =PERSONAL. XLSB. Ví dụ: nếu tôi muốn sử dụng hàm GETNUMBER trong sổ làm việc trong Excel và tôi đã lưu mã cho hàm đó trong sổ làm việc macro bưu chính, tôi sẽ phải sử dụng =PERSONAL. XLSB. NHẬN SỐ(A2) Tách Văn bản và Số bằng Power QueryPower Query đang dần trở thành tính năng yêu thích của tôi trong Excel Nếu bạn đã sử dụng Power Query như một phần trong quy trình làm việc của mình và bạn có một tập dữ liệu mà bạn muốn tách văn bản và số thành các cột riêng biệt, thì Power Query sẽ thực hiện điều đó sau vài cú bấm chuột Khi bạn có dữ liệu trong Excel và bạn muốn sử dụng Power Query để chuyển đổi dữ liệu đó, một điều kiện tiên quyết là chuyển đổi dữ liệu đó thành Bảng Excel (hoặc một dải ô đã đặt tên) Dưới đây tôi có một Bảng Excel chứa dữ liệu mà tôi muốn tách các phần văn bản và số thành các cột riêng biệt Dưới đây là các bước để làm điều này
Các bước trên sẽ lấy dữ liệu từ Bảng Excel mà chúng tôi có ban đầu, sử dụng Power Query để tách cột và tách phần văn bản và phần số thành hai cột riêng biệt, sau đó trả lại cho chúng tôi đầu ra trong một trang tính mới trong cùng một sổ làm việc Lưu ý rằng chúng tôi đã sử dụng tùy chọn 'By Non-Digit to Digit' trong bước 7, điều đó có nghĩa là mỗi khi có thay đổi từ ký tự không phải chữ số thành chữ số, một phần chia sẽ được thực hiện. Nếu bạn có một tập dữ liệu trong đó các số được theo sau bởi văn bản đầu tiên, bạn có thể sử dụng tùy chọn 'Theo chữ số đến không phải chữ số' Bây giờ hãy để tôi nói cho bạn biết phần hay nhất về phương pháp này. Bảng Excel gốc của bạn (là nguồn dữ liệu) được kết nối với Bảng Excel đầu ra Vì vậy, nếu bạn thực hiện bất kỳ thay đổi nào trong dữ liệu gốc của mình, bạn không cần phải lặp lại toàn bộ quy trình. Bạn có thể chỉ cần đi đến bất kỳ ô nào trong Bảng Excel đầu ra, nhấp chuột phải rồi nhấp vào Làm mới Power query sẽ chạy ở phía sau, kiểm tra toàn bộ nguồn dữ liệu gốc, thực hiện tất cả các phép biến đổi mà chúng ta đã thực hiện ở các bước trên và cập nhật dữ liệu kết quả đầu ra của bạn Đây là nơi Power Query thực sự tỏa sáng. Nếu có một tập hợp các phép biến đổi mà bạn thường cần thực hiện trên một tập dữ liệu, thì bạn có thể sử dụng Power Query để thực hiện phép biến đổi đó một lần và thiết lập quy trình. Excel sẽ tạo kết nối giữa nguồn dữ liệu gốc và dữ liệu đầu ra kết quả và ghi nhớ tất cả các bước bạn đã thực hiện để chuyển đổi dữ liệu này Bây giờ, nếu bạn nhận được một tập dữ liệu mới, bạn chỉ cần đặt nó vào vị trí của dữ liệu ban đầu và làm mới truy vấn và bạn sẽ nhận được kết quả sau vài giây. Ngoài ra, bạn có thể chỉ cần thay đổi nguồn trong Power Query từ bảng Excel hiện có sang một số Bảng Excel khác (trong cùng hoặc sổ làm việc khác) Vì vậy, đây là bốn cách đơn giản bạn có thể sử dụng để tách số và văn bản trong Excel. nếu đây là hoạt động thỉnh thoảng, bạn nên sử dụng Flash Fill hoặc phương pháp công thức Và trong trường hợp đây là việc bạn cần làm khá thường xuyên, thì tôi khuyên bạn nên xem xét phương pháp VBA nơi chúng tôi đã tạo hàm tùy chỉnh hoặc phương pháp Power Query Có cách nào để tách văn bản khỏi số trong Excel không?Để tách văn bản và số, bạn có thể sử dụng công thức dựa trên hàm FIND, hàm MIN và hàm LEN với hàm LEFT hoặc RIGHT, depending on whether you want to extract the text or the number.
Công thức tách văn bản trong Excel là gì?Trong cột trống đầu tiên, viết =SPLIT(B1,"-") , với B1 là ô bạn muốn tách và . (Nếu bạn thấy lỗi #REF. trong ô của mình, bạn sẽ cần tạo nhiều cột hơn. )
Phím tắt trong Excel để tách văn bản và số là gì?Giới thiệu về Tách văn bản trong Excel. Để Tách Văn bản trong Excel, chúng ta có thể sử dụng tùy chọn Văn bản thành Cột, có sẵn trong tab menu Dữ liệu trong Công cụ Dữ liệu. Chúng tôi cũng có thể sử dụng tùy chọn này bằng các phím tắt ALT + A + E đồng thời khi chúng tôi chọn dữ liệu mà chúng tôi muốn tách. |
