vba Excel INSTR 在单元格中找到硬返回
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15075169/
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 INSTR to find hard return in cell
提问by Keith Graham
I have a spreadsheet where the Item description is a variable length but always finishes with a "hard return" to force a wrapped line in the cell. I need to copy the first line of the cell into another cell.
我有一个电子表格，其中项目描述是可变长度，但总是以“硬返回”结束以强制在单元格中换行。我需要将单元格的第一行复制到另一个单元格中。
Can I use INSTR to find the first instance of a hard return (invisible character) and then copy the first N-1 characters?
我可以使用 INSTR 查找硬返回（不可见字符）的第一个实例，然后复制前 N-1 个字符吗？
Dell PowerEdge R720XD (<- hard return here) Chassis (Max of ......
Dell PowerEdge R720XD（<-此处硬返回）机箱（最大......
OR Dell OptiPlex 7010 Minitower (<- hard return here) Intel Core.............
或 Dell OptiPlex 7010 Minitower（<- 此处硬返回）英特尔酷睿 .....................
In all cases I need to copy the first line of the text in the cell, irrespective of length.
在所有情况下，无论长度如何，我都需要复制单元格中文本的第一行。
Any ideas how I could do this??
任何想法我怎么能做到这一点？
Thanks
谢谢
Keith
基思
回答by Peter Albert
Yes, you can easily do this
是的，您可以轻松做到这一点
strShort = Left(strLong, InStr(strLong, vbCrLf) - 1)
Some times (eep. when sourced from a Unix system), you might have to replace vbCrLf(carriage Return, LineFeed) with a vbLfonly.
有时（例如，当来自 Unix 系统时），您可能需要将vbCrLf(回车、换行) 替换为vbLfonly。
If you are not sure if it contains an Enter, this code will do
如果您不确定它是否包含 Enter，则此代码将执行
strShort = IIf(InStr(strLong, vbCrLf), Left(strLong, InStr(strLong, vbCrLf) - 2), strLong)
回答by Ripster
Depending on the type of line break you can do it this way:
根据换行符的类型，您可以这样做：
InStr(Range("A1").Text, vbLf)
InStr(Range("A1").Text, vbCr)
InStr(Range("A1").Text, vbCrLf)
to get the text before the line break:
在换行符之前获取文本：
Left(Range("A1").Text, InStr(Range("A1").Text, vbLf) - 1)
to get the text after the line break:
获取换行符后的文本：
Right(Range("A1").Text, InStr(Range("A1").Text, vbLf))
回答by awsmitty
I was not able to add a comment to Ripster's code below. I needed a reputation of 50; mine's only 36, but that's really where this comment should go instead of a separate answer.
我无法在下面的 Ripster 代码中添加注释。我需要 50 的声望；我的只有 36 岁，但这确实是这条评论应该去的地方，而不是单独的答案。
It seems to me that the last bit of Ripster's code,
在我看来，Ripster 代码的最后一点，
Right(Range("A1").Text, InStr(Range("A1").Text, vbLf))
should be,
应该，
Right(Range("A1").Text, (Len(Range("A1")-InStr(Range("A1").Text, vbLf)) - 1)
And finally to regenerate the string without the break
最后在没有中断的情况下重新生成字符串
strString = Left(Range("A1").Text, InStr(Range("A1").Text, vbLf) - 1) & Right(Range("A1").Text, (Len(Range("A1")-InStr(Range("A1").Text, vbLf)) - 1)
A note to the moderators : Please review this. I still consider mysefl as a beginner to VBA and my logic could be wrong.
给版主的提示：请查看此内容。我仍然认为 mysefl 是 VBA 的初学者，我的逻辑可能是错误的。
回答by Jan
my solution is:
我的解决方案是：
strString = Replace(strString, vbLf, "")
strString = Replace(strString, vbLf, "")
It is useful even if the string contains more than one line brakes.
即使字符串包含多个线路刹车，它也很有用。
