Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split values in one cell and place them in different cells
I have a value say @Sum([aa] [bb] [cc] [dd] [ee]) in cell A1.
I want to place the cursor in cell A2 and run a function which will remove the brackets and place the values in cells as follows: A2- aa A3- bb A4- cc A5- dd A6- ee Thanks, Nagesh |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split values in one cell and place them in different cells
Nagesh:
try, Sub test() Dim a As Variant a = VBASplit(Range("A1"), " ") For IDX = 1 To UBound(a) Cells(IDX + 1, 1) = a(IDX) Next End Sub Public Function VBASplit(InputText As String, Delimiter As String) As Variant Const CHARS = ".!?,;:""'()[]{}" Dim strReplacedText As String Dim intIndex As Integer strReplacedText = Trim(Replace(InputText, vbTab, " ")) For intIndex = 1 To Len(CHARS) strReplacedText = Trim(Replace(strReplacedText, _ Mid(CHARS, intIndex, 1), " ")) Next intIndex Do While InStr(strReplacedText, " ") strReplacedText = Replace(strReplacedText, _ " ", " ") Loop If Len(Delimiter) = 0 Then VBASplit = VBA.Split(strReplacedText) Else VBASplit = VBA.Split(strReplacedText, Delimiter) End If End Function -- 天行健,君*以自強不息 地勢坤,君*以厚德載物 http://www.vba.com.tw/plog/ "Nagesh" wrote: I have a value say @Sum([aa] [bb] [cc] [dd] [ee]) in cell A1. I want to place the cursor in cell A2 and run a function which will remove the brackets and place the values in cells as follows: A2- aa A3- bb A4- cc A5- dd A6- ee Thanks, Nagesh |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split values in one cell and place them in different cells
select the cell which contains data, and run mytest.
it will put out strings enclosed by "[]" below that cell, maybe. but not sure to statify your condition. Sub mytest() Dim i As Long If Not IsError(Mysplit(ActiveCell)) Then For i = LBound(Mysplit(ActiveCell)) To UBound(Mysplit(ActiveCell)) ActiveCell.Offset(i + 1, 0) = Mysplit(ActiveCell)(i) Next End If End Sub Function Mysplit(ByVal s) As Variant Dim tmp Dim s1 As String Dim i As Long Dim instate As Boolean Const del1 = "[" Const del2 = "]" instate = False i = 1 Do s1 = Mid(s, i, 1) If instate And s1 < del2 Then tmp = tmp & s1 ElseIf s1 = del1 Then instate = True ElseIf s1 = del2 Then instate = False tmp = tmp & Chr(1) End If i = i + 1 Loop While (i < Len(s)) If Right(tmp, 1) = Chr(1) Then tmp = Left(tmp, Len(tmp) - 1) End If Mysplit = split(tmp, Chr(1)) End Function keizi "Nagesh" wrote in message ... I have a value say @Sum([aa] [bb] [cc] [dd] [ee]) in cell A1. I want to place the cursor in cell A2 and run a function which will remove the brackets and place the values in cells as follows: A2- aa A3- bb A4- cc A5- dd A6- ee Thanks, Nagesh |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split values in one cell and place them in different cells
Hi,
It worked well. Thanks, "kounoike" wrote: select the cell which contains data, and run mytest. it will put out strings enclosed by "[]" below that cell, maybe. but not sure to statify your condition. Sub mytest() Dim i As Long If Not IsError(Mysplit(ActiveCell)) Then For i = LBound(Mysplit(ActiveCell)) To UBound(Mysplit(ActiveCell)) ActiveCell.Offset(i + 1, 0) = Mysplit(ActiveCell)(i) Next End If End Sub Function Mysplit(ByVal s) As Variant Dim tmp Dim s1 As String Dim i As Long Dim instate As Boolean Const del1 = "[" Const del2 = "]" instate = False i = 1 Do s1 = Mid(s, i, 1) If instate And s1 < del2 Then tmp = tmp & s1 ElseIf s1 = del1 Then instate = True ElseIf s1 = del2 Then instate = False tmp = tmp & Chr(1) End If i = i + 1 Loop While (i < Len(s)) If Right(tmp, 1) = Chr(1) Then tmp = Left(tmp, Len(tmp) - 1) End If Mysplit = split(tmp, Chr(1)) End Function keizi "Nagesh" wrote in message ... I have a value say @Sum([aa] [bb] [cc] [dd] [ee]) in cell A1. I want to place the cursor in cell A2 and run a function which will remove the brackets and place the values in cells as follows: A2- aa A3- bb A4- cc A5- dd A6- ee Thanks, Nagesh |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split values in one cell and place them in different cells
Hi,
This code has worked. This stores any prefixes to [] (like @sum[11]...) in a different cell rather than eliminating them. Thanks, "chijanzen" wrote: Nagesh: try, Sub test() Dim a As Variant a = VBASplit(Range("A1"), " ") For IDX = 1 To UBound(a) Cells(IDX + 1, 1) = a(IDX) Next End Sub Public Function VBASplit(InputText As String, Delimiter As String) As Variant Const CHARS = ".!?,;:""'()[]{}" Dim strReplacedText As String Dim intIndex As Integer strReplacedText = Trim(Replace(InputText, vbTab, " ")) For intIndex = 1 To Len(CHARS) strReplacedText = Trim(Replace(strReplacedText, _ Mid(CHARS, intIndex, 1), " ")) Next intIndex Do While InStr(strReplacedText, " ") strReplacedText = Replace(strReplacedText, _ " ", " ") Loop If Len(Delimiter) = 0 Then VBASplit = VBA.Split(strReplacedText) Else VBASplit = VBA.Split(strReplacedText, Delimiter) End If End Function -- 天行健,君*以自強不息 地勢坤,君*以厚德載物 http://www.vba.com.tw/plog/ "Nagesh" wrote: I have a value say @Sum([aa] [bb] [cc] [dd] [ee]) in cell A1. I want to place the cursor in cell A2 and run a function which will remove the brackets and place the values in cells as follows: A2- aa A3- bb A4- cc A5- dd A6- ee Thanks, Nagesh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Split values in cells to variables | Excel Discussion (Misc queries) | |||
Macro to Split Window in Same Place Even If Columns Added or Hidde | Excel Discussion (Misc queries) | |||
Split values from one cell to dif. cells | Excel Discussion (Misc queries) | |||
Unmerge cells and place contents of first cell in all cells | Excel Programming | |||
Split cell values based on content | Excel Worksheet Functions |