excel vba : how to split characters in text file into cells?
 

Hi

I need some opinions. I got a text file loaded into a text box
and a combo box which has several integer values.
When a user choose a value from the combo box, e.g. 3,
I want to be able to group the characters from the text file
into 3 characters where each cell consists of one character.

The sample of the text file looks like this:
WUBEFIQLZURMVOFEHMYMWTIXCGTMPIFKRZUPMVOIRQMMWOZM

And when the user choose the number from the combo box,e.g. 3
I want the cell to contain:


W U B
E F I
Q L Z
U R M
V O F
and so forth

I have this line of codes but I wander if there's a better way of doin
it?

Private Sub KeyLengthComboBox_Change()
Worksheets("Sheet9").Activate
Dim myChar
keylength = CInt(KeyLengthComboBox)

For k = 1 To CipherTextBox.TextLength
myChar = Mid(CipherTextBox.Text, k, 1)
If k Mod keylength = 0 Then
Worksheets("sheet9").Cells(Int(k / keylength), keylength)
myChar
Else
Worksheets("sheet9").Cells(Int(k / keylength) + 1, k Mo
keylength) = myChar
End If
Next k

End Sub

Many thanks
did

--
Message posted from http://www.ExcelForum.com

excel vba : how to split characters in text file into cells?
 

thisrow = 0
mytext = textbox1.value 'eg WUBEFIQLZU...
mystep = combobox1.value 'eg 3

for i = 1 to len(mytext) step mystep

thisrow = thisrow + 1
cells(thisrow ,1) = mid(mytext,i,mystep)

mext

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"paku " wrote in message
...
Hi

I need some opinions. I got a text file loaded into a text box
and a combo box which has several integer values.
When a user choose a value from the combo box, e.g. 3,
I want to be able to group the characters from the text file
into 3 characters where each cell consists of one character.

The sample of the text file looks like this:
WUBEFIQLZURMVOFEHMYMWTIXCGTMPIFKRZUPMVOIRQMMWOZM

And when the user choose the number from the combo box,e.g. 3
I want the cell to contain:


W U B
E F I
Q L Z
U R M
V O F
and so forth

I have this line of codes but I wander if there's a better way of doing
it?

Private Sub KeyLengthComboBox_Change()
Worksheets("Sheet9").Activate
Dim myChar
keylength = CInt(KeyLengthComboBox)

For k = 1 To CipherTextBox.TextLength
myChar = Mid(CipherTextBox.Text, k, 1)
If k Mod keylength = 0 Then
Worksheets("sheet9").Cells(Int(k / keylength), keylength) =
myChar
Else
Worksheets("sheet9").Cells(Int(k / keylength) + 1, k Mod
keylength) = myChar
End If
Next k

End Sub

Many thanks
dido


---
Message posted from http://www.ExcelForum.com/

excel vba : how to split characters in text file into cells?
 

Thank you Patrick but the desired output is
to be like

one character per each cell instead of 3 characters per cell

for example

W in cell (1,1)
M in cell (1,2)
Y in cell (1,3)
W in cell (2,1)
M in cell 2,2)
Y in cell (2,3)
U in cell (3,1)
H in cell (3,2)
K in cell (3,3)

and so forth


any ideas?

thank

--
Message posted from http://www.ExcelForum.com

excel vba : how to split characters in text file into cells?
 

Does this minor modification of Patrick's code do it?

Option Explicit
Sub testme01()

Dim thisRow As Long
Dim thisCol As Long
Dim myText As String
Dim myStep As Long
Dim i As Long

thisRow = 0
'myText = textbox1.Value 'eg WUBEFIQLZU...
'myStep = combobox1.Value 'eg 3

myText = "abcdefghijklmnopqrstuvwxyz"
myStep = 5

For i = 1 To Len(myText)
If (i Mod myStep) = 1 Then
thisRow = thisRow + 1
thisCol = 1
Else
thisCol = thisCol + 1
End If
Cells(thisRow, thisCol) = Mid(myText, i, 1)
Next i
End Sub




"paku <" wrote:

Thank you Patrick but the desired output is
to be like

one character per each cell instead of 3 characters per cell

for example

W in cell (1,1)
M in cell (1,2)
Y in cell (1,3)
W in cell (2,1)
M in cell 2,2)
Y in cell (2,3)
U in cell (3,1)
H in cell (3,2)
K in cell (3,3)

and so forth

any ideas?

thanks

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

excel vba : how to split characters in text file into cells?
 

Hi

I need some opinions. I got a text file loaded into a text box
and a combo box which has several integer values.
When a user choose a value from the combo box, e.g. 3,
I want to be able to group the characters from the text file
into 3 characters where each cell consists of one character.

The sample of the text file looks like this:
WUBEFIQLZURMVOFEHMYMWTIXCGTMPIFKRZUPMVOIRQMMWOZM

And when the user choose the number from the combo box,e.g. 3
I want the cell to contain:


W U B
E F I
Q L Z
U R M
V O F
and so forth


An alternate method, without looping, but only works with "small" strings (<
252 characters)

Sub BreakIt()
Dim St As String
Dim HowMany As Long
Dim Nm1 As Name, Nm2 As Name

St = "WUBEFIQLZURMVOFEHMYMWTIXCGTMPIFKRZUPMVOIRQMMW OZM"
HowMany = 3

If Len(St) 252 Then
MsgBox "Can't do this one...", vbCritical
Exit Sub
End If

Set Nm1 = ThisWorkbook.Names.Add("_TempString", _
"=" & Chr$(34) & St & Chr$(34))
Set Nm2 = ThisWorkbook.Names.Add("_TempForm", _
"=MID(_TempString,ROW($A$1:$A$" & Len(St) & "),1)", True)

With Range("A1").Resize(Application.RoundUp(Len(St) \ HowMany, 0),
HowMany)
.Formula = "=INDEX(_TempForm," & HowMany & "*ROW(A1)-" & _
HowMany & "+COLUMN(A1))"
.Value = .Value
On Error Resume Next
.SpecialCells(xlCellTypeConstants, xlErrors).Value = ""
End With

Nm1.Delete
Nm2.Delete
End Sub


--
Regards,

Juan Pablo González

excel vba : how to split characters in text file into cells?
 

Just fill down and across as (no vba) needed

=MID(Sheet1!$A2,COLUMN(A1),1)


Regards Robert

"paku " wrote in message ...
Thank you Patrick but the desired output is
to be like

one character per each cell instead of 3 characters per cell

for example

W in cell (1,1)
M in cell (1,2)
Y in cell (1,3)
W in cell (2,1)
M in cell 2,2)
Y in cell (2,3)
U in cell (3,1)
H in cell (3,2)
K in cell (3,3)

and so forth


any ideas?

thanks


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004

excel vba : how to split characters in text file into cells?
 

Hi All,

Thanks, Dave. The modification works fine.
Thanks to all of you too for the opinions.

did

--
Message posted from http://www.ExcelForum.com