Text to Columns in IDEA

2 posts / 0 new
Last post
Joined: 11/29/2018 - 16:53
Text to Columns in IDEA

I am trying to figure out a way to do a 'text to column' type of task in IDEA instead of Excel. 
I want to be able to take a file that containst a listing of names that contain the first and last name in a single field and create a separate first name field and last name field.
I am hoping for an easy solution for this, it doesnt have to be a script. 

Steven Luciani
Joined: 07/31/2012 - 13:20

This can be done without a script. There are numerous IDEA funtions available under the character subset of the equation editor that will help you with this task. @left, @right, @mid, @split, @simplesplit come to mind.

In the IDEA file you will need to create two virtual character fields. One for first name and one for last name. I like to create virtual fields by right clicking on the field I'm going to be analyzing then choosing Append Field... in the pick list that pops up. This way your new field will drop to the right of the field you right clicked in.

The way your name field is formatted with trigger the equation you will need. Here's an example of a field that is last name, first name so Smith, Will

In this example I would use simple split and create a last name field using the equation @simplesplit(fullnamefield,"",1,", ") and a first name field using the equation @simplesplit(fullnamefield, "",1,", ",1)

Each field length should be long enough to capture large first or last names so you don't truncate letters. If you want you can use the lengh of the original full name field to be safe.

To explain what's happening in those functions: last name is looking in the fullname field starting at the far left then looking for the first "comma then space" it finds and picking up what's between the start and the separator you identified. In the first name field you see an extra one at the end. This tells the function to start from the right and read left so you can grab the first name.

In a real life scenario freeform name fields aren't always perfect so it may take a more complex equation combining @if's and some of the functions listed above to properly split all your names.

Good luck,