vba Excel解析单元格值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8894646/
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
Excel parse cell value
提问by thedev
I have placed the following in cell A1:
我在单元格A1中放置了以下内容：
"a lot of text marker: xxx some more text"
"a lot of text marker: xxx some more text"
I would like to copy the xxxvalue into cell A2.
我想将xxx值复制到 cell 中A2。
Any suggestions on how this could be done?
关于如何做到这一点的任何建议？
Thanks
谢谢
回答by shahkalpesh
=MID(A1, FIND("marker:",A1) + LEN("marker:"), 4)
I am assuming that the xxx (per your example) is 3 characters long and a space is present between "marker:" and "xxx".
我假设 xxx（根据您的示例）长度为 3 个字符，并且“标记：”和“xxx”之间存在空格。
回答by Siddharth Rout
Just my two cents. Find() is case sensitive so if the text in A1 is
只有我的两分钱。Find() 区分大小写，所以如果 A1 中的文本是
"a lot of text Marker: xxx some more text"
“很多文字标记：xxx一些更多文字”
Then Find will give you an error.
然后 Find 会给你一个错误。
You can use Search() in lieu of FIND()
您可以使用 Search() 代替 FIND()
=MID(A1, SEARCH("marker: ",A1) + LEN("marker: "), 3)
=MID(A1, SEARCH("marker: ",A1) + LEN("marker: "), 3)
Also depending upon your regional settings you might have to use ";" instead of ","
此外，根据您的区域设置，您可能必须使用“;” 代替 ”，”
回答by JimmyPena
If you wanted a VBA solution, this worked for me using your sample input:
如果你想要一个 VBA 解决方案，这对我来说使用你的示例输入：
Function GetValue(rng As Excel.Range) As String
Dim tempValue As String
Dim arrValues() As String
' get value from source range
tempValue = rng.value
' split by ":" character
arrValues = Split(tempValue, ":")
' split by spaces and take the second array element
' because there is a space between ":" and "xxx"
GetXXXValue = Trim$(Split(arrValues(1), " ")(1))
End Function
To use, put this code into the sheet module (see Where do I paste the code that I want to use in my workbookfor placement assistance) and then put the following into cell A2:
要使用，请将此代码放入工作表模块（请参阅我在哪里粘贴我想在工作簿中使用的代码以获得放置帮助），然后将以下内容放入单元格 A2：
=GetValue(A1)
=GetValue(A1)
