Trong nội dung bài viết này, mình đang hướng dẫn cho mình 4 Mẹo tách bóc Chữ với Số Ra trường đoản cú Chuỗi vào Excel rất có thể Bạn không biết thường xuyên gặp mặt hằng ngày trong đời sống. Hãy cùng mình khám phá ngay bên dưới nhé!
Ví dụ: Mình bao gồm bảng tài liệu như mặt dưới:
Yêu cầu: phụ thuộc số liệu trong bảng số liệu trên, các bạn sẽ tách phần đông chuỗi trong cột Emloyee Id ra thành 2 phần: tách bóc ra mã thành phần của nhân viên vào cột Emp Department và bóc ra số nhân viên cấp dưới ra vào vào cột Emloyee No.
Bạn đang xem: Lấy số trong chuỗi ký tự excel
Để làm được bài toán này, mình đang qua 4 mẹo tách bóc dữ liệu như sau:
Mẹo 1: tách chữ với số ra bằng cách sử dụng Flash Fill
Kể tự phiên bạn dạng 2013 trở đi, Excel có thêm một cách thức mới rất là tiện ích, sẽ là Flash Fill. Đây là một tính năng giúp người dùng điền những giá trị một phương pháp tự động, góp tiết kiệm thời hạn và tinh giảm phải sử dụng các hàm không nên thiết.
Để sử dụng công cầm này, các bạn sẽ thực hiện các bước sau:
Bước 1: vào cột Emp Department, trên ô đầu tiên, bạn gõ mã bộ phận của nhân viên trước tiên vào.
Tương tự, sống cột Emloyee No, bạn cũng gõ con số nhân viên nhờ vào ô thứ nhất trong cột Emloyee ID.
Bước 2: theo lần lượt tại ô hiển thị hiệu quả đầu tiên nghỉ ngơi cột Emp Department, các bạn nhấn phím Ctrl + E để hiển thị kết quả phía sau.
Tương tự, tại ô trước tiên trong cột Emloyee No, chúng ta nhấn phím Ctrl + E để hiển thị hiệu quả phía sau.
Mẹo 2: tách bóc chữ cùng số ra bằng cách sử dụng công thức
Để sử dụng cách này, trên ô đầu tiên trong cột Emp Department, bạn gõ cách làm sau: =LEFT(A2,MIN(IFERROR(FIND(0,1,2,3,4,5,6,7,8,9,A2),""))-1)
Giải thích: Find: dùng hàm này để tìm ra vị trí của các con chu kỳ lượt tự 0 -> 9. A2: Ô chứa ID trước tiên muốn tách bóc mã bộ phận nhân viên. IFERROR: áp dụng hàm này nhằm phòng trong trường đúng theo nếu tìm ko thấy những số lượng hoặc bị lỗi thì sẽ hiển thị ra dấu khoảng tầm trắng "". Min: vì chưng trong ngôi trường hợp cần sử dụng hàm FIND thì các vị trí như thế nào nào chứa những số lượng thì đều hiển thị ra. Tuy nhiên, mình chỉ muốn rước vị trí trước tiên xuất hiện những số lượng nên cần sử dụng hàm MIN để mang ra số vị trí nhỏ nhất. LEFT: dùng hàm này để bóc tách ra mã phần tử nhân viên từ bỏ ô ID. Cuối cùng trừ 1 nhằm chỉ tính con số mã cỗ phân nhân viên từ ô ID. Số lượng đó bắt buộc trước những số lượng như vào dữ liệu.
Tương tự, bản thân cũng bóc tách ra con số nhân viên vào cột Emloyee No, bạn cũng gõ: =MID(A2,MIN(IFERROR(FIND(0,1,2,3,4,5,6,7,8,9,A2),"")),100)
Giải thích: Find: dùng hàm này để tìm ra vị trí của những con số lần lượt từ bỏ 0 -> 9. A2: Ô cất ID trước tiên muốn tách bóc số lượng nhân viên. IFERROR: áp dụng hàm này để phòng vào trường hòa hợp nếu tìm không thấy những con số hoặc bị lỗi thì vẫn hiển thị ra dấu khoảng tầm trắng "". Min: vì chưng trong ngôi trường hợp cần sử dụng hàm FIND thì những vị trí như thế nào nào chứa những con số thì phần đa hiển thị ra. Mặc dù nhiên, tôi chỉ muốn rước vị trí thứ nhất xuất hiện tại những con số nên cần sử dụng hàm MIN để lấy ra số vị trí nhỏ nhất. MID: cần sử dụng hàm này để tách bóc ra con số nhân viên tự ô ID. 100: cần sử dụng giá trị 100 để ước lượng tương đối số kí từ bỏ sau đó.
Mẹo 3: bóc chữ và số ra bằng cách sử dụng VBA
Để sử dụng cách thức này, thứ nhất bạn phải truy cập vào giao diệnVBA bởi cách:
Tại trang tính chứa dữ liệu cần tách, bạn nhấn tổng hợp phím ALT + F11.
Hiện ra đồ họa VBA. Tiếp theo, bạn sẽ tạo module để cất code tách bóc dữ liệu bằng phương pháp vào thẻ Insert => chọn vào Module.
Hiển thị hình ảnh module, tại đây chúng ta gõ đoạn code sau:
Function Get
Number(Cell
Ref As String)Dim String
Length As Integer
String
Length = Len(Cell
Ref)For i = 1 khổng lồ String
Length
If Is
Numeric(Mid(Cell
Ref, i, 1)) Then Result = Result & Mid(Cell
Ref, i, 1)Next i
Get
Number = Result
End Function
Function Get
Text(Cell
Ref As String)Dim String
Length As Integer
String
Length = Len(Cell
Ref)For i = 1 to String
Length
If Not (Is
Numeric(Mid(Cell
Ref, i, 1))) Then Result = Result và Mid(Cell
Ref, i, 1)Next i
Get
Text = Result
End Function
Tiếp theo, chúng ta nhấn phím Run trên thanh công cụ hoặc dìm phím F5 để chạy đoạn code trên.
Sau khi chạy hoàn thành thì thoát bối cảnh VBA ra.
Ở cột Emp Department, ô trước tiên bạn gõ: =Get
Text(A2)
Giải thích: Hàm Get
Text dành được do mình tạo nên hàm từ bỏ Code VBA.
Tương tự, nhằm hiển thị cột Emloyee No, trên ô đầu tiên bạn gõ: =Get
Number(A2)
Giải thích: Hàm Get
Number có được do mình tạo ra hàm từ bỏ Code VBA.
Mẹo 4: tách bóc chữ với số ra bằng phương pháp sử dụng power Query
Trước khi áp dụng mẹo này, thì các bạn sẽ phải gửi dữ liệu của bạn sang thành bảng Table bằng phương pháp bôi đen vùng tài liệu muốn tách, kế tiếp chọn vào thẻ Insert => Table với nhấn Ok để xong xuôi việc sinh sản bảng.
Sau lúc 1 bảng được tạo, bây chừ tôi rất có thể sử dụng truy vấn vấn nguồn để đưa tất cả tài liệu này vào trình chỉnh sửa truy vấn nguồn. Kế tiếp mình sẽ bóc tách văn bản và số vào trình sửa đổi truy vấn nguồn. Vày vậy, để triển khai điều đó, mình vẫn đi mang đến thẻ Data, trên đây các bạn chọn vào From Table/Range.
Hiển thị ra đồ họa Power Query, tại đây các bạn sẽ bôi black vùng tài liệu cần tách bằng phương pháp chọn vào tiêu đề.
Tiếp theo, vào thẻ Home => Split Column => By non Digit lớn Digit
Sau lúc nhấn chọn xong, dữ liệu của người tiêu dùng đã được tách
Hoàn thành xong, thì các bạn sẽ tải dữ liệu về File Excel bằng cách vào thẻ Home => Close & Load to...
Trong bảng Import Data, các bạn chọn vào Existing worksheet và chọn vào vị trí mà bạn muốn hiển thị kết quả.
Nhấn OK để hoàn thành.
Lưu ý: Ở mẹo này thú vui ở chỗ khi chúng ta sửa thông tin trong cột tài liệu gốc thì bảng tài liệu mới tách sẽ từ bỏ cập nhật bằng phương pháp chọn ô bất cứ trong vùng dữ liệu mới tắt, nhận chuột cần và lựa chọn vào Refresh.
Vậy là mình đã hướng dẫn kết thúc cho bạn4 mẹo bóc tách chữ và số ra tự chuỗi trong excel có thể bạn không biết. Hy vọng bài viết này để giúp ích mang lại bạn. Ví như thấy hay đừng quên đánh giá nội dung bài viết bên dưới nhé! Trân trọng.
Sử dụng hàm bóc tách số ra khỏi chuỗi trong Excel giúp bạn quản lý dữ liệu, ID của người tiêu dùng một cách dễ dàng. Vậy hàm này được sử dụng như thế nào? Mời bạn đọc xem thêm các nội dung cụ thể thông qua bài xích viết.
1. Hàm bóc số thoát ra khỏi chuỗi vào Excel
Ví dụ: mang lại bảng tài liệu sau, yêu cầu tách mã số sinh viên và tên sv thành những dữ liệu riêng biệt.
- bước 1: Áp dụng công thức =FIND(0;1;2;3;4;5;6;7;8;9;C4). Hàm này chúng ra sẽ tìm từ số 0 - 9 trong chuỗi C4.
Tách số ra khỏi chuỗi vào Excel - Hình 1
- cách 2: Kéo thả chuột để hiển thị tác dụng ở các ô phíasau.
Tách số thoát khỏi chuỗi vào Excel - Hình 2
- cách 3: Đối với mọi ô tính hiện nay lỗi VALUE, chúng ta có thể nhấn đúp loài chuột vào ô đầu tiên và thêm &"0123456789" vào thời gian cuối hàm. Làm việc này giúp đỡ bạn nối thêm chuỗi từ bỏ 0-9. Như vậy, bạn sẽ có bí quyết như sau: =FIND(1;2;3;4;5;6;7;8;9;C4&"0123456789")
Tách số thoát ra khỏi chuỗi trong Excel - Hình 3
- cách 4: Thêm hàm Min ở phía ngoại trừ và nhấn Enter. bây giờ kết trái trả về là địa điểm tìm thấy cùng là vị trí bé dại nhất. Tức là trong chuỗi C4 từ 0-9 để tìm kiếm được vị trí trước tiên và trả về.
Tách số thoát ra khỏi chuỗi trong Excel - Hình 4
- cách 5: Để xem biện pháp chạy của hàm này, bạn chọn vào ô vừa tìm địa chỉ số và nhấn chọn vào Formulas -> chọn Evaluate Arrows.
Tách số thoát khỏi chuỗi trong Excel - Hình 5
2. Hàm Right tách số ra khỏi chuỗi trong Excel
Ngoài ra, để tách bóc số thoát khỏi chuỗi vào Excel, bạn áp dụng hàm RIGHT.
Công thức:=RIGHT(chuỗi bạn phải tách; LEN(chuỗi bạn cần tách) - Vị trí ban đầu số + 1)
Ví dụ minh họa: cần sử dụng hàm RIGHT để tách số ra khỏi chuỗi trong Excel
- bước 1: Áp dụng công thức: =RIGHT(C4;LEN(C4)-H4+1).
Tách số thoát ra khỏi chuỗi trong Excel - Hình 6
Khi áp dụng vào lấy ví dụ trên, ta được hàm như sau:
=RIGHT(chuỗi bắt buộc tách;LEN(chuỗi yêu cầu tách);+1-MIN(FIND(0;1;2;3;4;5;6;7;8;9,chuỗicần bóc tách & "0123456789"))).
Tách số thoát ra khỏi chuỗi trong Excel - Hình 7
3. Bóc tách số thoát ra khỏi chuỗi vào Excel bằng CODE VBA
Ví dụ: cho bảng dữ liệu sau, yêu cầu bóc tách số chứng minh thư của từng người ra khỏi chuỗi trong cột thông tin.
Tách số ra khỏi chuỗi bằng CODE VBA - Hình 8
- bước 1: xuất hiện sổ Microsoft Visual Basic for Applications bằng tổng hợp phím alt + F11. Tiếp theo sau chọn Insert -> chọn Module.
Tách số thoát khỏi chuỗi bởi CODE VBA - Hình 9
- bước 2: sao chép toàn bộ đoạn code sau bằng tổng hợp phím CTRL + C.
- bước 3: Dán đoạn code bởi phím Ctrl + V vào cửa sổ Module.
Xem thêm: Top 7 phần mềm check ngữ pháp tiếng anh tốt nhất, kiểm tra ngữ pháp
Tách số thoát ra khỏi chuỗi bởi CODE VBA - Hình 10
- cách 4: trở lại trang tính, kế tiếp áp dụng hàm: = Extract
Number(B2)
- bước 5: xào nấu công thức mang đến các đối tượng trong bảng và nhận được công dụng như sau:
Tách số ra khỏi chuỗi bằng CODE VBA - Hình 11
4. Tổng kết
Qua bài viết trên, cdvhnghean.edu.vn đã thuộc bạn khám phá về hàm bóc số thoát ra khỏi chuỗi vào Excel. Ngoài các kiến thức trên, bạn đọc hoàn toàn có thể tham khảo các khóa huấn luyện và đào tạo tin học văn phòng Online bên trên cdvhnghean.edu.vn để nâng cao kỹ năng Excel của mình.