Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
split contents of cell
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
split contents of cell
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
split contents of cell
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
split contents of cell
On Wed, 27 May 2009 12:34:01 -0700, april
wrote: 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I split contents of cell with no delimiter | Excel Discussion (Misc queries) | |||
VBA to split contents of cell | Excel Discussion (Misc queries) | |||
Excel 2000 - Split Contents of Cell Across Multiple Cells | Excel Discussion (Misc queries) | |||
Split contents of a cell | Excel Worksheet Functions | |||
How to split the contents of a cell between two cells. | Excel Worksheet Functions |