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

,

A 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.

excel_formula

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

More Posts

Fixing The trust relationship between this computer and the primary domain failed Error when restoring a Snapshot or Old Virtual Machine

I had a VM snapshot/checkpoint running for several months in my test lab and after reverting the snapshot back I went to login to the machine and got the dreaded Trust relationship between this computer and the primary domain failed error message.   So I logged in as a local admin onto that machine, opened […]

How to Install a trusted certificate onto a FileZilla FTP server to enable FTPS (FTP over TLS)

I was recently helping troubleshoot an issue where as part of that I needed to get a 50GB SQL Database transferred from an interstate client onto our servers.  Both the client and us have a decent Internet Connection and we already had an FTP server in place but I was worried about the sensitive nature […]

Procurve Switches and Windows Network Load Balancing in Multicast Mode causing high collision and drop rates

Over the last few days we have been looking at getting a Client Access Array going for our Exchange 2010 setup for basic redundancy and load balancing. I thought I would outline an issue we discovered with using Windows Network Load Balancing in a HP switching environment. The first is whether to use Unicast or […]