string excel中的子串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2727679/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Substring in excel
提问by Celops
I have a set of data that shown below on excel.
我有一组数据显示在excel下面。
R/V(208,0,32) YR/V(255,156,0) Y/V(255,217,0)
R/S(184,28,16) YR/S(216,128,0) Y/S(209,171,0)
R/B(255,88,80) YR/B(255,168,40) Y/B(255,216,40)
And I want to separate the data in each cell look like this.
我想像这样将每个单元格中的数据分开。
R/V 208 0 32
R/S 184 28 16
R/B 255 88 80
what is the function in excel that I can use for this case. Thank you in advance.
我可以用于这种情况的excel中的功能是什么。先感谢您。
回答by Charles Clayton
kennytm's links are dead and he doesn't provide an example so here's how you do substrings:
kennytm 的链接已失效，他没有提供示例，因此您可以使用以下方法处理子字符串：
=MID(text, start_num, char_num)
Let's say cell A1is Hello.
假设单元格A1是Hello.
=MID(A1, 2, 3)
Would return
会回来
ell
Because it says to start at character 2, e, and to return 3 characters.
因为它说从字符 2, 开始e，并返回 3 个字符。
回答by kennytm
In Excel, the substring function is called MID function, and indexOf is called FINDfor case-sensitive location and SEARCH functionfor non-case-sensitive location. For the first portion of your text parsing the LEFT functionmay also be useful.
在Excel中，子串函数称为MID函数，indexOf对于区分大小写的位置称为FIND，对于不区分大小写的位置称为SEARCH函数。对于解析文本的第一部分，LEFT 函数也可能有用。
See all the text functions here: Text Functions (reference).
在此处查看所有文本函数：文本函数（参考）。
Full worksheet function reference lists available at:
完整的工作表函数参考列表可在以下位置获得：
????Excel functions (by category)
????Excel functions (alphabetical)
？？？Excel 函数（按类别）
???? Excel 函数（按字母顺序）
回答by andyb
Another way you can do this is by using the substitute function. Substitute "(", ")" and "," with spaces. e.g.
另一种方法是使用替代函数。用空格替换 "(", ")" 和 ","。例如
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "(", " "), ")", " "), ",", " ")
回答by bhaskar mudam
I believe we can start from basic to achieve desired result.
我相信我们可以从基础开始达到预期的效果。
For example, I had a situation to extract data after "/". The given excel field had a value of 2rko6xyda14gdl7/VEERABABU%20MATCHA%20IN131621.jpg . I simply wanted to extract the text from "I5" cell after slash symbol. So firstly I want to find where "/" symbol is (FIND("/",I5). This gives me the position of "/". Then I should know the length of text, which i can get by LEN(I5).so total length minus the position of "/" . which is LEN(I5)-(FIND("/",I5)) . This will first find the "/" position and then get me the total text that needs to be extracted. The RIGHT function is RIGHT(I5,12) will simply extract all the values of last 12 digits starting from right most character. So I will replace the above function "LEN(I5)-(FIND("/",I5))" for 12 number in the RIGHT function to get me dynamically the number of characters I need to extract in any given cell and my solution is presented as given below
比如我遇到了在“/”之后提取数据的情况。给定的 excel 字段的值为 2rko6xyda14gdl7/VEERABABU%20MATCHA%20IN131621.jpg 。我只是想从斜杠符号后的“I5”单元格中提取文本。所以首先我想找到“/”符号在哪里（FIND（“/”，I5）。这给了我“/”的位置。然后我应该知道文本的长度，我可以通过LEN（I5）得到.so 总长度减去“/”的位置。这是 LEN(I5)-(FIND("/",I5)) 。这将首先找到“/”位置，然后让我得到需要的总文本提取。RIGHT 函数是 RIGHT(I5,12) 将简单地从最右边的字符开始提取最后 12 位数字的所有值。所以我将替换上面的函数 "LEN(I5)-(FIND("/",I5) )”
The approach was
方法是
=RIGHT(I5,LEN(I5)-(FIND("/",I5))) will give me out as VEERABABU%20MATCHA%20IN131621.jpg . I think I am clear.
=RIGHT(I5,LEN(I5)-(FIND("/",I5))) 会给我作为 VEERABABU%20MATCHA%20IN131621.jpg 。我想我很清楚。
回答by Toffee Tanaka
What about using Replace all? Just replace All on bracket to space. And comma to space. And I think you can achieve it.
使用全部替换怎么样？只需将支架上的 All 替换为空格即可。和逗号到空格。我认为你可以实现它。
