Use Microsoft Excel to split words into cells for easier data manipulation

excel_formulaA client was recently part of an expo and used the opportunity to collect subscribers for their mailing list. Unfortunately the data input method wasn’t designed with their back end database in mind which has a potential customer’s first name and last name separated into different fields.  Luckily the data was clean enough that we could use Excel to perform some manipulation and using formula’s pull words apart.

To do this, there are two formula’s that we need to use, one being LEFT and the other is MID.  In the example above, we have John Smith as the name, we use LEFT to get John and MID to get Smith into separate cells.  The two formulas you need are;

=LEFT(B2,(FIND(" ",B2,1)-1))
=MID(B2,FIND(" ",B2)+1,200)

Using these, simply change the cell location to where your data is (in our case B2). Once everything is split up, the spreadsheet can be exported out and imported into a database table located for example in MySQL.


Leave a Reply