carraige return within a cell & capitalization

[Guest]

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?

 

[Bernie Deitrick]

Olvarski,

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

HTH,
Bernie
MS Excel MVP

 

[Guest]

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

 

[Bernie Deitrick]

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(C2)),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