regexreplace 批量替换多组数据

Functions：

1. regexreplace
2. ArrayFormula

REGEXREPLACE

Replaces part of a text string with a different text string using regular expressions.

Sample usage

REGEXREPLACE("Spreadsheets", "S.*d", "Bed")

Syntax

REGEXREPLACE(text, regular_expression, replacement)

• text – The text, a part of which will be replaced.

• regular_expression – The regular expression. All matching instances in text will be replaced.

• replacement – The text that will be inserted into the original text.

Notes

• Google products use RE2 for regular expressions. Google Sheets supports RE2 except Unicode character class matching. Learn more on how to use RE2 expressions.
• This function only works with text (not numbers) as input and returns text as output. If a number is desired as the output, try using the VALUE function in conjunction with this function. If numbers are used as input, convert them to text using the TEXT function.

See also

REGEXEXTRACT: Extracts matching substrings according to a regular expression.

REGEXMATCH: Whether a piece of text matches a regular expression.

SUBSTITUTE: Replaces existing text with new text in a string.

REPLACE: Replaces part of a text string with a different text string.

VLOOKUP 批量数据分类

Functions：

1. vlookup:

prob level
0 0
0.001 1
0.006 2
0.012 3
0.03 4
1 5

1. 其中 A2 表示目标数据，及 0.0005
2. $D$1:$E$6 表示映射关系数据，这里我们必须保证这个返回的第一列是和目标数据相关的，用于搜索数据。
3. 第三个参数需要重点注意，他和第二个参数相关，表示最终返回哪个数据，这个值是在第二个参数数据的 index，比如我们第二个参数是一个有 2 列的数据，那么如果 该参数 = 1， 表示返回第二列数据。
4. is_sorted：需要设置为 1，只有 TRUE 才会返回最相近的匹配。

完全匹配用例

data!$C$2:$D$52 是一个 key value 对照关系。

IFERROR 表示如果找不到我们可以显示其他内容，比如留空。

VLOOKUP

Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found.

Sample usage

VLOOKUP(10003, A2:B26, 2, FALSE)

Syntax

VLOOKUP(search_key, range, index, [is_sorted])

• search_key – The value to search for. For example, 42, 'Cats' or I24.
• range – The range to consider for the search. The first column in the range is searched for the key specified in search_key.
• index – The column index of the value to be returned, where the first column in range is numbered 1.
• If index is not between 1 and the number of columns in range, #VALUE! is returned.
• is_sorted[TRUE by default] – Indicates whether the column to be searched (the first column of the specified range) is sorted. FALSE is recommended in most cases.
• It’s recommended to set is_sorted to FALSE. If set to FALSE, an exact match is returned. If there are multiple matching values, the content of the cell corresponding to the first value found is returned, and #N/A is returned if no such value is found.
• If is_sorted is TRUE or omitted, the nearest match (less than or equal to the search key) is returned. If all values in the search column are greater than the search key, #N/A is returned.

组合多项字符串及附加操作

Functions：

1. JOIN
2. SPLIT
3. SUBSTITUTE

1. 先将数据使用 % JOIN
2. 然后 SPLIT 这样空白的就没有了
3. 再用 , JOIN

SPLIT function

Divides text around a specified character or string and puts each fragment into a separate cell in the row.

Make a copy

Sample usage

SPLIT("1,2,3", ",")

SPLIT("Alas, poor Yorick"," ")

SPLIT(A1, ",")

Syntax

SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

• text – The text to be divided.

• delimiter – The character or characters to be used to split text.

• By default, each character in delimiter is considered individually, e.g. if delimiter is ‘the’, then text is divided around the characters ‘t’, ‘h’ and ‘e’. Set split_by_each to FALSE to turn off this behaviour.
• split_by_each[ OPTIONAL – TRUE by default ] – Whether or not to divide text around each character contained in delimiter.

• remove_empty_text – [ OPTIONAL – TRUE by default ] – Whether or not to remove empty text messages from the split results. The default behavior is to treat consecutive delimiters as one (if TRUE). If FALSE, empty cells values are added between consecutive delimiters.

Notes

• Note that the character or characters to split the string around will not be contained in the result themselves.

See also

CONCATENATE: Appends strings to one another.