Excel 2007-Auto numbering rows-with in a cell

T

thenvk

Adept
I have 20 rows of text in a single cell. I want them to be numbered automatically.

Situation

Text1
Text2
...

Text 20

Need like this

1. Text1
2. Text2
.
.
.
20. Text20

this way.

Is there any way to do it?
I can do it easily in word, but have about 250 cells. So need some easy way.

thanks in advance.
 
Use the "Concatenate" formula in Excel if you want the numbering. I don't know of a direct function in Excel that does the same. Auto numbering is a MS Word specific feature I think.
 
Step 1:

Suppose A1 cell has 1

then go to A2 cell, and type =A1+1 (A2 will have automatically "2" after you have formula applied)

copy paste formula from A2 to its below cell so here you have automatic numbering till the formula's you have copied :D

Step2 - (connected to step 1) :

after uve done above, insert a column just ahead of A column so now the above A column will become B

Now, in inserted column in A1 type =Concatenate(B1,".",C1) [C1 is ur content which you want to give serial no. to] [where B1 is ur old A1 column] [you can add anything in the place of a dot which will add up after numerical 1 even space can be added]

you will get in cell A1 is "1." [a 1 and a dot which is required by you] exactly you want and then hide column B and you will also see column C where ur data ABC,XYZ etc will be as per ur above image :)

once Done.. hide column C which has ur contents and voila!! u get auto numbered contents..

If you want, i can PM you the Excel file which i just made. you just have to copy paste formula as per your need :)
 
^ concatenate is a much simpler and efficient way of doing the same thing that you mentioned my friend

@OP - You can always eliminate the first row of digits mentioned in nishangandhi's post by "hiding"/ "deleting" it after you have got the desired pattern.
 
^^ no. auto serial number is necessary.. so once you do step one, u need to insert a column before A column and hide the B column to enable auto numbering :)

and after putting Concatenat formula, he just have to input his data and serial no. plus dot will be all automatic :D

if he puts Test1, he will automatically get 1.Test1 :p thats the power of Excel and my step 1 :D
 
^^ listen, ive edited. everything is there in my post :)

IVE ATTACHED A FILE YOU JUST HAVE TO REMOVE .TXT AND ADD .XLSX AT THE END. ENJOY AND IM EXPECTING SOME REPS :p

if you want space after the dot, just edit the formula and add space after dot in concatenat formula :)
 

Attachments

  • Book1.TXT
    8.8 KB · Views: 184
G@d of w@r,

If there is only one row in the cell, I know what you said.

I have a case where a cell has many steps and are not correcly numbered.
Each step is separated by Alt+enter
I want to renumber them correctly.
 
If I understand your req correctly, this may work.

I am assuming cell A2 down is your input & B2 down is your output.
Col C onwards will be intermediate values; I am giving a soln for max 15 lines per cell but I think the limit will be like 125-126 (max cols in excel / 2) only matter of copying the formula across

Steps
1) Set cell C1 & C2 to 0, cell D2 to empty

2) Put these formulae:
E1 = C1 + 1
E2 = IF(C2 < LEN($A2), IF(ISERR(FIND(CHAR(10), $A2, C2+1)), LEN($A2), FIND(CHAR(10), $A2, C2+1)), 99999)
F2 = IF(E2 = 99999, D2, D2 & E$1 & "." & MID($A2, C2+1, E2 - C2)) << Modify this as per your format req

3) Select E1:F2 and drag (copy) right across 1:2 rows. Copy upto AH col. AG1 should be 15 now (the 15 limit I was talking about). If you need more continue the copy

4) Set the formula:
B2 = AH2 (or until you copied, the cell with blank in row 1)
For B2 set Format > Cells > Allignment > check Wrap text

5) Put your text in A2 & verify result in B2:

My test case
A2
TESTtest
test2
test3
test4

B2
1.TESTtest
2.test2
3.test3
4.test4
6) If OK, copy row 2:2 down & keep putting your values in Col A
You can very easily do this with Macro also, it will be cleaner & easier to code.

bchat
 

Attachments

  • Book1.zip
    9.3 KB · Views: 179
Back
Top