如何使用多次出现的特定字符从右向左从单元格中提取字符串？

Normally I would just use the RIGHT function in excel to split it by finding a specific character such as / and outputting the string that I want.

通常我只是使用excel中的RIGHT函数通过查找特定字符(如/)并输出我想要的字符串来拆分它。

However, I am finding trouble extracting THISSTRING.txt from d/aaa/THISSTRING.txt. With only one instance of / I would just use a function such as =RIGHT(B17,LEN(B17) - FIND("/",B17))

但是,我发现从d / aaa / THISSTRING.txt中提取THISSTRING.txt时遇到了麻烦。只有一个/ I实例才会使用= RIGHT(B17,LEN(B17) - FIND(“/”,B17)等函数

3 个解决方案

#1

Objective: To return the rightmost sub-string from a target string after the last occurrence of a character which appears several times within the target string.

目标:在最后一次出现在目标字符串中出现多次的字符后,从目标字符串返回最右边的子字符串。

This formula:

=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99))

Provides the correct result under the following conditions:

在以下条件下提供正确的结果:

The sub-string to retrieve does not have more than 99 characters.

要检索的子字符串不超过99个字符。

The sub-string to retrieve does not contain more than one space character together.

要检索的子字符串不包含多个空格字符。

Example: To retrieve a sub-string which is 123 characters long and contains the following characters 1 ABC XXX 123 XYZ.

示例:要检索长度为123个字符且包含以下字符的子字符串1 ABC XXX 123 XYZ。

Point 1 is easily solved by working with the length of the string instead of a fixed number:

通过使用字符串的长度而不是固定数字,可以轻松解决第1点:

=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",LEN(A1))), LEN(A1)))

However point 2 can't be overcome with the referred formula.

然而,使用所提到的公式无法克服第2点。

Proposed solution: The following formula returns the correct result regardless of the conditions mentioned above:

建议的解决方案:无论上述条件如何,以下公式都会返回正确的结果:

=RIGHT(A1, LEN(A1) - FIND( CHAR(12),
SUBSTITUTE(A1, "/", CHAR(12), LEN(A1) - LEN( SUBSTITUTE(A1, "/", "" )))))

Note: I used non-printable character 12 which is very unlikely to be found in excel, change as required.

注意:我使用了不可打印的字符12,这在excel中很难找到,根据需要进行更改。

#2

Here's one way to get the rightmost:

这是获得最右边的一种方法:

=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99))

#3

Here's another way.....

这是另一种方式.....

=REPLACE(B17,1,LOOKUP(2^15,FIND("/",B17,ROW(INDIRECT("1:"&LEN(B17))))),"")

FIND generates an array of values, indicating the first position of a "/" in B17, but with the start point incrementing by 1 each time, which means that the last numeric value in that array is the positon of the last "/".

FIND生成一个值数组,指示B17中“/”的第一个位置,但每次开始点递增1,这意味着该数组中的最后一个数值是最后一个“/”的位置。

LOOKUP extracts that value from the array and we can use it in REPLACE function to replace all characters before and at that position with nothing, just leaving you with all characters after the last "/"

LOOKUP从数组中提取该值,我们可以在REPLACE函数中使用它来替换之前和那个位置的所有字符,只剩下最后一个“/”之后的所有字符

You'll get an error if there is no "/" in B17

如果B17中没有“/”,您将收到错误消息

#1