Forum Discussion

Roshan_K's avatar
Roshan_K
Copper Contributor
Jan 13, 2021

Looping through all characters in a cell

Hi,

 

I have a situation where I want to loop through all characters in a cell and wanted to split the numeric and alphabets letters into separate column as given below:

 

 TEXT ONLYNUMBERS ONLY
ABC1234  
DEF2354GH  
1234KLM0253  

 

Could you please help me to understand by how I can achieve the result by using loop in vba.

 

Thanks

Macros and VBA

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 13, 2021

    Roshan_K 

    For such sample

    it could be

    =LET(txt, C4, n, SEQUENCE(LEN(txt)), sym, MID(txt,n,1), k, CODE(sym), TEXTJOIN("",1,IF( (k>57)+(k<48),sym,"") ))

and

=LET(txt, C4, n, SEQUENCE(LEN(txt)), sym, MID(txt,n,1), k, CODE(sym), TEXTJOIN("",1,IF( (k<58)*(k>47),sym,"") ))
    • Roshan_K's avatar
      Roshan_K
      Copper Contributor
      Jan 14, 2021

      SergeiBaklan Thanks for your help

       

      Just to let you know that, I was looking for the solution in VBA excel by using for loop function.

      • JMB17's avatar
        JMB17
        Bronze Contributor
        Jan 14, 2021

        Roshan_K 

         

        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

Resources