A Windows System Admin's Blog

Covering Server Administration, Endpoint Management, Scripting and Network Management

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

Fix FortiGate HA out of sync

Sometimes after performing a Firmware upgrade on FortiGate HA pairs, I find that after sometime the cluster still stays out of sync and won’t synchronise. I usually find this is because the checksums of the config files on each or some members are different. To quickly check if this is the case, fire up the […]

Fixing Windows cannot connect to printer with Error Error 0x0000007e when shared on Windows Server 2003 or 2008 32 bit (x86) and your client is 64 bit

So I was out installing a new laptop for a client recently, their server infrastructure is very old (they’re still running Server 2003 but about to migrate) and doing the final stage of the deployment I was installing the local printer in the office but got Windows cannot Connect to the Printer (0x0000007e) error every […]

How to remove the Open File Security Warning Prompt during Driver Deployment or User Login when using MDT or SCCM

During a deployment of Windows or even after Windows is deployed you see an Open File – Security Warning prompt when a .EXE runs (similar to the one below). This happens because when you download an .EXE, .ZIP, or .CAB Internet Explorer (as well as Firefox and Chrome) saves what is called a Zone Identifier, […]