فصل القيم النصية والرقمية Split Text & Number

Public Function SplitText(WorkRng As Range, Number As Boolean) As String  

   Dim xLen As Long  

   Dim xStr As String  

   xLen = VBA.Len(WorkRng.Value)  

   For i = 1 To xLen  

     xStr = VBA.Mid(WorkRng.Value, i, 1)  

     If ((VBA.IsNumeric(xStr) And Number) Or (Not (VBA.IsNumeric(xStr)) And Not (Number))) Then  

       SplitText = SplitText + xStr  

     End If  

   Next  

 End Function  

  =SplitText($A2;0)

=SplitText($A2;False)

=SplitText($A2;1)

=SplitText($A2;TRUE)

 Sub split_Text()  

   Dim xLen As Long  

   Dim xStr As String  

   Dim Rng As Range  

   For Each Rng In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)  

     xLen = VBA.Len(Rng.Value)  

     Rng.Offset(, 4).ClearContents  

     For i = 1 To xLen  

       xStr = VBA.Mid(Rng.Value, i, 1)  

       If Not (VBA.IsNumeric(xStr)) And Not (Number) Then  

         Rng.Offset(, 4) = Rng.Offset(, 4) + xStr  

       End If  

     Next i  

   Next Rng  

 End Sub  

 Sub Split_NUM()  

   Dim xLen As Long  

   Dim xStr As String  

   Dim Rng As Range  

   For Each Rng In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)  

     xLen = VBA.Len(Rng.Value)  

     Rng.Offset(, 5).ClearContents  

     For i = 1 To xLen  

       xStr = VBA.Mid(Rng.Value, i, 1)  

       If (VBA.IsNumeric(xStr)) And Not (Number) Then  

         Rng.Offset(, 5) = Rng.Offset(, 5) & xStr  

       End If  

     Next i  

   Next Rng  

 End Sub