vba 如何从换行的单元格中获取前两行文本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9889002/
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
How to get first two lines of text from a wraped cell?
提问by divz
I need to take the first two lines of text from a wrapped cell in Excel. For example, a wrapped Excel cell contains the text as follows:
我需要从 Excel 中包装好的单元格中取出前两行文本。例如，包装好的 Excel 单元格包含如下文本：
wrapedtext1
wrappedtext2
wrappedtext3
wrappedtext4
I need only the first two lines as 'wrapedtext1wrappedtext2'. Is it possible?
我只需要前两行作为'wrapedtext1wrappedtext2'。是否可以？
采纳答案by Siddharth Rout
I only need to get first two lines as 'wrapedtext1wrappedtext2' .Is it possible???
我只需要将前两行作为 'wrapedtext1wrappedtext2' 。这可能吗？？？
Yes it mightbe possible but there is NO SIMPLEway to achieve it. There are lot of factors that you will have to consider.
是的，这可能是可能的，但没有简单的方法来实现它。您必须考虑很多因素。
1) Row Height in Pixels
1) 以像素为单位的行高
2) Font Type and Size
2) 字体类型和大小
3) Line Spacing
3) 行距
4) Is the Cell Merged?
4) 单元格是否合并？
5) Is the cell in Autofit state
5) 单元格是否处于 Autofit 状态
6) Is all the text in Normal mode or does it have any Bold/Italics/Underline character(s) etc etc
6）所有文本都处于正常模式还是有任何粗体/斜体/下划线字符等
Consider this snapshot
考虑这个快照
For example, Row Height in Pixels can be derived from
例如，以像素为单位的行高可以从
Debug.Print Range("A1").Height * (24 / 18)
Debug.Print Range("A1").Height * (24 / 18)
Font Size in the above case can be achieved from this
上述情况下的字体大小可以由此实现
Debug.Print Range("A1").Font.Size
Debug.Print Range("A1").Font.Size
But the challenge is what would happen in the below scenario?
但挑战是在下面的场景中会发生什么？
In my opinion, it would be too much of a pain to achieve what you want. The best part would be to use ALT + Enterto insert line breaks and then retrieve the text.
在我看来，实现你想要的东西太痛苦了。最好的部分是用于ALT + Enter插入换行符，然后检索文本。
FOLLOWUP
跟进
The strings are entered into the wrapped cell through vba code. So How will insert data by pressing alt + enter? – 1355 4 hours ago
字符串通过 vba 代码输入到包装的单元格中。那么如何通过按 alt + enter 插入数据呢？– 1355 4 小时前
In such a scenario, you can take a similar approach as well.
在这种情况下，您也可以采用类似的方法。
Sub Sample()
Dim strg As String
strg = "This is a sample" & vbCrLf & _
"sentence which is" & vbCrLf & _
"in Cell A1 and the" & vbCrLf & _
"text is separated" & vbCrLf & _
"with line breaks"
With Range("A1")
.Columns(1).ColumnWidth = 16.86
.Font.Name = "Calibri"
.Font.Size = 11
.Value = strg
End With
End Sub
NOTE: For the above you will have to record a macroand see what is the font, font size and column width that can take a particular formatting. Again, you will have to consider the fact that the example that I have given above is for a non formatted cell in a new sheet. If you are writing to a merged cell or a per-formatted cell then you will have to change the above code accordingly which can be easily achieved by recording a macro. I am also assuming that the ZOOM level is set to 100%
注意：对于上述情况，您必须录制一个宏并查看可以采用特定格式的字体、字体大小和列宽是什么。同样，您必须考虑这样一个事实，即我上面给出的示例适用于新工作表中的非格式化单元格。如果您要写入合并的单元格或按格式设置的单元格，则必须相应地更改上述代码，这可以通过录制宏轻松实现。我还假设 ZOOM 级别设置为 100%
SNAPSHOTS
快照
HTH
HTH
Sid
锡德
回答by user2628294
Use the FIND function:
使用查找功能：
=LEFT(B2,(FIND(CHAR(10),B2)))
=LEFT(B2,(FIND(CHAR(10),B2)))
- FIND searches for the line break character (CHAR(10)
- LEFT gives you the first X characters in B2, up to the number where it finds the line break
- FIND 搜索换行符 (CHAR(10)
- LEFT 为您提供 B2 中的前 X 个字符，直到找到换行符的数字
回答by William Niu
You can do some complicated formula in Excel to achieve this without VBA code.
您可以在 Excel 中执行一些复杂的公式来实现这一点，而无需 VBA 代码。
Assuming the multi-line text is in A1. If you just want the first two lines, you can do this:
假设多行文本在 A1 中。如果你只想要前两行，你可以这样做：
Get the first line:
获取第一行：
=MID(A1,1,IF(2=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1,LEN(A1),SEARCH("@",SUBSTITUTE(A1,CHAR(10),"@",2))))
Get the second line:
获取第二行：
=MID(A1,SEARCH("@",SUBSTITUTE(A1,CHAR(10),"@",1))+1,IF(2=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1,LEN(A1),SEARCH("@",SUBSTITUTE(A1,CHAR(10),"@",2)))-SEARCH("@",SUBSTITUTE(A1,CHAR(10),"@",1)))
Then simply use CONCATENATE()to combine those two.
然后简单地使用CONCATENATE()将这两者结合起来。
Have a look at this site that allow you to specify which line to get: http://www.excelblog.ca/separating-lines-from-multi-line-excel-cell/
看看这个站点，它允许您指定要获取的行：http: //www.excelblog.ca/separating-lines-from-multi-line-excel-cell/
回答by Moosli
Hi ok there are two ways how to do it with the VBA Code.
嗨，好的，有两种方法可以使用 VBA 代码来实现。
First way. If the Lines are Separated with a space
第一种方式。如果行之间用空格分隔
Dim avarSplit As Variant
'If separated with a space
avarSplit = Split(Worksheets(2).Range("A7").Value, " ")
second way if they are separated with a break.
第二种方式，如果他们分开休息。
Dim avarSplit2 As Variant
'separated with a break
avarSplit2 = Split(Worksheets(2).Range("A8").Value, Chr(10))
Then you get an array where all the lines are seperated and you can just read them out...
然后你会得到一个数组，其中所有的行都是分开的，你可以把它们读出来......
Moosli
穆斯利
