Microsoft Excel là một công cụ tuyệt vời, nhưng đôi khi các tệp bảng tính mà chúng tôi làm việc không lý tưởng. Một ví dụ là tệp có cột dữ liệu, có thể là địa chỉ đường phố mà bạn thực sự muốn tách ra. Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách trích xuất văn bản từ một ô trong Excel bằng cách sử dụng một số hàm văn bản đơn giản nhưng mạnh mẽ. [Kèm sách bài tập thực hành. ]
Nội dung hướng dẫn
- Chuỗi con Excel là gì?
- Một vấn đề phổ biến
- Xây dựng công thức lồng nhau một cách trực quan
- Cách trích xuất số đường bằng hàm LEFT
- Cách trích xuất tên đường bằng chức năng RIGHT
- Dọn dẹp bảng tính và thay đổi định dạng ô
- File thực hành Excel
Chuỗi con Excel là gì?
Trước khi chúng ta có Excel trích xuất văn bản từ chuỗi, chúng ta cần xác định một số điều. Một số ngôn ngữ lập trình có các hàm chuỗi con chuyên dụng. Excel thực hiện điều tương tự bằng cách sử dụng các hàm Văn bản. Họ có thể loại riêng của họ
Khi chúng tôi nói về một chuỗi con, chúng tôi muốn nói đến một phần hoặc tập hợp con của nội dung ô Excel. Ví dụ: nếu ô chứa 1001 Drake Ave. , bất kỳ mục nào trong số này có thể là một chuỗi con
- 1001
- Đại lộ Drake
- 100
- cái cào
Một vấn đề phổ biến
Nhiều cơ sở dữ liệu thành viên hoặc danh sách gửi thư được thiết lập với các trường được xác định cho Tên, Họ, Đường phố, Thành phố, Bang và Zip. Định dạng này hoạt động tốt nếu bạn đang tạo nhãn gửi thư vì bưu điện dựa vào phân loại mã zip. Và đôi khi bạn có thể may mắn và phân tích tên và họ bằng Trình hướng dẫn Chuyển đổi Văn bản thành Cột của Excel
Nhưng nếu bạn cần đi vận động từng nhà để kiểm tra hàng xóm hoặc để thông báo cho mọi người về một dự luật bỏ phiếu sắp tới thì sao?
Nếu bạn mở loại danh sách này trong Excel và sắp xếp nó trên cột Đường phố, bạn sẽ nhận được một danh sách được sắp xếp theo số. Như bạn có thể thấy trong ví dụ bên dưới, các bản ghi Drake Ave không ở cùng nhau
Lý tưởng nhất là bạn muốn sắp xếp danh sách sao cho Drake Ave. các mục được cùng nhau. Có một số cách để thực hiện việc này trong Excel, nhưng có một cách là tạo hai cột từ cột Phố
Cột đầu tiên phản ánh chuỗi con số đường và cột thứ hai là chuỗi con tên đường. Sau đó, bạn có thể sử dụng danh sách dựa trên tên đường và số đường
Xây dựng công thức lồng nhau một cách trực quan
Đối với ví dụ đầu tiên, chúng ta sẽ lồng một số hàm Excel như LEFT và FIND. Khi chúng tôi tiến bộ, chúng tôi sẽ thêm một số bộ dấu ngoặc đơn. Bằng cách lồng nhau, ý tôi là chúng ta sẽ sử dụng một hàm [TÌM] làm đối số cho một hàm khác, chẳng hạn như TRÁI hoặc PHẢI
Hãy bắt đầu với =FIND[" ",C2]. Ở dạng văn bản thuần túy, cú pháp hàm của chúng tôi yêu cầu Excel tìm khoảng trống trong ô tham chiếu C2 để tìm khoảng trống được biểu thị bằng dấu " ". Trong hình bên dưới, tôi đã thêm vị trí bắt đầu là “1“, nhưng đây là tham số tùy chọn và Excel bắt đầu từ 1 theo mặc định. Excel tìm thấy khoảng trống ở vị trí 5 hiển thị trong ô D2
Để làm cho công thức dễ dàng hơn, tôi sẽ xóa tham số bắt đầu tùy chọn là 1 vì dù sao thì Excel cũng bắt đầu từ đó
Bây giờ, hãy thêm hàm LEFT để công thức của chúng ta đọc là =LEFT[C2,[FIND[" ",C2]]]. Trong trường hợp này, chúng ta lại sử dụng ô C2, nhưng hàm LEFT sẽ lấy nội dung ô trong C2 từ vị trí 1 đến vị trí 5 nơi hàm FIND của Excel tìm thấy khoảng trống
Tuy nhiên, có một vấn đề nhỏ. Trong khi bạn không thể nhìn thấy nó, có một dấu cách ở cuối trong D2. Dùng hàm LEN ta thấy ô D2 có 5 ký tự. Bạn có thể nhớ chức năng tiện dụng này từ hướng dẫn của chúng tôi về cách kiểm tra số lượng ký tự trong Excel
Giải pháp là trừ 1 hoặc sử dụng hàm TRIM mà mình đã tham khảo trong cách tách tên trong Excel. Để đơn giản, tôi sẽ sử dụng -1. Mặc dù kết quả trực quan giống nhau nhưng bạn có thể thấy số lượng ký tự giảm đi 1
Cách trích xuất số đường bằng hàm LEFT
- Nhập dữ liệu của bạn vào Microsoft Excel hoặc sử dụng bảng tính mẫu trong phần Tài nguyên
- Trong ô D1, gõ Nbr
- Trong ô E1, nhập Tên đường
- Trong ô D2, nhập công thức Excel sau
=LEFT[C2,[FIND[" ",C2]-1]] - nhấn nút Enter. Giá trị 1001 sẽ hiển thị trong D2
Phần tiếp theo liên quan đến việc sao chép công thức này vào phần còn lại của các mục. Tuy nhiên, chúng ta cần tham chiếu đúng ô đường phố và không sử dụng C2 cho các hàng còn lại
- Nhấp vào ô D2 để chọn phần đầu của phạm vi của chúng tôi
- Di chuột đến góc dưới bên phải
- Bấm đúp vào + con trỏ ở phía dưới bên phải. Điều này sẽ sao chép công thức của bạn xuống cột
Trong cột D, bạn sẽ thấy các số đường được trích xuất
Cách trích xuất tên đường bằng chức năng RIGHT
Bây giờ chúng ta sẽ tạo một công thức lồng nhau tương tự để nắm bắt địa chỉ đường phố bằng hàm RIGHT. Lần này, chúng tôi sẽ lấy nội dung ở bên phải của khoảng trống đầu tiên từ cột Đường phố
- Trong ô E2, nhập công thức sau
=RIGHT[C2,LEN[C2]-FIND[" ",C2]] - nhấn nút Enter. E2 sẽ hiển thị là Drake Ave
- Nhấp vào ô E2 để chọn phần đầu của phạm vi của chúng tôi
- Di chuột đến góc dưới bên phải
- Bấm đúp vào + con trỏ ở phía dưới bên phải. Điều này sẽ sao chép công thức của bạn xuống cột
Cột D và E phải chứa nội dung được phân tích cú pháp từ địa chỉ đường phố ban đầu của bạn
Bảng tính của bạn sẽ trông giống như bên dưới
Dọn dẹp bảng tính và thay đổi định dạng ô
Bảng tính hiện có các trường phân tách của bạn, nhưng bạn nên xóa các công thức. Đề xuất của tôi là chuyển đổi các công thức TRÁI và PHẢI thành các giá trị tương ứng của chúng. Chúng tôi đã thực hiện một hướng dẫn trước đó về cách sao chép các giá trị công thức trong Excel sang các giá trị
Sau khi bạn chuyển đổi cột Nbr, bạn có thể muốn thay đổi loại định dạng thành một số
- Nhấp vào cột D
- Nhấp chuột phải và chọn Định dạng ô
- Trên hộp thoại Format Cells, chọn Number
- Đặt vị trí thập phân thành 0
- Nhấp vào OK
Mặc dù ví dụ của chúng tôi đã trích xuất văn bản từ một ô Excel chứa thông tin đường phố, nhưng bạn có thể sử dụng quy trình tương tự để phân tích cú pháp các mục nhập khác. Ví dụ: Bước 1 ở trên thực sự đang phân tích cú pháp mọi thứ trừ từ đầu tiên vì nó đang tìm kiếm khoảng trống. Bạn có thể thay đổi công thức để tìm các giá trị khác, chẳng hạn như dấu phẩy hoặc dấu @