Cập nhật mới nhất ngày 03 tháng 09 năm 2022: Trong lúc làm việc với Excel mình có gặp một số trường hợp yêu cầu tách chỉ lấy phần số trong 1 chuỗi cho trước. Nhằm mục đích quản lý thông số, dữ kiện hoặc số điện thoại, ID của khách hàng, đối tác trên danh sách tổng. Vậy làm thế nào để tách số đây? Bài viết này chia sẻ với các bạn cách để tách số và chữ trong excel, thực hiện công việc tách số ra khỏi chuỗi.
NỘI DUNG
- Video hướng dẫn thực hiện nhanh
- Tách Chữ Ra khỏi chuỗi
- Hàm tách số
Video hướng dẫn thực hiện nhanh
Tách Chữ Ra khỏi chuỗi
Để tách số và chữ trong excel thì đầu tiên bạn phải xác định vị trí số. Để tìm vị trí số thì ta dùng hàm FIND với cấu trúc là =FIND[{0;1;2;3;4;5;6;7;8;9};C4]. Hàm này có nghĩa là bạn sẽ tìm từ số 0 đến số 9 trong chuỗi C4.
Tích vào góc dưới ô vừa tạo kéo xuống để định dạng cho các ô phía dưới. Kết quả trả về cho các ô có số và trả về VALUE cho các ô không có số.
Để không trả về VALUE thì bạn sửa như sau: ta tích đúp vào ô đầu tiên của trả về và thêm &"0123456789" vào cuối hàm có nghĩa là bạn sẽ nối thêm chuỗi từ 0 đến 9 để không bị thông báo VALUE mà trả về vị trí cuối cùng của chữ cũng là vị trí bắt đầu của số. =FIND[{1;2;3;4;5;6;7;8;9};C4&"0123456789"]
Tiếp theo mình thêm hàm Min phía ngoài và enter lúc này kết quả trả về là vị trí nó tìm thấy và là vị trí nhỏ nhất. Tức là trong chuỗi C4 và từ 0 đến 9 để tìm được vị trí đầu tiên và trả về.
Để kiểm tra và xem cách chạy của hàm này bạn kích vào ô vừa tìm vị trí số -> chọn vào Formulas -> Evaluate Arrows -> cửa sổ hiện lên chọn vào Evaluate để xem từng bước chạy của hàm vừa tạo.
Để tách được tên nhân viên mình làm như thế nào? Muốn tách tên nhân viên bạn dùng hàm LEFT bằng cách =LEFT[chuỗi mình cần tách;lấy vị trí bắt đầu số và trừ đi 1]. =LEFT[C4;H4-1]
Riêng trong vị trí chữ vừa tách ra luôn thừa 1 khoảng trắng và để thực hiện xóa khoảng trắng này đi ta dùng thêm lệnh TRIM trước hàm LEFT là được. =TRIM[LEFT[C4;H4-1]]
Bây giờ mình viết hàm chính thức lồng các hàm lại với nhau đầu tiên là =TRIM[LEFT[C4;MIN[FIND[{0;1;2;3;4;5;6;7;8;9};C4&"0123456789"]]-1]]. Đây là cách tách số và chữ trong excel đơn khá đơn giản. Vì thế mà bạn có thể tham khảo nhanh để áp dụng vào thực tế cho việc hàm tách số và chữ trong excel.
Hàm tách số
Để có thể sử dụng hàm chỉ lấy số trong excel đơn giản và dễ dàng. Bạn hãy sử dụng hàm tách số trong excel là RIGHT với cú pháp: =RIGHT[chuỗi bạn cần tách; LEN[chuỗi bạn cần tách]-Vị trí bắt đầu số + 1] và enter. =RIGHT[C4;LEN[C4]-H4+1]
Như vậy hàm tách số chính thức sẽ là =RIGHT[chuỗi cần tách;LEN[chuỗi cần tách];+1-MIN[FIND[{0;1;2;3;4;5;6;7;8;9},chuôi cần tách & "0123456789"]]]. =RIGHT[C4;LEN[C4]+1-MIN[FIND[{0;1;2;3;4;5;6;7;8;9};C4&"0123456789"]]]
Như vậy bạn đã sử dụng được hàm lấy số trong excel. Đây là cách lấy số trong chuỗi excel khá thông dụng. Bạn nên thử tải xuống ví dụ rồi tách chữ và số trong excel. Nên rèn luyện hàm tách số ra khỏi chuỗi trong excel để có thể sử dụng nhanh chóng. Tăng hiệu suất tối đa cho công việc của bạn lên gấp nhiều lần.
Lưu ý: cách tách chữ và số ra khỏi chuỗi này chỉ sử dụng được với trường hợp số nằm ở bên phải. Trường hợp số hoặc chữ nằm bất cứ ở đâu trong ô hoặc tìm số trong chuỗi excel. Ta phải sử dụng hàm cao hơn hoặc VBA mới thực hiện được, nếu các bạn muốn mình làm bài viết, video cách tách số và chữ ra khỏi chuỗi nâng cao thì để lại bình luận bên dưới nha.
Excel là phần mềm được sử dụng phổ biến hiện nay, một trong những hàm cơ bản của Excel là hàm MID được dùng để cắt chuỗi. Bài viết dưới đây, Điện máy XANH sẽ hướng dẫn cho bạn cách sử dụng hàm MID nhé!
Bài viết được thực hiện trên phiên bản Excel 2016, bạn có thể áp dụng tương tự trên các phiên bản Excel khác như: 2007, 2010, 2013, 2017 và Microsoft Excel 365.
1Hàm MID trong Excel
Hàm MID trong Excel là hàm dùng để cắt chuỗi ký tự ở giữa chuỗi ký tự cho trước, được xác định bắt đầu bằng vị trí bạn chỉ định và số lượng ký tự theo ý bạn.Hàm MID có cấu trúc như sau:
=MID[text,m,n]
Trong đó:
- text: Chuỗi ký tự.
- m: Vị trí bắt đầu cần cắt chuỗi ký tự.
- n: Số ký tự cần cắt ra từ chuỗi ký tự.
Lưu ý:
- Nếu giá trị m lớn hơn độ dài chuỗi ký tự thì hàm MID trả về giá trị rỗng.
- Nếu giá trị m nhỏ hơn độ dài chuỗi ký tự nhưng tổng giá trị m cộng với n vượt quá độ dài chuỗi ký tự, thì hàm MID trả về các ký tự đến cuối cùng của chuỗi ký tự đã cho.
- Nếu giá trị m nhỏ hơn 1 hay giá trị n là số âm thì hàm MID trả về giá trị lỗi #VALUE!
Một vài ứng dụng thực tế của hàm MID:
Ban đầu có một chuỗi ký tự là homnaytroimua.
- Yêu cầu lấy ra chữ naytroimua
=MID[C8,4,10]
-Yêu cầu lấy ra chữ mua
=MID[C8,11,3]
Lưu ý: Hàm MID, MIDB dùng cho các loại ngôn ngữ khác nhau:
- Các hàm này có thể không khả dụng ở mọi ngôn ngữ.
- Hàm MID luôn luôn đếm mỗi ký tự là 1.
- Hàm MIDB đếm mỗi ký tự là 2 khi bạn đã cho phép soạn thảo hỗ trợ DBCS [Ví dụ: Tiếng Nhật, Tiếng Trung, Tiếng Hàn], sau đó đặt nó làm ngôn ngữ mặc định. Nếu không, hàm MIDB đếm mỗi ký tự là 1.
2Ví dụ hàm MID
Để hiểu rõ hơn về cách sử dụng của hàm MID, mời bạn tham khảo qua các ví dụ dưới đây:
Cách xuất họ và tên bất kỳ
Dùng hàm SEARCH để tìm kiếm vị trí dấu cách. Sau đó, bạn dùng hàm MID để tách ra chuỗi con bắt đầu từ ký tự đầu tiên cho đến ký tự đứng trước dấu cách.
Để xuất ra thông tin họ bất kỳ, ta có ví dụ:
=MID[A2,1,SEARCH[" ",A2]-1]
Để xuất tên ta dùng cấu trúc sau:
Lần này, SEARCH được sử dụng để xác định vị trí bắt đầu. Sau đó dùng hàm LEN để cung cấp tổng chiều dài của chuỗi ban đầu. Nếu không dùng hàm LEN thì bạn có thể đặt một số thật dài cho chuỗi ký tự cần trích xuất, ví dụ như 100. Tiếp đến, dùng hàm TRIM sẽ loại bỏ phần dấu cách.
=TRIM[MID[A2,SEARCH[" ",A2],LEN[A2]]
Cách trích xuất chuỗi ký tự nằm giữa hai dấu cách
Chúng ta vẫn sử dụng hàm SEARCH để xác định vị trí của dấu cách đầu tiên, cộng thêm 1 vào đó để chuỗi ký tự trích ra bắt đầu bằng ký tự đứng ngay sau dấu cách.
Tiếp đến, để có được vị trí của khoảng cách thứ 2 thì bạn hãy sử dụng hàm SEARCH với việc bắt đầu tìm kiếm từ ký tự đầu tiên sau dấu cách thứ 2: SEARCH[” “,A2,SEARCH[” “,A2]+1]
Sau đó bạn cần trừ đi vị trí 2 khoảng không của 2 dấu cách. Sử dụng: SEARCH [” “, A2, SEARCH [” “,A2]+1] – SEARCH [” “,A2]
Kết hợp những công thức nêu trên lại với nhau chúng ta có được công thức MID để trích ra chuỗi ký tự nằm giữa hai dấu cách:
=MID[A2, SEARCH[” “,A2]+1, SEARCH [” “, A2, SEARCH [” “,A2]+1] – SEARCH [” “,A2]-1]
Cách tách chữ từ vị trí bất kỳ
Bạn sử dụng công thức chung sau:
=TRIM[MID[SUBSTITUTE[string,” “,REPT[” “,LEN[string]]], [N-1]*LEN[string]+1, LEN[string]]] Trong đó:
- String là chuỗi văn bản ban đầu.
- N là số thứ tự của từ cần trích xuất.
- LEN - hàm lấy tổng chiều dài của chuỗi văn bản gốc.
- REPT - hàm lặp lại một ký tự cụ thể với số lần nhất định.
- SUBSTITUTE - thay thế một ký tự bằng một ký tự khác.
- TRIM - loại bỏ dấu cách.
Hàm SUBSTITUTE và REPT biến mọi khoảng trống trong chuỗi văn bản gốc thành nhiều khoảng trống. Số lượng khoảng trống được thêm vào bằng tổng chiều dài của chuỗi văn bản gốc được trả về bới hàm LEN: SUBSTITUTE[A2,” “,REPT[” “,LEN[A2]]]
Tiếp đến, bạn cần tính ra vị trí bắt đầu của chuỗi con mà bạn cần trích xuất sử dụng công thức sau: [N-1] * LEN [A1] +1. Phép tính này trả về vị trí của ký tự đầu tiên trong chuỗi con cần trích xuất hoặc vị trí của một số khoảng trống trong tổng số những khoảng trống trước đó.
Số ký tự trích xuất bạn có thể tìm ra bằng cách lấy tổng chiều dài của chuỗi văn bản gốc: LEN[A2]. Qua đây, bạn sẽ loại bỏ được những khoảng trắng trong chuỗi dài gồm cả khoảng trống và từ.
Cuối cùng, hàm TRIM sẽ loại bỏ khoảng trống nằm ở đầu và cuối.
Cách trích xuất một từ chứa một hoặc nhiều ký tự cụ thể
Cùng xem qua ví dụ sau: Lọc giá tiền chứa trong đoạn văn bản khi sử dụng hàm MID.
=TRIM[MID[SUBSTITUTE[A2," ",REPT[" ",99]],MAX[1,FIND["$",SUBSTITUTE[A2," ",REPT[" ",99]]]-50],99]]
Giải thích:
Hàm SUBSTITUTE và hàm REPT biến mọi khoảng trống trong chuỗi văn bản gốc thành nhiều khoảng trống, chính xác hơn là 99 khoảng trắng.
Hàm FIND sẽ tìm kiếm và xác định vị trí của ký tự mong muốn [trong ví dụ này là $], tiếp đó bạn trừ đi 50. Làm như vậy bạn sẽ nhận lại được 50 ký tự và đặt ở giữa khối 99 khoảng trống đứng trước chuỗi con chứa ký tự được chỉ định.
Hàm MAX được sử dụng để xử lý tình huống khi chuỗi con mong muốn xuất hiện ở đầu chuỗi văn bản ban đầu. Trong trường hợp này, kết quả của FIND [] – 50 sẽ là số âm và MAX [1, FIND [] – 50] sẽ được thay thế bằng 1.
Vì thế, hàm MID sẽ thu thập 99 ký tự tiếp theo và trả về chuỗi con bạn cần. Hàm TRIM giúp bạn loại bỏ hết những khoảng trống và trả về giá tiền kèm theo [40$, 600$].
Cách khiến hàm MID trả về một số
=VALUE[MID[A2,7,3]]
Hàm MID trong Excel luôn trả về chuỗi văn bản, vậy để chuyển đầu ra thành một số. Bạn hãy sử dụng hàm VALUE để chuyển đổi.
5.490.000₫ 9.990.000₫ -45%
Quà 600.000₫
Trả góp 0%
6.090.000₫ 8.990.000₫ -32%
Quà 600.000₫
16.990.000₫ 24.690.000₫ -31%
Quà 100.000₫
Trả góp 0%
6.290.000₫ 8.990.000₫ -30%
Quà 100.000₫
9.490.000₫ 13.490.000₫ -29%
Quà 100.000₫
8.990.000₫ 12.490.000₫ -28%
Quà 600.000₫
19.290.000₫ 26.790.000₫ -27%
Quà 900.000₫
15.990.000₫ 21.890.000₫ -26%
Quà 400.000₫
10.090.000₫ 13.790.000₫ -26%
Quà 600.000₫
9.590.000₫ 12.790.000₫ -25%
Quà 100.000₫
7.490.000₫ 9.990.000₫ -25%
Quà 100.000₫
26.990.000₫ 35.990.000₫ -25%
Quà 300.000₫
17.990.000₫ 23.690.000₫ -24%
Quà 900.000₫
Trả góp 0%
11.690.000₫ 15.490.000₫ -24%
Quà 200.000₫
18.290.000₫ 24.290.000₫ -24%
Quà 900.000₫
9.490.000₫ 12.490.000₫ -24%
Quà 600.000₫
Xem thêm sản phẩm LaptopXem thêm:
- Hàm AND trong Excel là gì? Cách sử dụng, ví dụ đơn giản
- Cách dùng hàm IF trong Excel | Có ví dụ đơn giản dễ hiểu
- 3 cách chuyển file Excel sang PDF online/offline nhanh, chi tiết nhất
Trên đây là bài viết chia sẻ cho bạn cách dùng hàm MID trong Excel. Mong rằng những thông tin này sẽ mang lại hữu ích cho bạn trong việc sử dụng và nếu có thắc mắc hãy để lại bình luận bên dưới nhé!
Bạn có làm được hướng dẫn này không?
Cám ơn bạn đã phản hồi
Xin lỗi bài viết chưa đáp ứng nhu cầu của bạn. Vấn đề bạn gặp phải là gì?
Đang làm gặp lỗi, không có trong hướng dẫn Đã làm nhưng không thành công Bài viết không đúng nhu cầu Bài viết đã cũ, thiếu thông tin
GỬI
Bài viết liên quan
- Tư vấn mua máy quay phim phù hợp cho gia đình
- Cách đặt font chữ mặc định trong Word đơn giản, dễ thực hiện nhất
- Cách điều chỉnh âm lượng laptop trên Windows 11 đơn giản
Từ khóa: cách sử dụng hàm mid trong excel , sử dụng hàm mid trong excel , hàm mid trong excel , cắt chuỗi trong excel , hàm mid