LastIndexOf char in Excel

LastIndexOf char in Excel

Ví dụ:

Cho chuỗi https://example.com/archive/January

Yêu cầu lấy chuỗi con cuối cùng sau dấu /

=RIGHT(A2,LEN(A2)-FIND(“@”,SUBSTITUTE(A2,”/”,”@”,LEN(A2)-LEN(SUBSTITUTE(A2,”/”,””))),1))

Phân tích mã

  • SUBSTITUTE(A2,”/”,“”) – This part of the formula replaces the forward slash with an empty string. So for example, In case you want to find the occurrence of any string other than the forward slash, use that here.
  • 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,”/”,””))) – This part of the formula would replace the last forward slash with @. The idea is to make that character unique. You can use any character you want. Just make sure it’s unique and doesn’t appear in the string already.
  • 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,”/”,”@”,LEN(A2)-LEN(SUBSTITUTE(A2,”/”,””))),1)) – Now this would simply give us the string after the last forward slash.

 

Nguồn: https://trumpexcel.com/find-characters-last-position/

 

Advertisements