减少单元格中的换行次数/chr(10) [英] Reducing number of Line Breaks/chr(10) in a Cell

问题描述

我有一个excel工作表，其中的单元格具有不同数量的换行符，并且我想减少它，以便每行之间只有一个换行符.

I have an excel sheet that has cells with variable amounts of line breaks and I want to reduce it so that there is only one line break between each new line.

例如

HELLO




WORLD



GOODBYE

将修改为:

HELLO 

WORLD

GOODBYE

我已经花了几个小时来思考，并提出了几种方法，但是没有一种是非常有效的或产生最佳结果的.

I've been banging my head over this for hours and have come up with a few ways but none are very efficient or produce the best results.

这变得特别困难，因为我正在使用在换行符之前有空格的数据集.

This is made especially difficult because I'm working with a dataset that has spaces preceeding the Line Breaks.

因此常规解析也无法正常工作.

And so a regular parse doesn't work as well.

我试图用~替换单元格中所有chr(10)实例，以使其更易于使用，但是我仍然无法获得确切的数量.我想知道是否有更好的方法.

I've tried to replace all the instances of chr(10) in the cell with ~ to make it easier to work with, however i'm still not getting it to an exact amount. I'm wondering if there are better ways.

这是我到目前为止所拥有的:

here is what I have so far:

 myString = Replace(myString, Chr(10), "~")

    Do While InStr(myString, "~~") > 0
        str1 = Split(myString, "~")
        For k = 0 To UBound(str1)
        myString = Replace(myString, "~~", "~")
        Next k
    Loop

    Do While InStr(myString, "   ~") > 0
        str1 = Split(myString, "~")
        For k = 0 To UBound(str1)
        myString = Replace(myString, "  ~", "")
        Next k
    Loop

myString = Replace(myString, "   ~", " ~")
myString = Replace(myString, " ~", "~")
myString = Replace(myString, "~", Chr(10))

Cells(2, 2).Value = myString

因此，我正在使用一些do while循环来捕获不同类型的换行符(或在这种情况下为波浪号)的实例，但是我认为这不是解决此问题的最佳方法.

So i'm using a few do while loops to catch instances of different types of line breaks (or in this case, tildes) but I don't think this is the best way to tackle this.

我在想办法遍历单元格中的字符，如果存在一个实例，其中有多个chr(10)，请用""替换它.

I was thinking of ways to loop through the characters in the cell, and if there is an instance where there is more than one chr(10), replace it with "".

所以伪代码看起来像:

for i to len(mystring)
    if mystring(i) = chr(10) AND myString(i+1) = chr(10) Then
       myString(i + 1) = ""

但是不幸的是，我认为通过vba不可能做到这一点.

but unfortunately I don't think this is possible through vba.

如果有人能帮助我调整当前代码或通过上述伪代码帮助我，将不胜感激！

If anyone is kind enough to help me adjust my current code or assist me with the aforementioned psuedocode, it would be greatly appreciated!

推荐答案

您可以使用公式进行操作:

You can do it with a formula:

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1)," ","|"),"|"&CHAR(10)," "),CHAR(10)," "))," ",CHAR(10)),"|"," ")

这会将所有空格更改为|，然后将Char(10)更改为空格.饰边去除了多余的空间.我们将空格反转为Char(10)，将|反转为空格.

This changes all the spaces to | and then the Char(10) to spaces. The trim removes the extra spaces. The we reverse, space to Char(10) and | to spaces.

VBA:

Function manytoone(str As String)
    str = Replace(Application.Trim(str), " ", "|")
    str = Replace(str, "|" & Chr(10), " ")
    str = Replace(str, Chr(10), " ")
    str = Application.Trim(str)
    str = Replace(str, " ", Chr(10))
    str = Replace(str, "|", " ")
    manytoone = str

End Function

这篇关于减少单元格中的换行次数/chr(10)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！