Merging using the concatenate formula
I am always amazed by how many different ways data is exported from a CSV file depending on where it comes from. With student and project management software, how the data was typed in sometimes also makes a difference. A typical way in which a person's name and surname is displayed is SMITH, John
. In Excel, it is possible to take this field using the Text to Columns comma-delimited method to split the name and surname into two columns, as shown:
You can then concatenate the two cells using the appropriate formula, and you can use the Proper function to get the correct case. Of course, this is time-consuming and there is no quick way of doing the first step automatically. The other problem is that if you are doing this with multiple different documents, it is a complete nightmare.
This first section will compare what we have just done in Excel with getting the same result...