Excel question

Discussion in 'Mac Basics and Help' started by quigleybc, Aug 22, 2006.

  1. quigleybc macrumors 68030

    quigleybc

    Joined:
    Jun 17, 2005
    Location:
    Beautiful Vancouver British Columbia, Canada
    #1
    Hi,

    I have a giant column in Excel.

    Each cell in the column contains three words of text.

    Example:

    Soft Shell Jacket.

    I need to simply add the word F06 to the end of every cell of text to make it read:

    Soft Shell Jacket F06

    Is there a way to add that text to the entire column all at once? Instead of entering it one by one?

    The three words will change after every tenth row or so, so I can't just copy one and paste to the whole column..and if I go, Edit>replace> 'space' with 'F06'

    I get SoftF06ShellF06Jacket

    and that won't work.....

    Any suggestions?

    thanks in advance

    :)
     
  2. Kernow macrumors 65816

    Kernow

    Joined:
    Sep 30, 2005
    Location:
    Kingston-Upon-Thames
    #2
    It may not be the most efficient, but I would use the concatenate function.

    If you insert two blank columns next to the existing column with text, then copy F06 all the way down the first blank column. Insert the formula =CONCATENATE(first cell, second cell) in the second blank column and copy all the way down, it should add F06 to each cell. You can then use copy, paste special, values to get rid of the formulas.

    Like I said, probably not the most efficient, but it'll do the job fairly quickly.
     
  3. quigleybc thread starter macrumors 68030

    quigleybc

    Joined:
    Jun 17, 2005
    Location:
    Beautiful Vancouver British Columbia, Canada
  4. bearbo macrumors 68000

    bearbo

    Joined:
    Jul 20, 2006
    #4
    say the column that has "Soft Shell Jacket" is column A, start with cell A1

    then in B1 (or wherever), type =A1&" F06", then drag down...

    oh, do you have the original as "Soft Shell Jacket" or "Soft Shell Jacket." (the period)?
     
  5. WildCowboy Administrator/Editor

    WildCowboy

    Staff Member

    Joined:
    Jan 20, 2005
    #5
    That's neat...I'm not a power-Excel user, so I haven't discovered tricks like this yet. One question though...this generates a second column with the desired content it. But deleting the original column of course destroys the new content because it's just a formula. This can be remedied by using "Paste Special" to copy and paste just the content back into the original column, allowing you to delete the "formula" column. It's not difficult, but is there an even easier way to do that?
     
  6. clayj macrumors 604

    clayj

    Joined:
    Jan 14, 2005
    Location:
    visiting from downstream
    #6
    There's not really an *easier* way to do it. The suggestion of using '=A1&" F06"', filling it down, and then copying it/paste specialing it as values is the quickest solution. You could also write a macro to go down through the column and append " F06" to every cell, but that would take longer to do.
     
  7. WildCowboy Administrator/Editor

    WildCowboy

    Staff Member

    Joined:
    Jan 20, 2005
    #7
    Right, I thought of a macro, but I agree that this is simpler...thanks.

    I guess the only way that would be simpler is if you could easily convert the formula entries directly to content within their own column and then delete the original column, saving you the step of "paste specialing."
     
  8. quigleybc thread starter macrumors 68030

    quigleybc

    Joined:
    Jun 17, 2005
    Location:
    Beautiful Vancouver British Columbia, Canada
    #8

    no period...just soft shell jacket

    thanks a ton for all your reply's guys !

    That's why this site is so great

    BIG THANKS!

    :D
     
  9. clayj macrumors 604

    clayj

    Joined:
    Jan 14, 2005
    Location:
    visiting from downstream
    #9
    Well, if you found yourself doing this sort of thing quite frequently, then you'd want to write a macro (callable from a menu item... think "Tools > Append") that would prompt you for a value to be appended to all currently selected cells. Then you could just select the range, hit Tools > Append, type " F06", and click OK.
     
  10. r6girl Administrator/Editor

    r6girl

    Staff Member

    Joined:
    Sep 6, 2003
    Location:
    Massachusetts
    #10
    sounds like you already have a solution, but you could also do a find & replace: Find "Soft Shell Jacket" and Replace with "Soft Shell Jacket F06". approach it as replacing the whole thing vs. adding something to the end...
     
  11. WildCowboy Administrator/Editor

    WildCowboy

    Staff Member

    Joined:
    Jan 20, 2005
    #11
    Yeah, that was the issue he was talking about...not all of the entries he needs changed are "Soft Shell Jacket."
     
  12. clayj macrumors 604

    clayj

    Joined:
    Jan 14, 2005
    Location:
    visiting from downstream
    #12
    He said that the column was filled with all sorts of different values, so a Find and Replace would not work.
     
  13. r6girl Administrator/Editor

    r6girl

    Staff Member

    Joined:
    Sep 6, 2003
    Location:
    Massachusetts
    #13
    ah, i misread. thought he was just looking for specific entries in the column, not the whole column...
     
  14. quigleybc thread starter macrumors 68030

    quigleybc

    Joined:
    Jun 17, 2005
    Location:
    Beautiful Vancouver British Columbia, Canada
    #14
    When I go to "paste special"

    Should I choose "all"?

    or "formula"

    ?

    it gives me a few options,

    and should I paste it into the original column? or can it be pasted into the column with the formula, or even a new blank column?

    thanks!
     
  15. clayj macrumors 604

    clayj

    Joined:
    Jan 14, 2005
    Location:
    visiting from downstream
    #15
    Paste VALUES. That will eliminate the formula while leaving the result of the formula in place.

    And you can paste it in either place, so you might as well paste it on top of the original values. Just make sure the result of the formula is correct before you do this.
     
  16. quigleybc thread starter macrumors 68030

    quigleybc

    Joined:
    Jun 17, 2005
    Location:
    Beautiful Vancouver British Columbia, Canada
    #16

    You rock, Values worked perfectly!!

    Thank you guys soo much! saves me a ton O' work today! woo hoo...!!

    more time for MR

    and clay....what happened to Grisham?

    :D :D :D
     

Share This Page