Hi! Referring to the previous topic "Excel-Merging columns", this topic will share how to separate text with space in the middle into two separate columns. Please let me use the same example of "Merging columns". In the example, you are the one who need to collect all list of guest for the party. However different department had sent you the Excel sheet with two type of guest list. First type, "First name" and "Last name" of guests are in the same column. Second, "First name" and "Last name" are separate in two column. Previously, you prefer to have "First name" and "Last name" combine together in one column, so then, you need to use "&" to help merge different columns into on column. This time, you change your mind and want to separate "First name" and "Last name" into two separate column. Please follow the instruction below.
Note: On 20 Aug 15 VDO version is available here Excel Online tutorial - Separate Text to Columns
Note: On 20 Aug 15 VDO version is available here Excel Online tutorial - Separate Text to Columns
This is more easy than the merging column. We can get start now.
1. Normally, person who record "First name" and "Last name" into one column will make at least one space to separate first name and last name. For the picture below, I made different type of space. Please have a look at "Jimmy Chan", I just put more space for first and last name. However, the rest of name, I just let it be like when I got this list from other department.
2. To start, make a highlight from cell "c5" till cell "c13". Then click on tab menu "DATA". Next, click "Text to columns"
3. After click "Text to columns", the window of "wizard-Step 1 of 3" will display as below picture. Click to select "Delimited" and click "Next >".
4. Step 2 of 3 window will display, click to select "space". The example will show in "Data preview". Then click "Next >".
5.We come to the last step. Now wizard -Step 3 of 3 will display. Select "General". For the "Destination" you can leave it like the wizard provided. (It will replace your first column and separate text after space to next column on right hand. For this example, it will replace "First name" on "c5" and down. Then, Last name and other text after space will be in column D, E onward.) Then click "Finish".
6. Done!! You will get result as below picture. You can see "First name - Last name" are separated into different column since there is space after each word. More or less space is doesn't give any different. Excel will consider as same space. See example of "Jimmy Chan" and "Daniel Lee".
Watch VDO...
Thanks for today. G'Nite.
Other Excel lessons please go here "Excel makes your work easy"
|
