Extract Group in cell

=IF(ISNUMBER(SEARCH(" : Users",D2)),"Users",IF(ISNUMBER(SEARCH("Everyone",D2)),"Everyone","Authenticated Users"))

Extract Path in cell

=MID(B9,FIND("Path :",B9)+LEN("Path :"),FIND("Used by services",B9)-FIND("Path :",B9)-LEN("Path :"))

  • Only extract the path that includes three “", decrease to two if do not have it
    =IFERROR(MID(D3,1,FIND("\",D3,FIND("\",D3,FIND("\",D3)+1)+1)),MID(D3,1,FIND("\",D3,FIND("\",D3)+1)))
  • Click “Data” , then will find “Text to Columns”. It will separate each field, only need 3 fields.
  • New a pivot table to calculate data.(Control Columns A:I )
  • Required: DNS Name, Plugin Output

Extract Path except for filename

=LEFT(D2,FIND(CHAR(1),SUBSTITUTE(D2,"\",CHAR(1),LEN(D2)-LEN(SUBSTITUTE(D2,"\",""))))-1)**
i. left(text, num_chars)

  • text: required. The text string that contains the characters you want to extract.
  • num_chars: optional. Specifies the number of characters you want left to extract.
    ii. find(find_text, within_text, [start_num])
  • find_text: required. The text you want to find.
  • within_text: required. The text containing the text you want to find.
  • start_num: option. Specifies the character at which to start the search. The first character in within_text is character number 1. if you omit start_num, it is assumed to be 1.
    iii.substitute(text, old_text, new_text, [instance_num])
  • text: required. The text or the reference to a cell containing text for which you want to substitute characters.
  • old_text: required. The text you want to replace.
  • new_text: required. The Text you want to replace old_text with.
  • instance_num: optional. Specifics which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.
    iv. len(text)
  • Text: required. The text whose length you want to find. Space count as characters.

Combine content of cells

a. =TEXTJOIN(",",TRUE,K1:K10)
i. textjoin(delimiter, ignore_empty, text1)

  • Delimiter: required. A text string, either empty, or one or more characters enclosed by double quotes, or a reference to a valid text string. If a number is supplied, it will be treated as text.
  • Ignore_empty: if true, ignores empty cells.
  • Text1: required. Text item to be joined. A text string, or array of strings, such as a range of cells.

xLookup

=XLOOKUP(lookup_value,lookup_array,return_array, [if_not_found], [match_mode], [search_mode])

参数说明
lookup_value：查找值(必填项)
lookup_array：要搜索的数组或区域(必填项)
return_array：要返回的数组或区域(必填项)
[if_not_found]：如果找不到有效的匹配项，则返回你提供的[if_not_found]文本；如果找不到有效的匹配项，并且缺少[if_not_found]，则会返回#N/A(选填项)
[match_mode]：指定匹配类型。

0表示完全匹配，如果未找到，则返回#N/A，这是默认选项；
-1表示完全匹配，如果没有找到，则返回下一个较小的项；
1表示完全匹配，如果没有找到，则返回下一个较大的项；
2表示通配符匹配，其中*、？、~有特殊含义。

[search_mode]：指定要使用的搜索模式。(选填项)

1表示从第一项开始搜索，这是默认选项；
-1表示从最后一项开始执行反向搜索；
2表示执行依赖于lookup_array按升序排序的二进制搜索，如果未排序，将返回无效结果；
-2表示执行依赖于lookup_array按降序排序的二进制搜索，如果未排序，将返回无效结果。

通配符解释
通配符一般使用在Excel查找和替换中，将通配符用作文本筛选器的比较条件，并在搜索和替换内容时使用通配符字符。
常用通配符有三个，分别是？(问号)、*(星号)、~(波形符)。

？(问号)表示任意单个字符，例如A？就表示以A开头的任意两个字符；
*(星号)表示任意数量的字符，例如*A*就表示含有字符A的任意一个字符串，*A就表示以字符A结尾的任意一个字符串，A*就表示以字符A开头的任意一个字符串；
~(波形符)即转义字符，通常后跟？、*或者~，表示~后面的字符为普通字符，例如A~？表示查找字符串A？。