Text length and TextBoxes
 

Thanks in advance for all your help...
I have a userform that has a textbox that is set to display multiple lines
of input via a cut and paste operation. Once I have pasted the text into the
textbox, I'd like for it to be broken up into an array of text with lengths
less than or equal to 250. Can you guys point me in the right direction?

Text length and TextBoxes
 

Ron

I'm not sure if I totally understand, but let's start with this

Dim i As Long, j As Long
Dim aText() As String
Dim lLen As Long

lLen = Len(Me.TextBox1.Text)

ReDim aText(1 To (lLen \ 250) + 1)

For i = 1 To lLen Step 250
j = j + 1
aText(j) = Mid(Me.TextBox1.Text, i, 250)
Next i

Now aText will be an array of strings not greater than 250 characters each.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Ron_D wrote:
Thanks in advance for all your help...
I have a userform that has a textbox that is set to display multiple
lines of input via a cut and paste operation. Once I have pasted the
text into the textbox, I'd like for it to be broken up into an array
of text with lengths less than or equal to 250. Can you guys point me
in the right direction?

Text length and TextBoxes
 

Hi Dick,

That's a start. I'm trying to breakup the very long text into shorter
strings and storing these shorter strings on cells in a spreadsheet using a
loop method to write the strings to these cells or stepping through an array.

My goal is to catalog merge these cells into Publisher, but the merge
command truncates any string that is greater than 255 characters. So each
cells would have a portion of the original string. For example, the original
text is 1270 characters long. There would need to be 6 cells. The first
cell would have the 1st 250 characters, the 2nd cell would have characters
251 to 500, etc etc.

I hope this clears up what I need. Thanks again,
Ron

"Dick Kusleika" wrote:

Ron

I'm not sure if I totally understand, but let's start with this

Dim i As Long, j As Long
Dim aText() As String
Dim lLen As Long

lLen = Len(Me.TextBox1.Text)

ReDim aText(1 To (lLen \ 250) + 1)

For i = 1 To lLen Step 250
j = j + 1
aText(j) = Mid(Me.TextBox1.Text, i, 250)
Next i

Now aText will be an array of strings not greater than 250 characters each.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Ron_D wrote:
Thanks in advance for all your help...
I have a userform that has a textbox that is set to display multiple
lines of input via a cut and paste operation. Once I have pasted the
text into the textbox, I'd like for it to be broken up into an array
of text with lengths less than or equal to 250. Can you guys point me
in the right direction?

Text length and TextBoxes
 

Ron

Dim i As Long, j As Long

For i = 1 To Len(Me.TextBox1.Text) Step 250
j = j + 1
Sheet1.Cells(j, 1).Value = Mid(Me.TextBox1.Text, i, 250)
Next i


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com



Ron_D wrote:
Hi Dick,

That's a start. I'm trying to breakup the very long text into shorter
strings and storing these shorter strings on cells in a spreadsheet
using a loop method to write the strings to these cells or stepping
through an array.

My goal is to catalog merge these cells into Publisher, but the merge
command truncates any string that is greater than 255 characters. So
each cells would have a portion of the original string. For example,
the original text is 1270 characters long. There would need to be 6
cells. The first cell would have the 1st 250 characters, the 2nd
cell would have characters 251 to 500, etc etc.

I hope this clears up what I need. Thanks again,
Ron

"Dick Kusleika" wrote:

Ron

I'm not sure if I totally understand, but let's start with this

Dim i As Long, j As Long
Dim aText() As String
Dim lLen As Long

lLen = Len(Me.TextBox1.Text)

ReDim aText(1 To (lLen \ 250) + 1)

For i = 1 To lLen Step 250
j = j + 1
aText(j) = Mid(Me.TextBox1.Text, i, 250)
Next i

Now aText will be an array of strings not greater than 250
characters each.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Ron_D wrote:
Thanks in advance for all your help...
I have a userform that has a textbox that is set to display multiple
lines of input via a cut and paste operation. Once I have pasted
the text into the textbox, I'd like for it to be broken up into an
array of text with lengths less than or equal to 250. Can you guys
point me in the right direction?

Text length and TextBoxes
 

Thanks Dick for all your help!!!! The code you gave me worked perfectly.

Ron

"Dick Kusleika" wrote:

Ron

Dim i As Long, j As Long

For i = 1 To Len(Me.TextBox1.Text) Step 250
j = j + 1
Sheet1.Cells(j, 1).Value = Mid(Me.TextBox1.Text, i, 250)
Next i


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com



Ron_D wrote:
Hi Dick,

That's a start. I'm trying to breakup the very long text into shorter
strings and storing these shorter strings on cells in a spreadsheet
using a loop method to write the strings to these cells or stepping
through an array.

My goal is to catalog merge these cells into Publisher, but the merge
command truncates any string that is greater than 255 characters. So
each cells would have a portion of the original string. For example,
the original text is 1270 characters long. There would need to be 6
cells. The first cell would have the 1st 250 characters, the 2nd
cell would have characters 251 to 500, etc etc.

I hope this clears up what I need. Thanks again,
Ron

"Dick Kusleika" wrote:

Ron

I'm not sure if I totally understand, but let's start with this

Dim i As Long, j As Long
Dim aText() As String
Dim lLen As Long

lLen = Len(Me.TextBox1.Text)

ReDim aText(1 To (lLen \ 250) + 1)

For i = 1 To lLen Step 250
j = j + 1
aText(j) = Mid(Me.TextBox1.Text, i, 250)
Next i

Now aText will be an array of strings not greater than 250
characters each.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Ron_D wrote:
Thanks in advance for all your help...
I have a userform that has a textbox that is set to display multiple
lines of input via a cut and paste operation. Once I have pasted
the text into the textbox, I'd like for it to be broken up into an
array of text with lengths less than or equal to 250. Can you guys
point me in the right direction?