Excel is a phenomenal work tool that allows us to do everything from sums in the cells of different sheets to more complex operations; which is perfect for a wide range of professional and academic activities. But sometimes we need to separate some data. Well, we can separate names and surnames with formulas in an Excel sheet. How?
What is an Excel formula?
In Excel you can use codes or a set of commands, which we know as formulas, and insert them into cells to perform calculations or some type of specific task; generally using the values contained in one or more cells within a workbook, and which ultimately return a result.
Separate first and last names in an Excel sheet
To split a column where we have mixed the name and surname of a list of people, the first thing we must check is if the column next to it is empty; otherwise we right click with the mouse to select the “Insert” option and thus create a new column.
We will click on the header of the column where we have the names and surnames and thus select all its rows; then we click on the “Data” tab at the top of the screen and check the “Text in Columns” option, which is used to separate texts that can be delimited with commas, semicolons, tabs, or blank spaces.
The “Wizard to convert text into columns” window will open, we mark the radial type button “Delimited” and press the “Next” button; that will take us to the next step of said window and in the segment where it says “Separators”, we leave all the options except the “Space” option disabled, and we finish by pressing “Finish”.
Separate first and last names with formulas in Excel
As is often the case in the computer world, there are several different ways to achieve the same goal; This is why we can also separate the same list of names and surnames by writing an Excel formula, which will give the same result that we obtained in the “Data” tab with the “Text in Columns” button.
Assuming that we have the list of names and surnames in column A, we must take into account that columns B and C must be empty so that they are written separately by means of the formula that we are going to carry out; The first thing we will do is position ourselves with the mouse in the empty cell next to the first name.
Then we move to the formula bar at the top of Excel and copy and paste the following =LEFT(A2;FIND(” “;A2)) and press Enter to run it; and if everything went well we will immediately see the name of the first row in the cell (in our example it is 2 since row 1 has the column titles).
Every formula in Excel begins with an = sign, the LEFT function is used to write a certain amount of characters based on a text (in the example it is cell A2), and the FIND function returns the position number of a character, which in this case is a blank space; if the language of your Excel is in English the formula will be =LEFT(A2;FIND(” “;A2)).
Now you click on the lower right border in the cell where you have put the formula; (your mouse cursor will change to a thin black cross) and drag down as far as the list of first and last names; We do this to replicate the same formula to the other cells in the column that will display the name only.
For the last name we repeat the process again, you position yourself in the empty cell next to the first name, go to the formula bar and place =RIGHT(A2;LENGTH(A2)-FIND(” “;A2)) or =RIGHT (A2;LEN(A2)-FIND(” “;A2)) depending on the language and finish by dragging the cell down to copy said formula to the rest of the column.
An Excel spreadsheet is undoubtedly a valuable tool with which you can process data in one or more cells, you can customize the size of columns and rows, also with Excel formulas you can do things like total amounts; generate averages, show the minimum and maximum value, or even add the data from different cells.
