Cách tách chuỗi số trong excel

Microsoft Excel là một công cụ phổ biến để tổ chức và phân tích dữ liệu. Thông thường, chúng ta có thể cần tách các số trong Excel, do đó, biết một vài phương pháp có thể tiết kiệm rất nhiều thời gian và công sức. Bài viết này trình bày 5 cách hữu ích để tách các số bằng công thức trong Excel. Ngoài ra, chúng ta cũng sẽ khám phá cách tách số và cách tách số thành hai cột

Mục lục ẩn

Tải sách bài tập thực hành

5 Cách Tách Số Bằng Công Thức Trong Excel

Phương pháp 1. Sử dụng công thức Excel kết hợp

1. 1 số riêng biệt sau văn bản

1. 2 số riêng biệt đứng trước văn bản

1. 3 Số Thập Phân Riêng Biệt

Phương pháp 2. Sử dụng tính năng Flash Fill

Phương pháp 3. Sử dụng tính năng Text to Columns

3. 1 Áp dụng tùy chọn dấu phân cách

3. 2 Sử dụng tính năng chiều rộng cố định

Phương pháp 4. Sử dụng Power Query Editor

Phương pháp 5. Áp dụng mã VBA

Cách chia số bằng công thức trong Excel

Cách chia số thành hai cột trong Excel

phần thực hành

Phần kết luận

Những bài viết liên quan

Tải sách bài tập thực hành

Tách các số bằng công thức. xlsm

5 Cách Tách Số Bằng Công Thức Trong Excel

Tại thời điểm này, hãy xem xét Danh sách bộ dữ liệu Phiên bản Microsoft Office được hiển thị trong B4. Các ô B13, hiển thị các Phiên bản khác nhau của Microsoft Office bắt đầu từ 1995 đến 2019. Ở đây, chúng tôi chỉ muốn trích xuất các số từ mỗi ô, do đó, hãy cùng quan sát và tìm hiểu chi tiết về từng phương pháp

Ở đây, chúng tôi đã sử dụng phiên bản Microsoft Excel 365, bạn có thể sử dụng bất kỳ phiên bản nào khác tùy theo sự thuận tiện của bạn

Phương pháp 1. Sử dụng công thức Excel kết hợp

Đối với một điều, các hàm là trái tim và linh hồn của Excel và ở đây chúng tôi sẽ kết hợp các hàm khác nhau để chỉ trích xuất các số từ mỗi ô. Vì vậy, hãy bắt đầu

1. 1 số riêng biệt sau văn bản

Trước hết, hàm FIND tìm kiếm các số trong mảng đã cho và hàm MIN trả về số nhỏ nhất từ ​​mảng đã cho. Ngoài ra, hàm LEN trả về tổng độ dài của chuỗi văn bản và trừ vị trí của số khỏi tổng độ dài sẽ cho hàm RIGHT tách các số bằng công thức Excel

📌 Các bước.

• Ngay từ đầu, hãy chuyển đến ô C5 >> nhập công thức được cung cấp bên dưới

=RIGHT(B5,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))+1)

Ở đây, ô B5 đề cập đến văn bản Microsoft Office 95

Phân tích công thức

• FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″) → trả về vị trí bắt đầu của một chuỗi văn bản trong một chuỗi văn bản khác. Ở đây, {0,1,2,3,4,5,6,7,8,9} là đối số find_text trong khi B5&”0123456789″ là đối số within_text. Ở đây, hàm FIND trả về vị trí của các giá trị số trong chuỗi văn bản
• MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″)) → trả về số nhỏ nhất trong một tập giá trị. Trong trường hợp này, FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″) là đối số number_1 và hàm MIN trả về giá trị nhỏ nhất trong mảng này
  • Đầu ra → 18
• LEN(B5) → trả về số ký tự trong một chuỗi văn bản. Ở đây, ô B5 là đối số văn bản mang lại giá trị 19
  • Đầu ra → 19
• LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″))+1 → trở thành
  • 19 – 18 + 1 → 2
• RIGHT(B5,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″))+1) → trở thành
  • RIGHT(B5,2)  → trả về số ký tự đã chỉ định từ cuối chuỗi. Ở đây, ô B5 là đối số văn bản trong khi 2 là đối số num_chars sao cho hàm trả về 2 ký tự từ bên phải
  •   Đầu ra → 95

• Sau đó, sử dụng Công cụ xử lý điền để sao chép công thức vào các ô bên dưới

Cuối cùng, kết quả sẽ giống như hình dưới đây

Đọc thêm. Cách trích xuất nhiều số từ chuỗi trong Excel (6 phương pháp)

1. 2 số riêng biệt đứng trước văn bản

Ngoài ra, chúng ta cũng có thể sử dụng hàm SUBSTITUTE để thay thế văn bản hiện có bằng văn bản mới và hàm LEN để xác định độ dài của nó và tổng độ dài của chuỗi văn bản. Sau đây, hãy áp dụng hàm SUM để tính tổng các giá trị số, hàm này cung cấp số lượng ký tự cho hàm LEFT để tách các số bằng công thức Excel

📌 Các bước.

• Ban đầu, di chuyển đến ô C5 >> nhập biểu thức được cung cấp bên dưới

=LEFT(B5,SUM(LEN(B5)-LEN(SUBSTITUTE(B5,{"0","1","2","3","4","5","6","7","8","9"},""))))

Trong biểu thức này, ô B5 biểu thị văn bản 95 Microsoft Office

Phân tích công thức

• SUBSTITUTE(B5,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},””) → . Ở đây, B5 đề cập đến đối số văn bản trong khi Tiếp theo, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,
• LEN(SUBSTITUTE(B5,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},”” . Ở đây, đầu ra từ hàm SUBSTITUTE là đối số văn bản
• SUM(LEN(B5)-LEN(SUBSTITUTE(B5,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″, . Ở đây, hàm SUM trả về tổng các giá trị số
  • Đầu ra → 2
• TRÁI(B5,SUM(LEN(B5)-LEN(SUBSTITUTE(B5,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,
  • LEFT(B5,2)  → trả về số ký tự đã chỉ định từ đầu chuỗi. Ở đây, ô B5 là đối số văn bản trong khi 2 là đối số num_chars sao cho hàm trả về 2 ký tự từ phía bên trái
  •   Đầu ra → 95

Cuối cùng, đầu ra của bạn sẽ giống như hình dưới đây

1. 3 Số Thập Phân Riêng Biệt

Theo cách tương tự, chúng ta cũng có thể tách các số thập phân bằng công thức Excel. Bây giờ, quá trình này tương tự như phương pháp trước đó, vì vậy, chỉ cần làm theo

📌 Các bước.

• Trước tiên, hãy chèn phương trình sau vào ô C5

=RIGHT(B5,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))+1)

Trong trường hợp này, ô B5 đại diện cho giá trị văn bản của Microsoft Office 95. 1

Cuối cùng, sau khi hoàn thành bước trên, đầu ra sẽ giống như ảnh chụp màn hình bên dưới

Đọc thêm. Cách trích xuất số sau một văn bản cụ thể trong Excel (2 cách phù hợp)

Phương pháp 2. Sử dụng tính năng Flash Fill

Nếu việc sử dụng các công thức phức tạp không phù hợp với bạn thì phương pháp tiếp theo của chúng tôi có thể là câu trả lời mà bạn đang tìm kiếm. Sau đây, chúng ta sẽ áp dụng tính năng Flash Fill của Excel để tách nhanh số ra khỏi văn bản.
Giả sử chúng ta có tập dữ liệu Danh sách tên nhân viên và ID được hiển thị trong ô B4. Các ô B13 chứa ID và Tên. Trong trường hợp này, chúng tôi muốn tách số ID và Họ và Tên tương ứng. Vì vậy, chỉ cần làm theo các bước hiển thị dưới đây.

📌 Các bước.

• Trước tiên, hãy nhập thủ công số ID đầu tiên là 1214 vào ô C5 để Excel có thể nhận dạng mẫu
• Tiếp theo, tìm đến ô C6 và nhập 2 số đầu tiên của 1215 và Excel sẽ hiển thị bản xem trước kết quả tự động điền >> nhấn phím ENTER

Sau đó, kết quả sẽ giống như hình ảnh được đưa ra dưới đây

Phương pháp 3. Sử dụng tính năng Text to Columns

Không còn nghi ngờ gì nữa, tính năng Chuyển văn bản thành cột của Excel trở nên tiện dụng khi tách các số trong một ô thành nhiều ô. Thông thường, có hai cách để áp dụng tính năng Văn bản thành Cột, vì vậy, hãy xem cách thức hoạt động của từng kỹ thuật

3. 1 Áp dụng tùy chọn dấu phân cách

Tùy chọn dấu phân cách có thể chia dữ liệu văn bản thành các cột riêng biệt dựa trên khoảng trắng, tab, dấu chấm phẩy, v.v. ký tự hiện diện trong văn bản

📌 Các bước.

• Đầu tiên, chọn B5. Các ô B13 >> tiếp tục đến tab Dữ liệu >> nhấp vào tùy chọn Văn bản thành Cột

Ví dụ, B5. Các ô B13 trỏ đến các cột ID và Tên

Bây giờ, thao tác này sẽ mở trình hướng dẫn Chuyển văn bản thành cột

• Thứ hai, chọn tùy chọn Phân cách >> nhấn nút Tiếp theo

• Thứ ba, chèn dấu kiểm cho dấu cách Dấu cách >> nhấn nút Tiếp theo

• Thứ tư, nhập một ô Đích theo ý muốn của bạn, ở đây là ô C5 >> bấm nút Kết thúc

Do đó, kết quả sẽ xuất hiện trong hình dưới đây

3. 2 Sử dụng tính năng chiều rộng cố định

Nhân dịp này, chúng ta hãy xem xét bộ dữ liệu Danh sách Phòng ban và ID Nhân viên được hiển thị trong B4. Các ô C14, lần lượt chứa ID Bộ phận và Nhân viên. Trong tình huống này, chúng tôi muốn tách ID nhân viên thành Mã và Số, vì tất cả các số ID đều bao gồm 2 chữ số. Tùy chọn Chiều rộng cố định là ứng cử viên hoàn hảo cho công việc này

📌 Các bước.

• Để bắt đầu, hãy chọn C5. Ô C14 >> chuyển sang tab Data >> nhấn tùy chọn Text to Columns

Cụ thể là C5. Các ô C14 trỏ đến cột ID nhân viên

Ngay lập tức, trình hướng dẫn Chuyển văn bản thành cột bật ra

• Không lâu sau, chọn tùy chọn Fixed Width >> nhấn nút Next

• Lần lượt Click chuột trái để chèn ngắt cột sau Code và ID >> click nút Next

• Tiếp theo chọn ô Đích, ví dụ ô D5 >> nhấn nút Kết thúc

Cuối cùng, kết quả cuối cùng sẽ giống như ảnh chụp màn hình được cung cấp bên dưới

Phương pháp 4. Sử dụng Power Query Editor

Power Query là một tính năng thường bị bỏ qua của Excel, mặc dù khả năng tổ chức và phân tích dữ liệu nhanh chóng và hiệu quả. Trong phần sau, chúng ta sẽ thảo luận về cách chúng ta có thể tách các số trong Excel chỉ bằng một vài cú nhấp chuột. Vì vậy, hãy xem nó hoạt động

📌 Các bước.

• Để bắt đầu, hãy chọn B4. Các ô B13 >> nhấn phím tắt CTRL + T để chèn Bảng Excel >> nhấn OK

• Tiếp theo, đi đến ô B4 >> trong tab Data, nhấp vào tùy chọn From Table/Range

Không lâu sau, cửa sổ Power Query Editor xuất hiện

• Từ thời điểm này, di chuyển đến tab Chuyển đổi >> nhấn menu thả xuống Cột chia >> chọn tùy chọn Theo không phải chữ số thành chữ số

• Sau đó, Bấm đúp vào tiêu đề cột để đổi tên chúng >> nhấn tùy chọn Đóng & Tải để thoát khỏi cửa sổ Power Query

Cuối cùng, đầu ra sẽ giống như hình ảnh được mô tả bên dưới

Phương pháp 5. Áp dụng mã VBA

Nếu bạn thường xuyên cần tách các số bằng công thức Excel, thì bạn có thể xem xét Mã VBA bên dưới. Thật đơn giản và dễ dàng;

📌 Các bước.

• Đầu tiên, điều hướng đến tab Nhà phát triển >> nhấp vào nút Visual Basic

Bây giờ, thao tác này sẽ mở Trình soạn thảo Visual Basic trong một cửa sổ mới

• Thứ hai, chuyển đến tab Chèn >> chọn Mô-đun

Để tiện cho việc tham khảo, bạn có thể copy đoạn code từ đây và dán vào cửa sổ như hình bên dưới

Function SplitNumAndText(str As String, op As Boolean)
num = ""
   txt = ""
   For i = 1 To Len(str)
       If IsNumeric(Mid(str, i, 1)) Then
            num = num & Mid(str, i, 1)
Else
            txt = txt & Mid(str, i, 1)
     End If
    Next i
If op = True Then
    SplitNumAndText = num
Else
    SplitNumAndText = txt
End If
End Function

⚡ Phân tích mã.

Bây giờ mình sẽ giải thích code VBA dùng để tách số. Trong trường hợp này, mã được chia thành 2 bước

• Trong phần đầu tiên, hãy khai báo hàm SplitNumAndText trong đó đối số đầu tiên là một chuỗi và đối số thứ hai là kiểu Boolean
• Tiếp theo, lặp qua toàn bộ chuỗi bằng vòng lặp For và câu lệnh If để kiểm tra xem giá trị có phải là số hay không
• Trong phần sau, hãy sử dụng câu lệnh If thứ hai để trả về đầu ra (Số hoặc Văn bản) dựa trên đối số thứ hai. Nói một cách đơn giản, truyền 0 làm đối số thứ hai sẽ trích xuất Văn bản trong khi truyền 1 trả về Số từ chuỗi đã cho

• Thứ ba, đóng cửa sổ VBA >> tìm đến ô C5 >> chèn hàm bên dưới

=SplitNumAndText(B5,1)

Ở đây, ô B5 đề cập đến giá trị ID và Tên của 1214 John Connor

Cuối cùng, điều này sẽ mang lại kết quả như trong ảnh chụp màn hình bên dưới

Đọc thêm. Cách trích xuất các số cụ thể từ một ô trong Excel (11 cách)

Cách chia số bằng công thức trong Excel

Một tình huống phổ biến khác liên quan đến việc tách số bằng công thức. Bây giờ, điều này có thể được thực hiện bằng cách kết hợp các hàm MID và COLUMN. Ở đây, hàm MID trả về các ký tự từ giữa chuỗi văn bản trong khi hàm COLUMN cung cấp tham chiếu ô của chuỗi văn bản

📌 Các bước.

• Ban đầu, nhảy đến ô D5 >> chèn biểu thức vào thanh công thức

=MID($C5,COLUMN()-(COLUMN($D5)- 1),1)

Trong biểu thức trên, ô C5 cho biết số ID 1214

Phân tích công thức

• COLUMN()-(COLUMN($D5)- 1) → trả về số cột của tham chiếu ô
  • 4 – (4-1) → 1
• MID($C5,COLUMN()-(COLUMN($D5)- 1),1) → trở thành
  • MID($C5,1,1)  → trả về các ký tự từ giữa chuỗi văn bản, cho trước vị trí bắt đầu và độ dài. Ở đây, ô C5 là đối số văn bản, 1 là đối số start_num và 1 là đối số num_chars sao cho hàm trả về ký tự đầu tiên từ phía bên trái
  •   Đầu ra → 1

• Tiếp theo, kéo công cụ Fill Handle qua các hàng để chèn các số còn lại

• Lần lượt, chọn D5. ô G5 và sử dụng lại công cụ Fill Handle để hoàn thành bảng

Cuối cùng, đầu ra cuối cùng sẽ giống như ảnh chụp màn hình sau

Cách chia số thành hai cột trong Excel

Nếu bạn muốn tách các số thành hai cột thì sao? . Bây giờ, hãy cho phép chúng tôi chứng minh quy trình theo các bước bên dưới.
Ví dụ: giả sử bộ dữ liệu Danh sách tên và ID quốc gia được hiển thị trong B4. Ô C13 chứa Tên và số CMND của một nhóm người. Phải thừa nhận rằng 2 chữ số cuối của Chứng minh nhân dân đại diện cho Năm sinh của mỗi người và chúng tôi muốn tách 4 chữ số đầu và 2 chữ số cuối thành 2 cột riêng biệt.

📌 Các bước.

• Ở nơi đầu tiên, chọn C5. Các ô C13 >> điều hướng đến tab Data >> nhấp vào tùy chọn Text to Columns

• Sau đó, chọn tùy chọn Chiều rộng cố định >> nhấn nút Tiếp theo

• Sau đó Click chuột trái để chèn ngắt cột sau Năm sinh >> click nút Next

• Lần lượt chọn ô Đích, như ô D5 >> nhấn nút Kết thúc

Sau đó, kết quả của bạn sẽ giống như hình ảnh hiển thị bên dưới

phần thực hành

Chúng tôi đã cung cấp phần Thực hành ở bên phải của mỗi tờ để bạn có thể tự thực hành. Hãy chắc chắn để làm điều đó một mình

Phần kết luận

Về bản chất, bài viết này chỉ ra 5 phương pháp hiệu quả về cách tách số bằng công thức trong Excel. Vì vậy, hãy đọc kỹ toàn bộ bài viết và tải xuống sách bài tập miễn phí để thực hành. Bây giờ, chúng tôi hy vọng bạn thấy bài viết này hữu ích và nếu bạn có thêm bất kỳ câu hỏi hoặc đề xuất nào, vui lòng bình luận tại đây. Cuối cùng, hãy truy cập ExcelDemy để biết thêm nhiều bài viết như thế này