carraige return within a cell & capitalization
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
|