When dealing with data in Excel, there are situations, where you have both numbers and text in the same cells. Sometimes you may need to extract (or show separately) numbers and text, in order to easier analyze those values. In such cases I would recommend using the following macro to make such a task easier and efficient.
Sub ExtractBoth()
'Extracts separately numerical & non-numerical characters from a string in an active cell
Dim exTxt As String
Dim exNum As Double
Dim myStr As String
myStr = ActiveCell.Value
Dim outp As String
For i = 1 To Len(myStr)
ActiveCell.Offset(, 1).Value = exNum
ActiveCell.Offset(, 2).Value2 = exTxt
outp = MsgBox("Extracted number: " & exNum & vbNewLine & "Extracted text: " & exTxt, , "Extracted digits and text")
End Sub
'Extracts separately numerical & non-numerical characters from a string in an active cell
Dim exTxt As String
Dim exNum As Double
Dim myStr As String
myStr = ActiveCell.Value
Dim outp As String
For i = 1 To Len(myStr)
If Val(Mid(myStr, i, 1)) Or Mid(myStr, i, 1) =
"0" Then
exNum = exNum & Mid(myStr, i, 1)
Else
exTxt = exTxt & Mid(myStr, i, 1)
End If
exNum = exNum & Mid(myStr, i, 1)
Else
exTxt = exTxt & Mid(myStr, i, 1)
End If
Next i
ActiveCell.Offset(, 1).Value = exNum
ActiveCell.Offset(, 2).Value2 = exTxt
outp = MsgBox("Extracted number: " & exNum & vbNewLine & "Extracted text: " & exTxt, , "Extracted digits and text")
End Sub
If your active cell is
located e.g. in cell A1 and contains string like this one:
ab&400er=29>-23sdrk@j89:?
then the macro separates it into two parts
like these:
400292389 and
ab&er=>-sdrk@j:?
and displays them in cells B1 and C1. Then,
optionally, it shows them for you in a message box.
The macro code can be easily
adapted to your specific needs.
