What is an Excel formula?
In Excel you can use codes or a set of commands, which we know as formulas, and enter them into cells to perform calculations or some kind of specific task; they typically use 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 in which we have mixed the first and last names of a list of people, the first thing we need to check is if the next column is empty ; otherwise, click with the right mouse button to select the "Insert" option and thus create a new column.
We will click on the header of the column in which we have the first and last names and then we will select all its rows; then click on the "Data" tab at the top of the screen e select the option "Text in columns", used to separate texts that can be delimited with commas, semicolons, tabs, or whitespace.
The "Wizard to convert text into columns" window will open, mark the radial button " Delimited » and press the «Next» button; This will take us to the next step in that window and into the segment where it says "Separators", let's leave all options disabled except "Space" and we end up pressing «Finish».
I will separate nomi and cognomi with formula in Excel
As is often the case in the computer world, they exist different ways to achieve the same goal ; which is why we can also separate the same list of names and surnames by writing an Excel formula, which will give the same result as we got in the «Data» tab with the «Text in columns» button.
Assuming we have the list of names and surnames in column A, we must keep in mind that columns B and C must be empty so that they are written separately using the formula we are going to execute; The first thing we're going to do is place the mouse in the empty cell next to the name.
Then we move the formula bar to the top of Excel and copy and paste the following = LEFT (A2, FIND («», A2)) and press Enter to execute 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 starts with an = sign, the LEFT function is used to write a certain number 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 it is an empty space; if your Excel language is in English the formula will be = LEFT (A2; FIND («»; A2)).
Now click the bottom right border in the cell where you entered the formula; (the mouse cursor will turn into a thin black cross) e drag down until you reach the list of names and surnames; We do this to replicate the same formula in the other cells of the column which will only show the name.
For the last name we repeat the process again, you position yourself in the empty cell next to the name, go to the formula bar and enter = RIGHT (A2; LONG (A2) -FIND («»; A2)) or = RIGHT (A2; LEN (A2) -FIND («»; A2)) depending on the language and you end up dragging the cell down to copy said formula in the rest of the column.
An Excel sheet is undoubtedly a valuable tool with which to process data in one or more cells, customize the size of columns and rows, plus with Excel formulas you can do things like total amounts; generate averages, show minimum and maximum values or even add data from different cells.
