If you have =5+10-11/2*3 AS A FORMULA in the activecell. Then you get in
columns by using this macro
5 10 11 2 3


Sub splitNumbersFromFormula()
With ActiveCell
ms = .Formula
'MsgBox ms
ms = Application.Substitute(ms, "=", " ")
ms = Application.Substitute(ms, "+", " ")
ms = Application.Substitute(ms, "-", " ")
ms = Application.Substitute(ms, "/", " ")
ms = Application.Substitute(ms, "*", " ")
ms = Right(ms, Len(ms) - 1)
ActiveCell.Offset(, 1) = ms
'MsgBox ms

Application.DisplayAlerts = False
ActiveCell.Offset(, 1).TextToColumns _
Destination:=ActiveCell.Offset(, 1), _
DataType:=xlDelimited, Space:=True
End With
end sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"april" wrote in message
...
Hi Don,

that's pretty neat - but it's not quite what i wanted. in A1 i have the
formula "+5+10+11". this yields 26 and your macro put a 2 in col b and a
6
in col c. what i want is a 5 in col b, a 10 in col c and an 11 in col d.
any thoughts on that? thanks in advance again.
--
aprilshowers


"Don Guillett" wrote:

One way

Sub getnumsonly()'SAS
Dim i As Long
Dim dlc As Long
Dim mv
For i = 1 To Len(ActiveCell)
dlc = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column + 1
mv = Mid(ActiveCell, i, 1)
If IsNumeric(mv) Then Cells(ActiveCell.Row, dlc) = mv
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"april" wrote in message
...
i have cell with formulas like this "2+5-7" and i want to have a
separate
cell for each number. i have tried using the Cntl ~ function which
displays
the formulas but you can't use functions like @left when the cell is in
that
state. any ideas? thanks in advance for your help
--
aprilshowers