Everyone who work with Excel may has experience getting Excel file from different source. Like I am a host of office new year event and I have to collect guest list from different department in my company. Unfortunately, I didn't give the Excel form for those department to collecting the guest list. It is end up with different of guest list format. Let's say one format first name and last name was separate into 2 columns. Another format first name and last are combining into 1 column. In this case if your preferred format is first name and last name should be in the same column you can use "&" to merge 2 or more column together. Please follow below instruction.
Note: VDO version (5 mins) is available at "Excel-tutorial-MergeColumns" (English)
on 11 Aug 15 Thai version is also available "เพิ่มประสิทธิภาพงานด้วย Excel"
Note: VDO version (5 mins) is available at "Excel-tutorial-MergeColumns" (English)
on 11 Aug 15 Thai version is also available "เพิ่มประสิทธิภาพงานด้วย Excel"
Now, we will focus on the Guest list from "Sales" department. This sheet listed "First name" on column "C" and "Last name" on column "D".
1. On Sales department sheet, make a right click on column "D".
2. Once drop down menu display, click on "insert" to insert one column.
3.Now, "Last name" will be pushed to column "E" and column "D" will be empty.
4.Click on cell "D5" and make space by hitting "space button" on your keyboard twice.
5.Click on right bottom of cell "D5" and drag to cell "D15". This is just to copy "space" that you made on "D5" to cell "D6" until "D15".
6. Next, click on cell "F5" and type "=". Then click on cell "C5". Then type "&" ( now you will see "=C5&" show on cell "F5" as picture below.
7.Continue from 6, then click cell "D5" and type &. Then click cell "E5". You will see something like picture below and hit "Enter".
Note: you can also click on cell "F5" and type =c5&d5&e5 directly to this cell and hit "Enter" on your keyboard.
8. After hit "Enter", you will see value of "First name Last name" (same as left picture below) in cell "F5". Unless you see "First nameLast name" (There is no space between "name" and "Last". This meant you didn't put any space on cell "D5". See picture right below.)
9. Next, click on right bottom corner of cell "F5" and drag to cell "F15". This to copy pattern of cell "F5" to all the cell below. Another way, you can just copy cell "F5" and paste to every cell below.
You will see the result as below picture.
Note: If you add more space or any character on "D5" to "D15", it will show on "F5" to "F15". Please try.
10. Hey !, It's almost done one more step to go. Because if you click on any cell from "F5" to "F15" you will see the formula pattern like "c5&d5&e5", with this format you can't delete or change any value of cell "C5" until cell "e15" or even copy the result on cell "F5" to "F15" to paste on other Excel sheet. So, the last step, you need to copy cell "F5" to cell "F15" and paste "values" on the destination cell. To copy you can do by clicking on cell "F5" and hold "shift" key on your keyboard then click on cell "F15". Then make a right click on your mouse and select "copy" on the menu. See picture below.
11. After click "Copy", please find some cell that you want to paste the selected value. For this example, click cell "H5". Then make a right click. Once the menu display, just click "paste special" and select "first icon" or "Values". See picture below.
12. Now, your cell "H5" to "H15" will get same information look like cell "F5" to "F15". But when click on any of "H5" to "H15", you will get exact value as you've seen. For example, if you click on H5 you will see "=C5&D5&E5". If you click on "H5" you will see "Last name First name". This is done. You can copy these values to other Excel sheet now.
Watch VDO...
Thank you.
Other Excel lessons please go here "Excel makes your work easy"
|
