Looping through all characters in a cell

Bronze Contributor

Jan 14, 2021

If you are still interested in a vba approach, I believe this may help:

Public Sub Macro1()
     Dim cell As Range
     Dim numbers As String
     Dim letters As String
     
     
     On Error GoTo ErrHandler
     
     For Each cell In Selection.Cells
          If SplitText(cell.Value, numbers, letters) Then
               cell(1, 2).Value = letters
               cell(1, 3).Value = numbers
          End If
     Next cell
     
ExitProc:
     Exit Sub
     
ErrHandler:
     MsgBox "Error " & Err.Number & ": " & Err.Description
     Resume ExitProc
     
End Sub



Private Function SplitText(ByVal textIn As String, Optional ByRef retNum As String, Optional ByRef retText As String) As Boolean
     Dim i As Long
     
     On Error GoTo ErrHandler
     
     If Not IsMissing(retNum) Then retNum = vbNullString
     If Not IsMissing(retText) Then retText = vbNullString
     
     For i = 1 To Len(textIn)
          If IsNumeric(Mid(textIn, i, 1)) Then
               If Not IsMissing(retNum) Then
                    retNum = retNum & Mid(textIn, i, 1)
               End If
          Else
               If Not IsMissing(retText) Then
                    retText = retText & Mid(textIn, i, 1)
               End If
          End If
     Next i
          
     
     SplitText = True
          
     
ExitProc:
     Exit Function
     
ErrHandler:
     SplitText = False
     If Not IsMissing(retNum) Then retNum = vbNullString
     If Not IsMissing(retText) Then retText = vbNullString
     Resume ExitProc
         
End Function

Forum Discussion

Looping through all characters in a cell

Resources