Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Cosby, Tennessee, USA
    Posts
    126
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Numers in front of existing text (Office 7)

    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!

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numers in front of existing text (Office 7)

    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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Numers in front of existing text (Office 7)

    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.

  4. #4
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numers in front of existing text (Office 7)

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

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numers in front of existing text (Office 7)

    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)

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numers in front of existing text (Office 7)

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Cosby, Tennessee, USA
    Posts
    126
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Numers in front of existing text (Office 7)

    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

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Cosby, Tennessee, USA
    Posts
    126
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Numers in front of existing text (Office 7)

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

  9. #9
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numers in front of existing text (Office 7)

    You don't have to delete the two rows.

    To incorporate both criteria, you could use the following:

    =row()-2 & " " & A3

  10. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Cosby, Tennessee, USA
    Posts
    126
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Numers in front of existing text (Office 7)

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

  11. #11
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Cosby, Tennessee, USA
    Posts
    126
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Numers in front of existing text (Office 7)

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

  12. #12
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numers in front of existing text (Office 7)

    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

  13. #13
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numers in front of existing text (Office 7)

    Ah crum - after a closer look at your example....

    Use:
    =row() & ". " & A3

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

  14. #14
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Cosby, Tennessee, USA
    Posts
    126
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Numers in front of existing text (Office 7)

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

  15. #15
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Cosby, Tennessee, USA
    Posts
    126
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Numers in front of existing text (Office 7)

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •