Jan 12 2021 11:25 PM
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 ONLY | NUMBERS ONLY | |
ABC1234 | ||
DEF2354GH | ||
1234KLM0253 |
Could you please help me to understand by how I can achieve the result by using loop in vba.
Thanks
Jan 13 2021 01:55 AM
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,"") ))
Jan 13 2021 09:54 PM
@Sergei Baklan Thanks for your help
Just to let you know that, I was looking for the solution in VBA excel by using for loop function.
Jan 13 2021 10:57 PM
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