Today I wanted to introduce you to a very simple but also very useful function in excel; Concatenate. First off though, here is a context where I will use it. As is so often the case, there would be other ways to get this done but here I’ll use concatenate. First, here is my current set of data:
I would like to change the data to the following form:
FirstName LastName Country
so for the first name it would become:
Roger Federer SUI
First, I’ll get the 3 informations into 3 columns by using the following:
First name =MID(A2,FIND(“,”,A2)+2,FIND(“(“,A2)-FIND(“,”,A2)-3)
Last name =MID(A2,FIND(” “,A2)+1,FIND(“,”,A2)-FIND(” “,A2)-1)
Country =MID(A2,FIND(“(“,A2)+1,FIND(“)”,A2)-FIND(“(“,A2)-1)
The result becomes:
Then I can simply use the concatenate function to combine them by using:
=CONCATENATE(B2,” “,C2,” “,D2)
I also used the “find” and “mid” function a decent bit, you can find out more info about those in previous posts:) You can download the spreadsheet here.
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
