VBA分割字符,将空格用其它字符替代

VBA分割字符,将空格用其它字符替代

 

                                            ——Author:东园公

Sheets("printData").Activate

 

With Worksheets("printData")

For i = 11 To lastRows Step 2
   
    k = i + 1
    j = j + 1

   

   '支掉两边空格 

   .Range("A" & j).Value = Trim(Sheets("copySheet1").Range("A" & i).Value)  '

 

   '将空格用"-"替代
   .Range("A" & j).Value = Replace(.Range("A" & j).Value, " ", "-")

   

   '将字符串分割,放在数组中 

   myArray = Split(.Range("A" & j).Value, "-")
       
    .Range("A" & j).Value = myArray(0)
       
    l_len = UBound(myArray, 1)  '获取一维数组长度(也就是维数最大下标)
       
    If IsNumeric(myArray(1)) Then
          .Range("A" & j).Value = myArray(0) & "-" & myArray(1)
          .Range("J" & j).Value = myArray(2)
          .Range("K" & j).Value = myArray(3)
    Else
          .Range("J" & j).Value = myArray(1)
          .Range("K" & j).Value = myArray(2)
    End If
    
    .Range("L" & j).Value = myArray(l_len)  '最后一个值
   

Next i
  
End With