Split values in one cell and place them in different cells

[Guest]

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

 

[Guest]

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

 

[kounoike]

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

 

[Guest]

Hi,

It worked well.
Thanks,

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

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

 

[Guest]

Hi,

This code has worked. This stores any prefixes to [] (like @sum[11]...) in a
different cell rather than eliminating them.

Thanks,

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/

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