如何从换行的单元格中获取前两行文本?

问题描述：

我需要从Excel中的包装单元格中提取文本的前两行.例如，一个包装好的Excel单元格包含以下文本:

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:

wrapedtext1

wrappedtext2

wrappedtext3

wrappedtext4

我只需要前两行作为'wrapedtext1wrappedtext2'.有可能吗?

I need only the first two lines as 'wrapedtext1wrappedtext2'. Is it possible?

答

我只需要将前两行作为'wrapedtext1wrappedtext2'就可以了吗?

I only need to get first two lines as 'wrapedtext1wrappedtext2' .Is it possible???

是的，可能是可能的，但是没有简单的方法可以实现.您需要考虑很多因素.

Yes it might be possible but there is NO SIMPLE way to achieve it. There are lot of factors that you will have to consider.

1)行高(以像素为单位)

1) Row Height in Pixels

2)字体类型和大小

3)行距

4)细胞合并了吗?

5)单元格是否处于自动适应"状态

5) Is the cell in Autofit state

6)是否所有文本都处于普通"模式，或者是否具有任何粗体，斜体，下划线等?

6) Is all the text in Normal mode or does it have any Bold/Italics/Underline character(s) etc etc

考虑此快照

例如，以像素为单位的行高可以源自

For example, Row Height in Pixels can be derived from

Debug.Print Range("A1").Height * (24 / 18)

在上述情况下的字体大小可以通过此实现

Font Size in the above case can be achieved from this

Debug.Print Range("A1").Font.Size

但是挑战是在以下情况下会发生什么?

But the challenge is what would happen in the below scenario?

在我看来，实现您想要的东西太痛苦了.最好的部分是使用ALT + Enter插入换行符，然后检索文本.

In my opinion, it would be too much of a pain to achieve what you want. The best part would be to use ALT + Enter to insert line breaks and then retrieve the text.

关注

通过vba代码将字符串输入到包装的单元格中.那么如何通过按alt + Enter插入数据? – 1355 4小时前

The strings are entered into the wrapped cell through vba code. So How will insert data by pressing alt + enter? – 1355 4 hours ago

在这种情况下，您也可以采用类似的方法.

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

注意:对于上面的，您将必须记录一个宏，并查看可以采用特定格式的字体，字体大小和列宽是什么.同样，您将不得不考虑这样一个事实，即我上面给出的示例是针对新工作表中未格式化的单元格的.如果要写入合并的单元格或按格式设置的单元格，则必须相应地更改上述代码，这可以通过记录宏轻松实现. 我还假设ZOOM级别设置为100％

NOTE: For the above you will have to record a macro and 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%

快照

HTH

Sid

如何从换行的单元格中获取前两行文本?

相关推荐