Are you ever working in a spreadsheet and need to combine two columns or separate two columns? Below you will find the instructions on how to add two columns together as well as how to separate two columns to meet your needs.
Let's pretend we have a spreadsheet that contains the information for 300 of our clients. We would like to pull together a mailing list, but the Last Name and First Name fields are separated. We would need to combine them. Of course we can do this automatically in Word using a Mail Merge, but lets just say for this example that is not an option.
Below is an example of our spreadsheet.
We need to add a blank column to column C (to do this, highlight column C, right-click and choose Insert. Now, add a column header of "Full Name"). Now, in C2 we will start the Concatenate function.
On the Formula toolbar you will see a button called "Insert Function". DO NOT be afraid of this button. It is a wonderful tool! While cell C2 is selected click on the Insert Function button (I've highlighted it in red below). The "Insert Function" window will pop-up. In the "Search for function:" field type in Concatenate and hit the the "Go" button. The Concatenate function will now show up in the "Select a function" box. Highlight it and then hit the "OK" button.
Now, you will see the function wizard (see below). In the "Text1" box enter in "B2" or click the B2 cell and it will automatically add B2. Now, if we put A2 into Text2 you will see that the function has concatenated the two columns, but they are all one word, "DarleneSmith". See red box below. This shows you what your function will look like if you hit OK. Do not hit OK. We we would like a space between the First and Last Name.
So, we would need to add a space. In Text1 we will leave it as is, "B2" and in Text2 we will add " " (double quote, followed by a space, followed by a double quote). All text in a formula should be between double quotes. Then, in Text3 we will add in A2 (to add the First Name in). Now, click OK.
Once you click OK you will see "Darlene Smith" in C2. You may now copy your function from C2 and place it in the remaining C cells so that the remaining names will combine.
Please note, if you wanted your field for Full Name to be "Last Name, First Name" you would've put "A2" in Text1, ", " in Text2 and then B2 in Text3. This would've changed the Full Name for Darlene to "Smith, Darlene". It really depends on what your needs are.
Now, if you had a full name field and you wanted to separate the names out you can use the "Text to Columns" button on the Data toolbar. I've deleted columns A and B because we will be pretending we do not have a separate First Name and Last Name field. That is what we are wanting in our spreadsheet.
What I would do first is move your Full Name column so it appears on the end of your spreadsheet (I moved it from column A to column F by highlighting column A, right-clicking and choose Cut, right-click column H and choose Insert Cut Cells). My current spreadsheet looks like the below.
The reason we moved the column is because when it separates the names it will put the first name in column F and the last name in column H. If we had left the Full Name field in column A the last name would've overwritten column B.
Now we need to highlight all the names in column F. Then, click the Data toolbar and choose the "Text to Columns" button (I've highlighted the button in red below to show you which button it is).
Now, The Text Wizard is now up on your screen. The first step is to tell the wizard if we want to use Delimited or Fixed width. For the this example we are using "Delimited". We have a character (the space) that we are going to have Excel use to determine where we want it to break our columns. It could be any character (comma, semi-colon, a certain letter, a dash, etc). Fixed width would be used if your field contained a value that was the same number of characters (for example, social security number with no dashes). You could have excel parse the data to give you just the last 4 digits, but that isn't what we want here. So, click on "Delimited" and then choose the "Next" button.
You will now see the 2nd step in the Convert Text to Columns Wizard. The "Delimiter" may have defaulted to "Tab". We want it to be "Space" (see arrow below). So, please uncheck anything that is checked and make sure only "Space" is checked. Now, don't change anything else. If you look down in the red box that I have highlighted below you will see that the "Data Preview:" line is separating the data right on the 1st character of the Last Name. This is EXACTLY what we want. Click the "Next" button.
Now, do not let the next step scare you. This is just Excel wanting know what type of data you want your data to be. Since we are dealing with text (and not numbers) we will choose "Text" from the "Column data format" and then click the Finish button.
You will now see that Excel has parsed the field and now First Name and Last Name are separate (see below).
You would now need to change your column headers to match your data and do any formatting necessary. I would just use the format painter for formatting the Last Name field.
Do not be afraid to play around with Excel functions and get to know them. Just back up your data so that if you do mess something up you can go back to your original. Have fun with Functions!
Also, please keep in mind that if you send me a message or comment below and tell me something you would like me to cover I will do a post on it. I always welcome ideas! I can post on Microsoft Word, Excel, Power Point, OneNote, Facebook or most basic computer questions.
Let me know if I've helped you or if you've learned anything. This will encourage me to do more posts.
No comments:
Post a Comment