SPLIT TEXT BY MACROS

[teodormircea]

Hello Forum

I have this code that split a text, and put a value in a text box,
Ex:

ABS1-EN1-FINLAND1 Split in ABS1 or EN1 or FINLAND1
I want to change it such in order to chose the column that contains the text to split, and in the column to put my result
Here is the code with the userform,

[Kenneth Hobs]

Not sure if you are wanting to show something in a userform or just process the data. If the latter, then create two refedit controls and put this in a commandbutton. I just returned the 2nd element of the split array.
Private Sub CommandButton1_Click()
Dim sCol As Range, rCol As Range
Dim a() As String, c As Range
On Error Resume Next
Set sCol = Range(RefEdit1)
Set rCol = Range(RefEdit2)
If sCol Is Nothing Then Set sCol = Range("A1")
If rCol Is Nothing Then Set rCol = Range("B1")
For Each c In Range(Cells(2, sCol.Column), Cells(Rows.Count, sCol.Column).End(xlUp))
a() = Split(c, "-")
If UBound(a) > 0 Then
Cells(c.Row, rCol.Column).Value = a(1)
End If
Next c
Unload Me
End Sub

[teodormircea]

Thanks for your help, can i ask you to add also the choice of element to split
like in my first attempt
Thank you

[Kenneth Hobs]

You could use what you did by getting the option button index.

In this example, I used a label and a spinbutton control.
Private Sub UserForm_Initialize()
Label1.Caption = 2
End Sub

Private Sub CommandButton1_Click()
Dim sCol As Range, rCol As Range
Dim a() As String, c As Range
On Error Resume Next
Set sCol = Range(RefEdit1)
Set rCol = Range(RefEdit2)
If sCol Is Nothing Then Set sCol = Range("A1")
If rCol Is Nothing Then Set rCol = Range("B1")
For Each c In Range(Cells(2, sCol.Column), Cells(Rows.Count, sCol.Column).End(xlUp))
a() = Split(c, "-")
If UBound(a) > 0 Then
Cells(c.Row, rCol.Column).Value = a(Label1.Caption - 1)
End If
Next c
Unload Me
End Sub

Private Sub SpinButton1_SpinDown()
With Label1
If .Caption = 1 Then Exit Sub
.Caption = .Caption - 1
End With
End Sub

Private Sub SpinButton1_SpinUp()
With Label1
If .Caption = 3 Then Exit Sub
.Caption = .Caption + 1
End With
End Sub

[teodormircea]

Hello,
Thanks for your help
i just tested the macros but is not displaying the results in the range that i chose with the ref edit, the result is going always in the second column, or i want it to go in the range i chose

[Kenneth Hobs]

I designed it to do use Column B for the results if RefEdit2 was empty.

You can post the xls if you like.

[Kenneth Hobs]

Here is your example using my userform2 and code.

[teodormircea]

Thanks!

[oleneazer]

Hi all,

teodormircea
Troubles with your memory, perhaps ? :think:

http://www.excel-downloads.com/forum/694732-post11.html

or dont like VBAcrumble 's pie