Numbered Items in a Cell

M

Manik

I am using Excel 97. Is there a way to have a numbered
items inside a single cell.

Thanks
Manik
 
C

CLR

Please be more specific as to what you would like to do.......

Vaya con Dios,
Chuck, CABGx3
 
M

Manik

Lets say I want the following contents to be in a cell
(big one):

1. First Line
2. Second Line
3. Third Line

I want all the above numbered items to appear in a single
cell.

Thanks
Manik
 
G

Gord Dibben

Manik

Type in the following..........

1. First line then hit ALT + ENTER(which forces a carriage return in-cell.
2. Second line then hit ALT + ENTER
3. Third line then hit ENTER

Set the cell for Wrap Text and Autofit the row.

Gord Dibben Excel MVP - XL97 SR2 & XL2002
 
C

CLR

If the cell is large enough, you can insert Text-Boxes and do your typing in
those.......they can also be separately hyperlinked if you desire.......

Vaya con Dios,
Chuck, CABGx3
 
M

Manik

Can I also make it auto numbered like, we the "numbering"
feature in MS-Word.

Thanks
Manik

-----Original Message-----
Manik

Type in the following..........

1. First line then hit ALT + ENTER(which forces a carriage return in-cell.
2. Second line then hit ALT + ENTER
3. Third line then hit ENTER

Set the cell for Wrap Text and Autofit the row.

Gord Dibben Excel MVP - XL97 SR2 & XL2002
Click to expand...
 
L

Leo Heuser

Hi Manik

Here's a VBA solution for an automatic numbering.

1. From the sheet rightclick the sheet-tab and choose "View code"
2. Copy the below code and paste it to the righthand window.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim CheckRange As Range

On Error Goto Finito

Set CheckRange = Range("A1:C12, D16, F16:H16")

If Union(Target, CheckRange).Address = Union(CheckRange,
CheckRange).Address Then
Application.EnableEvents = False

For Each Cell In Target
Call Numbering(Cell)
Next Cell
End If
Finito:
Application.EnableEvents = True
End Sub

3. Enter the cells in question in CheckRange instead of
Range("A1:C12, D16, F16:H16")


4. Choose from the menu Insert > Module
5. Copy the below code and paste it to the righthand window.

Sub Numbering(CheckCell As Range)
'Leo Heuser, 18-7-2003
Dim CheckCellValue As String
Dim Counter As Long
Dim FindChr10 As Long
Dim MaxNumberOfDigits As Long

CheckCellValue = CheckCell.Value

FindChr10 = 1
Counter = 1
MaxNumberOfDigits = 3 ' Numbering from 1 to 999. Used
'to pad the numbers with blanks to make them (almost with
'a proportional font :) line up under each other.

Do While InStr(FindChr10, CheckCellValue, Chr(10))

FindChr10 = InStr(FindChr10, CheckCellValue, Chr(10))
Counter = Counter + 1

CheckCellValue = Left(CheckCellValue, FindChr10) & _
String(MaxNumberOfDigits - Len(Mid(Str(Counter), 2)), " ") & _
Counter & ". " & Mid(CheckCellValue, FindChr10 + 1)
FindChr10 = FindChr10 + 1
Loop

CheckCellValue = String(MaxNumberOfDigits - 1, " ") & _
"1. " & CheckCellValue

CheckCell.Value = CheckCellValue

End Sub

6. Go to the sheet with <Alt><F11> and save the workbook.



When you enter your lists, with <Alt><Return> as described
by Gord, in the cells of
Set CheckRange = Range("A1:C12, D16, F16:H16")
they will be automatically numbered.

Another option is to insert the below code in a module,
and call it from the sheet with a button.

Sub Numbering2()
'Leo Heuser, 18-7-2003
Dim CheckCell As Range
Dim CheckCellValue As String
Dim Counter As Long
Dim FindChr10 As Long
Dim MaxNumberOfDigits As Long

Set CheckCell = ActiveCell
CheckCellValue = CheckCell.Value

FindChr10 = 1
Counter = 1
MaxNumberOfDigits = 3 ' Numbering from 1 to 999. Used
'to pad the numbers with blanks to make them (almost with
'a proportional font :) line up under each other.

Do While InStr(FindChr10, CheckCellValue, Chr(10))

FindChr10 = InStr(FindChr10, CheckCellValue, Chr(10))
Counter = Counter + 1

CheckCellValue = Left(CheckCellValue, FindChr10) & _
String(MaxNumberOfDigits - Len(Mid(Str(Counter), 2)), " ") & _
Counter & ". " & Mid(CheckCellValue, FindChr10 + 1)
FindChr10 = FindChr10 + 1
Loop

CheckCellValue = String(MaxNumberOfDigits - 1, " ") & _
"1. " & CheckCellValue

CheckCell.Value = CheckCellValue

End Sub


1. Enter the list in a random cell
as described above.
2. Select the cell
3. Push the button
4. The list is numbered.


--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only, please.
 
S

sharon

I have a question about auto-numbering. I have a list in ColB and in
ColA I have auto-numbering. ColB has blank rows and I need ColA to
skip those blank rows and continue numbering. Is there a formula that
can do that?
Any help would be appreciated.

Thanks
Sharon
 
K

Kevin Stecyk

Hi Sharon,

Your answer is here at John Walkenbach's site.

http://j-walk.com/ss/excel/usertips/tip035.htm

Hope that helps.

Regards,
Kevin



sharon said:
I have a question about auto-numbering. I have a list in ColB and in
ColA I have auto-numbering. ColB has blank rows and I need ColA to
skip those blank rows and continue numbering. Is there a formula that
can do that?
Any help would be appreciated.

Thanks
Sharon
Click to expand...
creating financial statements
 
Top