Separation names in Excel use Flash Fill
To separate them, and names in Excel 2003, 2007, 2010 was quite difficult because a lot of functions used in combination
In the following list, and we want to split the name into two columns, one column contains the name , remaining columns contain surname.
+ For Excel 2003, 2007, 2010 we use the following function in the cell containing the data “Name”
As shown above in cell F3 enter the following command to split one name:
= IF (ISERROR (FIND (“”, TRIM (B3), 1)), “”, RIGHT (TRIM (B3), LEN (TRIM (B3)) -find (“#”, SUBSTITUTE (TRIM (B3), “”, “”, LEN (TRIM (B3)) – LEN (SUBSTITUTE (TRIM (B3), “”, “”))))))
in cell E3 and we enter the following command them split surname:
= LEFT (A3, LEN (A3) compensating (F3))
For Excel 2013 we use new features is “Flash FILL”
With Flash Fill feature will help you to do this easily. First, you need to create two columns contain name and surname. You type one a samble in top row. On the Data tab, select the Fill Flash.
Perhaps the closest feature for Excel 2013 users that Flash Fill. This feature predictive data intelligently and automatically fill in the blanks data on spreadsheets. For example, on a spreadsheet containing a list of staff with a column showing the name in full form.
I enter the sample as shown in Fig 1 then put the cursor at the C3 and press the button on the toolbar FILL FLASH, EXCEL will separate immediately help you
Similarly in our D3 form name and press enter FILL FLASH
I wish you success.
Lượt xem (771)