Hướng dẫn Excel này giải thích cách sử dụng hàm MID của Excel để trích xuất các chữ cái thứ 1, 2, 3 và 4 từ các từ trong 4 ô để tạo một từ mới với ảnh chụp màn hình và hướng dẫn
Sự mô tả
Hàm MID của Microsoft Excel có thể được sử dụng để trích xuất một ký tự đơn hoặc một tập hợp các ký tự từ một chuỗi. Như vậy, bạn có thể sử dụng hàm MID để tạo một từ mới bằng cách trích xuất các chữ cái từ nhiều từ khác nhau [giống như một từ viết tắt]. Hãy khám phá làm thế nào để làm điều này
Nếu bạn muốn làm theo hướng dẫn này, hãy tải xuống bảng tính mẫu
Tải xuống ví dụ
Thí dụ
Trong ví dụ này, chúng tôi sẽ không sử dụng hàm MID để tạo từ viết tắt truyền thống trong đó chữ cái đầu tiên được trích xuất từ mỗi từ. Thay vào đó, chúng tôi sẽ trích xuất chữ cái đầu tiên từ từ thứ nhất, chữ cái thứ 2 từ từ thứ 2, chữ cái thứ 3 từ từ thứ 3 và chữ cái thứ 4 từ từ thứ 4
Dựa vào bảng tính dưới đây, hãy tìm 4 từ trong các ô A2, A3, A4 và A5
Trong ô B2, chúng tôi đã tạo công thức sau để trích xuất một chữ cái từ mỗi từ
=MID[A2,1,1] & MID[A3,2,1] & MID[A4,3,1] & MID[A5,4,1]
Công thức này sẽ sử dụng hàm MID để trích xuất 1 chữ cái từ mỗi từ và sau đó nối các chữ cái lại với nhau bằng toán tử &. Chữ cái thứ nhất được trích xuất từ ô A2, chữ cái thứ 2 được trích xuất từ ô A3, chữ cái thứ 3 được trích xuất từ ô A4 và chữ cái thứ 4 được trích xuất từ ô A5. Kết quả của công thức này là chữ Hy vọng
Vì hầu hết các từ viết tắt đều ở dạng chữ hoa nên trong ô C2, chúng tôi cũng đã tạo công thức sau để chỉ cho bạn cách chuyển đổi kết quả thành tất cả chữ hoa
=UPPER[MID[A2,1,1] & MID[A3,2,1] & MID[A4,3,1] & MID[A5,4,1]]
Công thức này sử dụng hàm UPPER để chuyển đổi kết quả thành chữ hoa cho chúng ta từ HOPE
Văn bản trong một ô trong Excel được gọi là chuỗi văn bản. Excel cung cấp một số hàm dựng sẵn cho phép bạn chọn và thao tác các chuỗi văn bản trong ô. Cách dễ nhất để xác định hoặc trích xuất một từ trong một ô là xác định vị trí bắt đầu và kết thúc của từ đó trong chuỗi và sử dụng hàm MID để chỉ từ đó theo vị trí của nó
1
Xem lại văn bản trong ô và tìm từ bạn muốn xác định. Ví dụ: bạn có thể muốn xác định từ "tốt" trong chuỗi sau trong ô Excel
Bây giờ là lúc để tất cả những người đàn ông tốt đến giúp đỡ bữa tiệc
2
Bắt đầu từ đầu chuỗi và đếm từng ký tự trong chuỗi cho đến khi bạn đạt được từ "good. " Ví dụ: "Bây giờ" bắt đầu bằng ký tự 1, "thời gian" bắt đầu ở ký tự 12 và "tốt" bắt đầu ở ký tự 25
3
Xác định xem từ đó có bao nhiêu chữ cái. Ví dụ: "tốt" có bốn chữ cái
4
Bấm vào một ô trống, chẳng hạn như D1, rồi nhập công thức xác định từ "tốt" trong chuỗi lớn hơn. Ví dụ: nhập "=MID[A1, 25, 4]" để trích xuất chuỗi con bắt đầu ở vị trí 25 và có 4 ký tự
5
Cung cấp từ bạn muốn xác định như một phần của công thức và sử dụng hàm FIND và MID để xác định từ. Đầu tiên chỉ định vị trí bắt đầu của chuỗi, đây là kết quả của việc sử dụng hàm FIND trên ô. Ví dụ
=MID[A1, TÌM[A1, "tốt"]]
6
Tính độ dài của từ bằng cách tìm khoảng trống tiếp theo sau khi bắt đầu từ. Ví dụ
=TÌM[A1," ", TÌM[A1," tốt"]
7
Trừ vị trí bắt đầu của từ khỏi vị trí của khoảng trắng để tính độ dài. Ví dụ
Có rất nhiều tác vụ yêu cầu từ đầu tiên của một ô được tách riêng thành một cột
Trộn thư, sắp xếp và lọc, v.v...
Trong hướng dẫn này, bạn học ba phương pháp khác nhau để lấy từ đầu tiên từ một chuỗi văn bản
Mục lục
Cô lập từ đầu tiên với hàm LEFT
Trích xuất từ đầu tiên bằng Tìm và Thay thế
Tách từ đầu tiên khỏi phần còn lại của văn bản bằng Flash Fill
Hãy xem cuộc trò chuyện này
Có vẻ như Mika cần trợ giúp để lấy tên từ danh sách này
Gary – táo, chuối, cam
Tina – spaghetti
Maggie – bánh ngọt, bánh nướng nhỏ
Elena – gà
Luke
Henry
Eric – pizza, ice cream
Tony – soft drinks
Mika
Peter – beef curry, porkchop
Việc này có thể chỉ mất chưa đầy một phút vì chỉ có 10 mục trong danh sách. Nhưng nếu danh sách được nhân đôi lên 20 thì sao?
Mở một sổ làm việc Excel mới và hãy xem cách chúng tôi có thể tự động thực hiện việc này
Trích xuất từ đầu tiên từ văn bản bằng hàm LEFT
Hàm LEFT trích xuất một số ký tự được chỉ định từ phía bên trái của một chuỗi văn bản đã cho. Nó có hai đối số đầu vào
- văn bản là chuỗi nguồn và đây thường là tham chiếu ô
- num_chars – là số ký tự sẽ được trích xuất
Vì tên chỉ là từ đầu tiên của mỗi chuỗi văn bản trong danh sách, bạn cần đếm tất cả các ký tự trước ký tự khoảng trắng đầu tiên ” “
Để làm điều này, sử dụng chức năng TÌM
Nó trả về vị trí của một ký tự/chuỗi đã cho trong một chuỗi văn bản khác. Các đối số đầu vào của hàm FIND là
- find_text là ký tự/chuỗi bạn đang tìm kiếm
- within_text là nơi Excel sẽ tìm find_text
- start_num là vị trí bắt đầu tìm kiếm. Đây là tùy chọn và theo mặc định, tìm kiếm bắt đầu từ phía bên trái
Hãy xem hai chức năng này hoạt động
1. Trong ô B1, gõ
=TÌM[” “, A1]
2. Nhấn Enter
Công thức trả về vị trí của ký tự khoảng trắng đầu tiên. Sau đó, bạn có thể trừ 1 để lấy độ dài của tên không có khoảng trắng là 4
Bây giờ, sử dụng cái này trong hàm LEFT
3. Thay đổi ô B1 thành công thức bên dưới
=LEFT[A1, TÌM[” “, A1]-1]
Bạn cũng có thể sử dụng chức năng TRIM để xóa khoảng trắng
=TRIM[LEFT[A1, TÌM[” “, A1]]]
Kasper Langmann, Chuyên gia Microsoft Office
4. Nhấn Enter
5. Điền vào phần còn lại của các hàng bằng cách sử dụng Fill Handle
Ôi không. 😱
Tại sao một số ô trả về #VALUE. lỗi?
Vì một số mục trong danh sách chỉ có một từ nên hàm TÌM không xác định được khoảng trắng trong chuỗi văn bản
Để khắc phục điều này, công thức phải bao gồm một cách để bắt lỗi này và thay vào đó trả về toàn bộ chuỗi văn bản từ Cột A
cách giải quyết. Sửa lỗi nếu ô chỉ chứa một từ
Hàm IFERROR kiểm tra xem công thức hoặc giá trị mong muốn có trả về lỗi không. Nếu có, thay vào đó, nó sẽ sử dụng công thức trong value_if_error
Hãy thử nó ra
1. Trong ô B1, công thức hiện tại trở thành đối số đầu tiên của hàm IFERROR và đối số thứ hai sẽ là văn bản đầu vào chỉ là ô A1
=IFERROR[LEFT[A1, FIND[” “, A1]-1],A1]
3. Nhấn Enter sau đó điền vào phần còn lại
Trích xuất từ đầu tiên trong Excel bằng Tìm và Thay thế
Hàm LEFT hoạt động tốt nhưng bạn cũng có thể thực hiện tương tự mà không cần công thức
1. Sao chép danh sách từ Cột A sang Cột B
2. Nhấn Ctrl + F để mở Tìm và Thay thế
3. Trên tab Thay thế
- Tìm gì. Nhập ” *” [một dấu cách theo sau là dấu hoa thị].
Dấu hoa thị “*” là ký tự đại diện đại diện cho bất kỳ số lượng ký tự nào. - Thay thế bằng. Để trống
Điều này yêu cầu Excel chọn một ký tự khoảng trắng và tất cả các ký tự khác theo sau. Sau đó, nó sẽ thay thế lựa chọn này bằng không có gì, do đó loại bỏ nó hoàn toàn khỏi chuỗi văn bản
3. Nhấn nút Thay thế tất cả
Trích xuất từ đầu tiên trong Excel bằng Flash Fill
Cuối cùng, hãy sử dụng một trong những tính năng thông minh nhất trong Excel. 💡
Flash Fill phát hiện mẫu có khả năng nhất từ tập dữ liệu của bạn và tự động sao chép mẫu đó
Trước tiên, bạn cần cung cấp cho Excel một ví dụ về kết quả dự định của mình. Các mẫu đơn giản như “tìm từ đầu tiên” hoặc “lấy từ cuối cùng” chỉ cần một hoặc hai ví dụ
1. Trong ô B1, nhập từ đầu tiên là “Gary”. Làm điều này cho cả ô B2
2. Trên ruy-băng Excel, bấm vào tab Dữ liệu rồi bấm vào Flash Fill
Và xem Flash Fill thực hiện điều kỳ diệu của nó
Như bạn vừa học trong nhiệm vụ “lấy từ đầu tiên”, bạn có thể sử dụng công thức, Tìm và Thay thế hoặc Điền Flash để dễ dàng làm việc với bất kỳ chuỗi văn bản nào trong Excel
Bây giờ bạn có thể sử dụng điều này để hợp lý hóa công việc của mình và trở thành một chuyên gia Excel. 📈
Nhưng tại sao lại hợp lý hóa dữ liệu như thế này?
Bởi vì nó làm cho công việc của bạn với các chức năng và tính năng quan trọng dễ dàng hơn nhiều
Những chức năng và tính năng quan trọng?
IF, SUMIF, VLOOKUP và bảng tổng hợp
Và bạn học tất cả những điều đó trong khóa học trực tuyến 30 phút miễn phí của tôi
Đọc tất cả về nó [và đăng ký] tại đây
Các tài nguyên liên quan khác
Bạn có thể tìm hiểu thêm về LEFT và các hàm liên quan đến văn bản khác như hàm MID trong hướng dẫn này