I had the need to fix mistakenly entered email addresses in an Excel, and luckily the mistake was a recognizable pattern. They should have been email@example.com but were entered as firstname.lastname@example.org. This short post explains how I extracted the names and switched them.
How to Swap Parts of an Excel String
First assume you have
**email@example.com in cell A2, and that what you want is
firstname.lastname@example.org in cell A5. Enter this formula in cell A3:
That extracts “smith” out of the email address in A2, by using the FIND function to return the number of the position of the period. You subtract 1 to tell LEFT when to stop extracting.
Next, in cell A4, enter this formula:
That uses MID to extract from one after the period, through one behind the @. This is about finding the positions of parts of the string you can see are in a pattern, and subtracting or adding 1. Now in A5 you can concatenate the two in the proper order, and add the domain, thusly:
That’s it & good luck!