Separating numbers and alphabet into different cells.

[omairhe]

Hello,

I have data in cell ref A1 which look something like this. ..

Abc def 47

so a formula in cell B1 should output value = Abc def and similarly in cell C1 output = 47

more examples:-

A1 = Abc
B1 = Abc
C1 =

A1 = Abc 5 0 1
B1 = Abc
C1 = 501

A1 = 50 0
B1 =
C1 = 500

A1 = Abc 50 40 Xyz 5
B1 = Abc Xyz
C1 = 50405


Thank u.

 

[ranman256]

Sub aParse1()
Dim vTxt, vNum, vWord, vChr
Dim i As Integer


Range("a1").Select
While ActiveCell.Value <> ""
   vWord = ActiveCell.Value
   For i = 1 To Len(vWord)
      vChr = Mid(vWord, i, 1)
      If IsNumeric(vChr) Then
         vNum = vNum & vChr
      Else
         vTxt = vTxt & vChr
      End If
   Next
   ActiveCell.Offset(0, 1).Value = vTxt
   ActiveCell.Offset(0, 2).Value = vNum
   vTxt = ""
   vNum = ""
   
   NextRow
Wend


End Sub


Private Sub NextRow()
ActiveCell.Offset(1, 0).Select
End Sub

 

[omairhe]

Hello ranman356,

Would it be possible for you to please make two different formulas that goes in B1 and C1.

I,m actually aiming for the formula..

Appreciate it...