Alright, now we're getting into the good stuff: formulas!
In Word©, combining two pieces of information is simple: you cut/copy and paste them together. (It's even simpler with the aforementioned shortcuts, just sayin'). However, in Excel©, it is not that simple. Yes, you can just copy and paste, but simple copying and pasting only works if you're copying whole cell and replacing another whole cell with it. In order to actually combine two cells into one, you have to edit the first cell, select the text, copy it, edit the second cell, paste it, etc. If you enjoy menial and boring work, you can do it this way. If you're like me, you don't like double clicking and scrolling and, and stuff! Well, concatenate is the answer to your troubles!
Concatenate is a formula that allows you to combine the text from two cells into a whole new cell, right next to each other. Example: if you had a list of first and last names, but you wanted full names and the list was upwards of 200 people long, this formula will have them combined into full names before you can say "supercalifragilisticexpialidocious" (I spelled that right on the first try! Woot! Sorry, back to the lesson...) Anyway, before you can say that really long word (and possibly you might have to sing the song), you'll be able to write a concatenate formula for every single entry in your 200+ list. Maybe not on your first try, but after awhile, you'll be able to do it three seconds. (If you can say "supercalifragilisticexpialidocious" in under three seconds...get a hobby).
Interesting tidbit: concatenate comes from the Latin word concatēnāre which means "to link together". And this is what you're telling Excel© to do.
Anyway, on to the formula! You ready? Too bad.
This is what the formula looks like in the cell: =concatenate(A1,B1,C1)
Important: If you are new to formulas, all formulas must start with an "=" or Excel© will not recognize them as a formula, and you'll just end up typing text and it won't help you at all.
Now, let's break down this formula:
= This tells Excel© you're entering a formula
concatenate This is the formula name. It tells Excel© what you want it to do, in this case, combine two are more cells into one.
(A1,B1,C1) This is where you put what you want to combine. In this example, you are combining three cells. I'll show you how to enter text in a minute.
If A1 = "Fred"
B1 = "M."
C1 = "Julian"
This formula would give you: "FredM.Julian"
Now, as you can see, there aren't spaces inherent in the formula. Excel© may be incredibly smart and amazing, but it doesn't know where to put spaces unless you tell it to. Which brings us to inputting text in the formula...
To put text in the formula, simply put it in quotes.
Ex: =concatenate(A1," ",C2)
Quotations in formulas tell Excel© that you want it to print what falls between the quotation marks. Say you wanted to print "Julian, Fred" (using the info above), your formula would look like this:
=concatenate(C1,", ",A1)
or
=concatenate(C1,","," ",A1)
The second example is the same as the first, except that I separated the comma and space. You can do it either way. The second way gets confusing with longer formulas, but I kinda like it for short ones.
A few other things you should know:
1. You must put the formula into a new cell. It's easiest if you insert a new column next to the columns you wish to combine and just combine across.
2. You can select a cell instead of typing A1 or B1 every time. Excel© will input the correct info for whatever cell you select. You can even use the arrow keys to select it. When you hit the comma, the selection tool will return to the formula cell (though the cell you selected should be highlighted in blue or something) and you can move it from there to select the next piece of info.
Commas in Excel© formulas denote the end of a section. The selection tool resets to the active cell after each comma. In OpenOffice©, a semi-colon replaces the comma.
3. Once a formula is written, you can copy/paste it down the whole column. It will change the cell references automatically. (**See Lesson 2: Absolutes)
4. You can concatenate any number of cells from anywhere on the spreadsheet. However, if you copy/paste that formula, you must remember that cell references change in relationship to the formula cell. (see Lesson 2: Absolutes). Besides, it's easier to understand and remember what you're trying to do if it's organized and you just have to read it across the row.
One final note: Before you delete or change any of the reference cells (the cells you combined), you must copy/paste the formulas as values, or they will automatically change when you change the cells, and if you move anything, Excel© goes kinda berserk. To paste it as values, copy the column and go to paste special (either a shortkey, or it's under the "Home" tab) If you use the shortcut, it brings up a dialogue box; check "values only" and hit "ok". If you use the "Home" tab, under the clipboard picture "Paste", click the arrow for more options, and there is a "Paste Values" option. What paste values does is it changes the formulas into text. If you click on the cell before repasting it, the formula bar (the display bar at the top) displays the formula you typed, not the answer. By pasting values, you get rid of the formula, but keep the answer, so it will display the actual text. Once done, you can delete, move, cut, edit or do whatever else you want to the original data (in our example, the first and last names) and the full name will stay the same.
If you have any questions, concerns, or comments, post a comment and I'll answer it as soon as I can. Also, if it's urgent and you understand what I've said here, you'd be fine searching on Google© for "excel 2007 your question" and you'll get some answers that way.
No comments:
Post a Comment