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

December 24, 2015

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.

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.

How to allow an Active Directory Certificate Authority to generate Certificates with a Subject Alternative Name attribute

Starting with Google Chrome 58, Chrome no longer trusts certificates without the Subject Alternative Name attribute, so this makes it a little troublesome for those with internal CAs where you rely on them for Software Development. We noticed last week that some end users couldn’t hit an internal application over HTTPS, but was fine in […]

Veeam File to Tape Job and how to backup up files directly from a Network Share

A customer of ours has a large archive of files located on a NAS device (around 15TB worth) that they want to simply push off to tape and then remove from the NAS. Network drives don’t show up in Veeam whilst creating backup jobs, so we needed a way to get this working. We use […]

Add AWS CLI to Windows Terminal

I am a fan of Windows Terminal and some of the recent things Microsoft seem to be doing for SysAdmins. I like having all the tools I need in a single spot. Adding the AWS CLI is fairly straight forward but makes life a heap easier. Start by installing Python from python.org if you don’t […]

A Windows System Admin's Blog