Text to Columns in IDEA
Forums
Hello,
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.
thanks!
One suggestion I can provide
One suggestion I can provide if your goal is to just pull out numbers from the substr_json_eviado_700_1200_ field is to use the @justnumbers function. You would create a virtual numeric field and in the example you woul want it to be 1 decimal place. Your equaiton would be @justnumbers(substr_json_eviado_700_1200_)/10 which would return 11062.7 as a number.
This will only work if each of your numbers are to one decimal place.
If you have instances where there are numbers at the begining of the string and the end of the string and you are only interested in the numbers at the end of the string then you should use @justnumberstrailing(). There is also a function that will allow you to focus on just numbers at the beginning of a string @justnumbersleading()
This can be done without a
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,
Steven