PDA

View Full Version : [SOLVED] Can't use Split correctly



ValerieT
03-03-2015, 09:33 AM
I try to learn the Split function but I am stuck.

I would like to copya list of IDs (in a string) into a column with one single Id per cells
example: "1;2;3" becomes:
1
2
3

I've read something but can't apply it (you don't have to follow this way if you know better)

String "1; 2; 3; 4; 5; 6;" is in Cells(1,1)
and the separator ";" indicated in cells (2,1) (can be various like ";" or "," or "|") so I guess it is easier to ask the user to indicate it?



Sub_Test
Dim My_Tex As String
Dim My_Sepa As String
Dim My_ID() As String

My_Tex = Cells(1, 1)
My_Sepa = Cells(2, 1)
My_ID() = Split(My_Tex, My_Sepa)

For i = LBound(My_ID()) To UBound(My_ID())

x= My_ID(i)
'then I get stuck !
Next i

End_Sub

snb
03-03-2015, 10:58 AM
In a row:


Sub M_snb()
cells(1).resize(,3)=split("aa|bb|cc","|")
End Sub

In a column

Sub M_snb()
cells(1).resize(3)=application.transpose(split("aa|bb|cc","|"))
End Sub

The separator in cells(2,1); result in a column

Sub M_snb()
cells(1).resize(3)=application.transpose(split(cells(1),cells(2,1)))
End Sub


More on split, arrays and writing into worksheets ( malheureusement je n'ai pas traduit la page en français)

http://www.snb-vba.eu/VBA_Arrays_en.html

Paul_Hossler
03-03-2015, 11:42 AM
little more wordy



Option Explicit
Sub Test()
Dim i As Long
Dim x As String

Dim My_Tex As String
Dim My_Sepa As String
Dim My_ID() As String

'testing
Cells(1, 1).Value = "aaaa;bbbb;cccc;dddd;eeee;"
Cells(2, 1).Value = ";"

My_Tex = Cells(1, 1)
My_Sepa = Cells(2, 1)

My_ID() = Split(My_Tex, My_Sepa)

'typically 0 to n-1 and since rows start at 1 we add one to it
For i = LBound(My_ID()) To UBound(My_ID())
Cells(i + 1, 3).Value = My_ID(i)
Next I

End Sub

JKwan
03-03-2015, 11:47 AM
try this


Sub Test()
Dim My_Tex As String
Dim My_Sepa As String
Dim My_ID() As String

My_Tex = Cells(1, 1)
My_Sepa = Cells(2, 1)
My_ID() = Split(My_Tex, My_Sepa)

For i = LBound(My_ID) To UBound(My_ID)

x = My_ID(i)
Cells(i + 1, "C") = My_ID(i)
Next i
End Sub

ValerieT
03-04-2015, 01:52 AM
Paul/ JKwan : thanks it works and matchs what I started

snb: thanks also because I learn a lot, and I've got new way to play ... :-)
by the way, congratulation for your french. If yousometimes need help for translating stuff, I'll be pleased as long as I am not in a crazy rush work period