Numers in front of existing text (Office 7)

Topic

New Reply

I have a column of text that I would like to quickly insert numbering in front of the text already in the cell. If I try to copy and paste into cells that have the numbering I erase the numbering or if I paste the numbers I erase the text. Thanks!

Reply | Quote

Replies

What are the number based upon? Is it a single number for each row of data, or are the numbers variable.

Method for adding the row number to the beginning of the cells

In a dummy column enter the formula =row() & A1
copy the formula down as far as necessary.
Highlight the cells with the formula – right click and choose Copy
Select the topmost cell that you want to add the numbers
Right click and choose “Paste Special…”
Choose Values
Ok out
Delete your dummy column

Reply | Quote

Mike,
A single number for the text in the cell starting with 1, then 2, etc.. This worked as long as I erased the heading and moved my column of cells starting at A3 up to A1. My next question is how do I get a space between the number and the text such as 1. text, 2. text ?
Ken

Reply | Quote

You don’t have to delete the two rows.

To incorporate both criteria, you could use the following:

=row()-2 & ” ” & A3

Reply | Quote

Mike,
Thanks, that worked great! Now, how do I get a space between the number and the text?
Ken

Reply | Quote

Make sure you have the space between the “s

” ” not “”

This version would put a space, a dash, and a space between the number and the text.
=row()-2 & ” – ” & A3

Reply | Quote

Ah crum – after a closer look at your example….

Use:
=row() & “. ” & A3

This will format as:
1. Text
2. Other text.

Reply | Quote

Thanks for all the help Mike!
The one that worked was =row()-2 & “. ” & A3
Ken

Reply | Quote

One last question. This is the first time I’ve used a formula. I operate Vista Home Premium. Is there somewhere I can save this formula?
Thanks!
Ken

Reply | Quote

I have a text file on my desktop for this purpose. The file contains “indispensable” (at least to me) formulas and chunks of code. You can save the file anywhere on your computer. Your formula is in the attached text file.

Reply | Quote

You can use a formula in another column to concatenate the values, e.g. =A1&” “&B1
If you wish, you can then select the column with the formulas, copy, then paste special with the values option to get rid of the formulas, and finally delete the original columns.

Reply | Quote

Hans,
Never using formulas in Excel before (I’ve just used it for text), I’m afraid I don’t understand.
Ken

Reply | Quote

Try formatting the column with the custom format “123”@
(quotes included)

Reply | Quote

I tried this and it placed 123 in front of every cell.
Ken

Reply | Quote

Sorry, I didn’t “see” the part where you indicate that the numbers are in another cell.

using the same method, change the formula to
= D1 & C1 (where D1 is the cell with the number and C1 is the cell that you want to add the numerical prefix)

Reply | Quote

you didn’t mentioned how you want your numbers to appear in front of the text, try =A2&B2 or =A2&” “&B2
place the formula into an empty cell and copy down if need, the latter wii return number space text
copy the result and then Paste Special follow by Value
after this, you can delete the cells with formula

Reply | Quote