时间戳

首页 > 解决方案 > 删除某个字符后的字符串

excel - 删除某个字符后的字符串

问题描述

我正在尝试将第一个空格后的单词从字符串中的第 30 个字符移动到添加的下一行（i+1），并删除从当前行（i）移到下一行的单词。代码给出运行时错误'5'：在Cells(i, 1).Value = Left(Cells(i, 1), InStr(30, Cells(i, 1), " ") - 1)行。

Sub TextLimit_02()

Dim i As Long
Dim CelLen As Long

 For i = 1 To 50

 CelLen = Len(Cells(i, 1))

  If CelLen > 40 Then

  Rows(i + 1).Insert

  Cells(i + 1, 1).Value = Mid(Cells(i, 1), InStr(30, Cells(i, 1), " ") + 1, Len(Cells(i, 
  1).Value) - InStr(30, Cells(i, 1), " "))

  Cells(i, 1).Value = Left(Cells(i, 1), InStr(30, Cells(i, 1), " ") - 1)

 Else

 End If

Next i


End Sub

标签： excelvba

解决方案

这段代码应该做你想做的事。请测试它，让我知道它做错了什么。

Sub TextLimit_02()
    ' 269
    
    Dim Txt     As String               ' text
    Dim Tail    As String               ' tail end of Txt to be moved
    Dim p       As Long                 ' position of character in string
    Dim R       As Long                 ' loop counter: rows

    Application.ScreenUpdating = False
    ' start from the end of column A to 2nd row
    For R = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
        Txt = Trim(Cells(R, "A").Value)         ' remove leading/trailing blanks
        p = InStr(Mid(Txt, 30), " ")
        Tail = Trim(Mid(Txt, p + 30))           ' in case of double space
        If Len(Tail) Then                       ' skip if no Tail
            Cells(R, "A").Value = Left(Txt, p + 29)
            Rows(R + 1).Insert
            Cells(R + 1, "A").Value = Tail
        End If
    Next R
    Application.ScreenUpdating = True
End Sub

推荐阅读