Trong hướng dẫn này, bạn sẽ tìm hiểu cách tìm vị trí xuất hiện cuối cùng của một ký tự trong một chuỗi trong Excel
Vài ngày trước, một đồng nghiệp đã đưa ra vấn đề này
Anh ấy có một danh sách các URL như hình bên dưới và anh ấy cần trích xuất tất cả các ký tự sau dấu gạch chéo cuối cùng [“/”]
Ví dụ: từ https. //thí dụ. com/archive/Jan anh ấy phải trích xuất 'Jan'.
Sẽ thực sự dễ dàng nếu chỉ có một dấu gạch chéo trong các URL
Những gì anh ấy có là một danh sách khổng lồ gồm hàng nghìn URL có độ dài khác nhau và số lượng dấu gạch chéo về phía trước khác nhau
Trong những trường hợp như vậy, mẹo là tìm vị trí của lần xuất hiện cuối cùng của dấu gạch chéo lên trong URL
Trong hướng dẫn này, tôi sẽ chỉ cho bạn hai cách để làm điều này
- Sử dụng công thức Excel
- Sử dụng chức năng tùy chỉnh [được tạo qua VBA]
Lấy Vị trí Cuối cùng của Ký tự bằng Công thức Excel
Khi bạn có vị trí của lần xuất hiện cuối cùng, bạn có thể chỉ cần trích xuất bất kỳ thứ gì ở bên phải của nó bằng hàm RIGHT
Đây là công thức tìm vị trí cuối cùng của dấu gạch chéo về phía trước và trích xuất tất cả văn bản ở bên phải của nó
=RIGHT[A2,LEN[A2]-FIND["@",SUBSTITUTE[A2,"/","@",LEN[A2]-LEN[SUBSTITUTE[A2,"/",""]]],1]]
Công thức này hoạt động như thế nào?
Hãy chia nhỏ công thức và giải thích cách thức hoạt động của từng phần của nó
- SUBSTITUTE[A2,”/”, “” ] – Phần này của công thức thay thế . Vì vậy, ví dụ: Trong trường hợp bạn muốn tìm sự xuất hiện của bất kỳ chuỗi nào khác với dấu gạch chéo lên, hãy sử dụng chuỗi đó tại đây.
- LEN[A2]-LEN[SUBSTITUTE[A2,”/”, “” ]] – This part would tell you how many forward slashes are there in the string. It simply subtracts the length of the string without the forward slash from the length of the string with forward-slashes.
- SUBSTITUTE[A2,”/”,”@”,LEN[A2]-LEN[SUBSTITUTE[A2,”/”,””]]] – Phần này của . Ý tưởng là làm cho nhân vật đó trở nên độc đáo. Bạn có thể sử dụng bất kỳ ký tự nào bạn muốn. Chỉ cần đảm bảo rằng nó là duy nhất và chưa xuất hiện trong chuỗi.
- FIND[“@”,SUBSTITUTE[A2,”/”,”@”,LEN[A2]-LEN[SUBSTITUTE[A2,”/”,””]]],1] – This part of the formula would give you the position of the last forward slash.
- LEN[A2]-FIND[“@”,SUBSTITUTE[A2,”/”,”@”,LEN[A2]-LEN[SUBSTITUTE[A2,”/”,””]]],1 . – This part of the formula would tell us how many characters are there after the last forward slash.
- =RIGHT[A2,LEN[A2]-FIND[“@”,SUBSTITUTE[A2,”5. ]”,”@”,[LEN[A2]-LEN[SUBSTITUTE[A2,”5. ]”,””]]]/3],1]] [mới]
- SUBSTITUTE[A2," ","/",LEN[A2]-LEN[SUBSTITUTE[A2," ",""]]]. Để thay thế khoảng trắng cuối cùng bằng "/" [hoặc các ký tự đặc biệt khác] để phân biệt với các khoảng trắng khác;
– TÌM["/",SUBSTITUTE[A2," ","/",LEN[A2]-LEN[SUBSTITUTE[A2," ",""]]]]. Để tìm vị trí của dấu "/" trong chuỗi văn bản, đó là vị trí của dấu cách cuối cùng
2. Để xác định vị trí không gian cuối cùng thứ hai
=FIND["/",SUBSTITUTE[Ô," ","/", LEN[Ô]- LEN[SUBSTITUTE[Ô," ",""]]-1]]
giải thích
- SUBSTITUTE[Ô," ",""]. Xóa khoảng trắng trong chuỗi văn bản;
– LEN[SUBSTITUTE[Ô," ",""]]. Độ dài của chuỗi văn bản sau khi loại bỏ dấu cách;
- LEN [Di động]. Độ dài của chuỗi văn bản;
– LEN[Ô]-LEN[SUBSTITUTE[Ô," ",""]]. Số khoảng trắng trong chuỗi văn bản;
– LEN[Ô]-LEN[SUBSTITUTE[Ô,” “,””]]-1. Số khoảng trắng trừ 1 hoặc khoảng trắng cuối cùng thứ hai trong chuỗi văn bản;
- SUBSTITUTE[Ô," ","/",LEN[Ô]-LEN[SUBSTITUTE[Ô," ",""]]-1]. Để thay thế khoảng trắng cuối cùng thứ hai bằng "/" [hoặc các ký tự đặc biệt khác] để phân biệt với các khoảng trắng khác;
– FIND["/",SUBSTITUTE[Ô," ","/",LEN[Ô]-LEN[SUBSTITUTE[Ô," ",""]]-2]]. Để tìm vị trí của "/", là vị trí của dấu cách cuối cùng thứ ba
Ở đây chúng tôi mô tả, cách lấy một phần của chuỗi văn bản, là phần sau phiên bản cuối cùng của một số chuỗi [dấu cách, dấu gạch chéo. ]
thích ở đây
chúng ta cần bướm [vì nó nằm sau dấu gạch chéo cuối cùng]
Làm thế nào để làm nó?
Đối với chuỗi trong A2, hãy sử dụng chuỗi này
- =MID[A1;FIND["#";SUBSTITUTE[A1;"/";"#";LEN[A1]-LEN[SUBSTITUTE[A1;"/";""]]]]+1;100]
Tại sao?
- LEN[A1]-LEN[SUBSTITUTE[A1;"/";""]] - đếm ký tự [dấu gạch chéo]. Trong ví dụ của chúng tôi, nó là 2
- SUBSTITUTE[A1;"/";"#";LEN[A1]-LEN[SUBSTITUTE[A1;"/";""]]] - thay thế ký tự cuối cùng bằng # hoặc bất kỳ ký hiệu nào khác không có trong
- FIND["#";SUBSTITUTE[A1;"/";"#";LEN[A1]-LEN[SUBSTITUTE[A1;"/";""]]]]+1 - tìm vị trí của #
- MID lấy chuỗi sau vị trí này
Nhân tiện, để có được những gì TRƯỚC phiên bản cuối cùng của một thứ gì đó, hãy sử dụng một cái gì đó như thế này
- =LEFT[A1;FIND["#";SUBSTITUTE[A1;"/";"#";LEN[A1]-LEN[SUBSTITUTE[A1;"/";""]]]]-1]