Add space between alphabets and numbers

[Ks1102]

Dear Expert
can u help how to adding space if letter and number ? find the code below it just can only be added in last character's.

Example : the doe just be done "

postal620332 ste"

How can it done become "postal 620332 ste"

postal620332 ste

Sub InsertSpaceBetweenDigitAndLetter()
  Dim X As Long, Z As Long, LastRow As Long, vArr As Variant
  Const DataColumn As String = "A"
  Const StartRow As Long = 1
  LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
  vArr = Cells(StartRow, DataColumn).Resize(LastRow - StartRow + 1).Value
  For X = 1 To UBound(vArr)
    For Z = Len(vArr(X, 1)) - 1 To 1 Step -1
      If Mid(vArr(X, 1), Z, 1) Like "#" And Mid(vArr(X, 1), Z + 1, 1) Like "[A-Za-z]" Then
        vArr(X, 1) = Left(vArr(X, 1), Z) & " " & Mid(vArr(X, 1), Z + 1)
      End If
    Next
  Next
  Cells(StartRow, DataColumn).Resize(LastRow - StartRow + 1).NumberFormat = "@"
  Cells(StartRow, DataColumn).Resize(LastRow - StartRow + 1) = vArr
End Sub

 

[Rick Rothstein]

Try your code this way...

Sub InsertSpaceBetweenDigitAndLetter()
  Dim X As Long, Z As Long, LastRow As Long, vArr As Variant
  Const DataColumn As String = "A"
  Const StartRow As Long = 1
  LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
  With Cells(StartRow, DataColumn).Resize(LastRow - StartRow + 1)
    vArr = .Value
    For X = 1 To UBound(vArr)
      For Z = Len(vArr(X, 1)) - 1 To 1 Step -1
        If Mid(vArr(X, 1), Z, 2) Like "#[A-Za-z]" Or Mid(vArr(X, 1), Z, 2) Like "[A-Za-z]#" Then
          vArr(X, 1) = Left(vArr(X, 1), Z) & " " & Mid(vArr(X, 1), Z + 1)
        End If
      Next
    Next
    .NumberFormat = "@"
    .Cells = vArr
  End With
End Sub

 

[Ks1102]

Try your code this way...

Sub InsertSpaceBetweenDigitAndLetter()
  Dim X As Long, Z As Long, LastRow As Long, vArr As Variant
  Const DataColumn As String = "A"
  Const StartRow As Long = 1
  LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
  With Cells(StartRow, DataColumn).Resize(LastRow - StartRow + 1)
    vArr = .Value
    For X = 1 To UBound(vArr)
      For Z = Len(vArr(X, 1)) - 1 To 1 Step -1
        If Mid(vArr(X, 1), Z, 2) Like "#[A-Za-z]" Or Mid(vArr(X, 1), Z, 2) Like "[A-Za-z]#" Then
          vArr(X, 1) = Left(vArr(X, 1), Z) & " " & Mid(vArr(X, 1), Z + 1)
        End If
      Next
    Next
    .NumberFormat = "@"
    .Cells = vArr
  End With
End Sub

Wow ... it's awesome .. thanks so much