split contents of cell

A

april

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
 
D

Don Guillett

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
 
A

april

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.
 
R

Ron Rosenfeld

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
Click to expand...


You need to use VBA.

Here's one example:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), Select the range to process. Then <alt-F8> opens the
macro dialog box. Select the macro by name, and <RUN>.


=================================
Option Explicit
Sub SplitNums()
Dim rg As Range, c As Range
Dim S As String
Dim i As Long
Set rg = Selection 'or whatever
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\b\d*\.?\d+\b"
For Each c In rg
Range(c.Offset(0, 1), c.Offset(0, 10)).Clear
S = c.Formula
If re.test(S) = True Then
Set mc = re.Execute(S)
i = 1
For Each m In mc
c.Offset(0, i).Value = m.Value
i = i + 1
Next m
End If
Next c
End Sub
=================================
--ron
 
D

Don Guillett

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
 
Top