Today’s blog post was actually a question from one of my colleagues at work earlier this week, they had a list of first and surnames of employees, separated by a comma, with the surname first and the first names last and they needed to switch them around for example ,from “Green, John” to” John Green”.
So let’s look at two of the ways we could easily do this in Excel.
The first method involves 2 steps.
Method 1. Text To Column & TextJoin
First we can use Text To Columns to split the First Name and Last Name.
- Select the column that contains your data
- Data Tab – Data Tools – Text To Columns
- In Step 1 of the Wizard select Delimited
- We can use comma as the delimiter
- Hit Next, Then confirm to Finish
- Your data will now be split, having used the comma as the delimiter
Once we have our two names separated, we can then join them together again using TEXTJOIN. This is one of the new formulas that cam with Excel 2016. If you don’t have TEXTJOIN then you can use CONCATENATE which works in previous versions of Excel prior to 2016. You can read my blog posts below, if you want to use CONCATENATE.
How To Concatenate Numbers And Percentages
Formula Friday – Let’s Use TEXTJOIN To Join Text Strings Without The CONCATENATE Complexity
Understand The Power Of Concatenation Of Cell Formulas
I have Excel 2016 so I’m going to jump right in and use TEXTJOIN, the updated and more simplified relative of CONCATENATE.
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
Where
delimiter – this is required this is what you want in between the cells that you are joining, for example- a comma, or a space.
ignore_empty – this is so you can choose to ignore and empty cells in your selected range. This can be important if you are using a space " " as your delimter and if you do not want extra spaces in between your joined text strings due to blank spaces in your selected cells.
text1,-is required, which can be a single text string or an array of strings like a range of cells
[text2] – this is an optional argument; use this additional text strings to be added to text1
So, in our example we want the delimeter to be a space, to separate the two names, we want to ignore any extra spaces so we set the ignore_empty to TRUE and select text1 as our First Name (cell C2) and text2 as B2 to populate our Second Name.
Hit Ok and we have our names switched to the correct way around.
Method 2. Use A One Stop Shop Formula
Ok so we have seen a two step approach to reversing the names, let do it all all together with one formula.
The formula uses the functions MID, FIND and LEN.
MID – this returns the middle characters from a text string given a starting and end position.
FIND – this finds the starting position of one text string within another text string.
LEN – returns the number of characters in a text string
Once we put all of these together as one formula, we can easily switch the positions of the two names,
This is a great formula…..if we break it down we can see the way it works.
Excel uses the MID function, which as we know returns the middle characters from a text string given a start number and length. So, we generate the Text piece by joining B2 and B2 again separated by a space, this gives us the following ‘Green,John Green,John’
=MID(B2&” “&B2
We find the start number by finding the position of the comma, then adding +1
=MID(B2&” “&B2,FIND(“,”,B2)+1
We find the number of characters to then return is the number of characters in cell B2
=MID(B2&” “&B2,FIND(“,”,B2)+1,LEN(B2))
Clever huh?. We can then simply drag our formula down the column of data to correctly fill the rest of our names.
So there are two ways to flip the cell contents in Excel. Which would you use????
Don’t forget to sign up to the Excel at Excel Newsletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.
