In cells A1:A30 I've got my original values, which are text sentences, and
anywhere between 40 and 70 characters long. Cells B1:B30 I've put a varation
of Sub Shuffle30Values() found elsewhere on these boards to create a random
order of lookup values. Cells C1:C5 list punctuation/conjunctive words; cell
D1 concatenates and merges the first five values of B and C to form a
"proper" randomly generated sentence with punctuation (at the moment I've got
the output set to LOWER).

The first thing I need to do is get the output of this cell to be in
"proper" English, so that the first letter, and any letter appearing after a
full stop, are capitalized.

The second thing I need to try and do is insert a carraige return within the
cell after 45-50 characters, but so that it isn't inserted in the middle of a
word.

Current output:

line 1 has fifty characters, whilst line 2 has fifty characters and line 3
has fifty characters. line 4 has fifty characters but line five has fifty
characters.

Desired output that can be copied over to word:

Line 1 has fifty characters, whilst
line 2 has fifty characters and
line 3 has fifty characters.
Line 4 has fifty characters but
line 5 has fifty characters.

Any suggestions on how to modify the output?

Olvarski,

It would be immensely helpful if you posted your code/formulas.

HTH,
Bernie
MS Excel MVP

"Olvarski" wrote in message
...
In cells A1:A30 I've got my original values, which are text sentences, and
anywhere between 40 and 70 characters long. Cells B1:B30 I've put a
varation
of Sub Shuffle30Values() found elsewhere on these boards to create a
random
order of lookup values. Cells C1:C5 list punctuation/conjunctive words;
cell
D1 concatenates and merges the first five values of B and C to form a
"proper" randomly generated sentence with punctuation (at the moment I've
got
the output set to LOWER).

The first thing I need to do is get the output of this cell to be in
"proper" English, so that the first letter, and any letter appearing after
a
full stop, are capitalized.

The second thing I need to try and do is insert a carraige return within
the
cell after 45-50 characters, but so that it isn't inserted in the middle
of a
word.

Current output:

line 1 has fifty characters, whilst line 2 has fifty characters and line 3
has fifty characters. line 4 has fifty characters but line five has fifty
characters.

Desired output that can be copied over to word:

Line 1 has fifty characters, whilst
line 2 has fifty characters and
line 3 has fifty characters.
Line 4 has fifty characters but
line 5 has fifty characters.

Any suggestions on how to modify the output?

Column A:
(plain text input - e.g. [line 1 is fifty characters long])

Column B:
is the punctuation and conjuctive words (e.g.[, and ] [ whilst ] etc.)

Column C:
=INDIRECT("A"&$F$5)

Column D:
=CONCATENATE(C1,B1,C2,B2,C3,B3,C4,B4,C5,B5)

Column E:
=LOWER(D1)

Bits of Columns F & G now hold the variation of Jim Cone's number shuffling
macro:

Sub Shuffle30Values()
'Jim Cone - June 22, 2004
'Lists five random numbers between 1 and 15 in a column (no dupes)
'Lists five random numbers between 16 and 30 in the adjoining column (no
dupes)

Dim lngTemp As Long
Dim lngNum As Long
Dim lngRow As Long
Dim lngCol As Long
Dim Arr() As Long
Dim i As Long
Dim j As Long

'For numbers from 1 to 50
lngNum = 50
'First row in which the numbers will be displayed
lngRow = 5

'Columns D and Columns C
For lngCol = 10 To 6 Step -1
ReDim Arr((lngNum - 9) To lngNum, 1 To 1)
'Add values to the array
For i = (lngNum - 9) To lngNum
Arr(i, 1) = i
Next 'i
'Move each array value to a new random position.
For i = lngNum To (lngNum - 9) Step -1
Randomize (Right(Timer, 2) * i)
'Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
j = Int(10 * Rnd + (lngNum - 9))
lngTemp = Arr(i, 1)
Arr(i, 1) = Arr(j, 1)
Arr(j, 1) = lngTemp
Next 'i

'Add first five numbers from the array to the worksheet.
Range(Cells(lngRow, lngCol), Cells(lngRow + 9, lngCol)).Value = Arr()
lngNum = lngNum - 10
Next 'lngCol

End Sub

Olvarski,

Enter your Plain text in column A with leading capital letters and all else
lower.

Then in D, use the formula

=CONCATENATE((C1),B1,CHAR(10),IF(B1=".",C2,LOWER(C 2)),B2,CHAR(10),IF(B2=".",
C3,LOWER(C3)),B3,CHAR(10),IF(B3=".",C4,LOWER(C4)), B4,CHAR(10),IF(B4=".",C5,L
OWER(C5)),B5)

This assumes that the punctuatiopn is simply a period, not a period followed
by a space.

Format the cell in column D for word wrap.

HTH,
Bernie
MS Excel MVP

"Olvarski" wrote in message
...
Column A:
(plain text input - e.g. [line 1 is fifty characters long])

Column B:
is the punctuation and conjuctive words (e.g.[, and ] [ whilst ] etc.)

Column C:
=INDIRECT("A"&$F$5)

Column D:
=CONCATENATE(C1,B1,C2,B2,C3,B3,C4,B4,C5,B5)

Column E:
=LOWER(D1)

Bits of Columns F & G now hold the variation of Jim Cone's number
shuffling
macro:

Sub Shuffle30Values()
'Jim Cone - June 22, 2004
'Lists five random numbers between 1 and 15 in a column (no dupes)
'Lists five random numbers between 16 and 30 in the adjoining column (no
dupes)

Dim lngTemp As Long
Dim lngNum As Long
Dim lngRow As Long
Dim lngCol As Long
Dim Arr() As Long
Dim i As Long
Dim j As Long

'For numbers from 1 to 50
lngNum = 50
'First row in which the numbers will be displayed
lngRow = 5

'Columns D and Columns C
For lngCol = 10 To 6 Step -1
ReDim Arr((lngNum - 9) To lngNum, 1 To 1)
'Add values to the array
For i = (lngNum - 9) To lngNum
Arr(i, 1) = i
Next 'i
'Move each array value to a new random position.
For i = lngNum To (lngNum - 9) Step -1
Randomize (Right(Timer, 2) * i)
'Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
j = Int(10 * Rnd + (lngNum - 9))
lngTemp = Arr(i, 1)
Arr(i, 1) = Arr(j, 1)
Arr(j, 1) = lngTemp
Next 'i

'Add first five numbers from the array to the worksheet.
Range(Cells(lngRow, lngCol), Cells(lngRow + 9, lngCol)).Value =
Arr()
lngNum = lngNum - 10
Next 'lngCol

End Sub