Copy down and copy and Edit>Paste Special Values
I am an Excel Novice. I don't know very much at all about how it works, what formulas are or any of its intracacies. My job requires me to take information from an Excel file that is emailed to me.
I need to know in the simplest terms how to take the first column (which is last names) and add a comma to the end of each name (one in each cell) all the way down that column, before I copy and paste them, so they will read "last name comma space first name".
If someone can answer this for me and I can make it actually work, it will give me a wonderful glow in my heart towards mankind.
That's very bounteous of you.
If you have, say, "Bloggs" in cell A1, and type =A1&"," in C1 (or any blank column) it will result in "Bloggs," and you can copy this formula down as far as necessary. If you want to overwrite the original column A entries, you can select the column C entries, copy them and paste special - values over column A.
I think I understand... when I get back to work I will try these suggestions.
Nice use of Stewie, Mr Administrator... He's my favorite little tyrant!
(I would add a smilie, but they're not showing up)
Auto Merged Post Until 24 Hrs Passes;
When I copy the =A1&"," and paste it into subsequent cells in the new column, I get the first name over and over in the new column. I need to take a column that has, say, these names:
etc, and add a comma to each name, resulting in a column that looks like:
Is there a fast way to do this? I have 366 names to put a comma behind.
Right now I have the startings of a column that looks like
I can see that I can get what I want if I change the number in the cell to correspond with the row that I'm on but it seems like it would be easier to just type 366 commas.
What am I not getting? What I don't know about Excel would fill a large book... a book about Excel
Last edited by anncognito; April 2nd, 2008 at 12:49. Reason: Auto Merged Doublepost
In cell A1 you have your first name (in your case it's Abadie)
Type in cell B1 =A1&","
This will result in Abadie,
Then copy cell B1 and paste it all the way down to Bx (where x is the last cells corresponding to entries in column A
Then copy all of column B, select edit>paste special and select 'values'...click OK
When I try to copy B1 and paste it into B2, the box around B2 gets blue and has a dashed border.
If I can get it to copy, do I go down to the end (Bx) and hold the shift key down and click to fill all the cells? Do I have to copy the cells one at a time?
I have so little understanding of how Excel works == this is the only job I am tasked to do that involves it at all, and I really appreciate everyone's suggestions. I usually use Quark and Creative Suite, this program makes me feel so stupid!
Fear not. With the formula in B1, there are two ways.
The quicker way is to move the mouse to the bottom right of B1 until the cursor changes to a black cross and then double-click. That should automatically copy the formula as far down as there are entries in column A.
Or, once you've got the black cross, keep the mouse button pressed and drag down as far as necessary.
Remember, if anything goes wrong there's always Undo (Edit > Undo or just Ctrl+Z).
ADDED BY ADMIN
Last edited by Dave Hawley; April 3rd, 2008 at 09:51.
Oh.My.God. It works just like you said!!!
Thank You, thank you, THANK YOU!!!!
I've been unwell and then the weekend came, and this is my first chance to try your suggestion and get back to you about it.
I've been pulling the copy into Microsoft Word and running a macro and then playing with "convert text to table" and "convert table to text" and this method worked according t the whims of the computer, or sunspot activity, or ????
This looks like a much more reliable and time-saving (not to mention neck-muscle-saving) method.
StephenR, there is a wonderful glow in my heart for you. I'll do what I can about the rest of mankind. :-)
Assume you have the last name in column A and the first name in column B and the names start in row 1 then enter this formula in cell C1 and copy down to the end of the names using the methods you have learned so far
=A1 & “, “ & B1
Notice there is a space after the comma,
This will result in column C having the last name comma space first name
Tip: To avoid chasing code always use Option Explicit.
There are currently 2 users browsing this thread. (0 members and 2 guests)