880632_en-US

liliconcom

How do a split a column at the 5th character?

I have a column that I want to split.  e.g 18.45>  or 16.45< I want to make 1 column with the no. & 1 column with the < & >.

Basic Data Analysis and ModelingRe: How do a split a column at the 5th character?

Hi @lisaash,

 

You could use the Recode Data in a Column option to extract segment, or use column formula :

- Settings to extract the value : 

Or formula : 

Word( [1 5], :Value, "", Unmatched( :Value ) )

- Settings to extract the sign at the end :

Or formula : 

Word( -1, :Value, "", Unmatched( :Value ) )

 

If your values may have a different format/length, I think a Regex may be a more flexible option.

Please find attached the datatable used for the demonstration, containing the formula described above in the "Value extracted" and "Sign extracted" columns.

 

Hope this answer will help you,

Re: How do a split a column at the 5th character?

You can use Recode in many different ways to do this (like Victor did demonstrate). If you need first 5 characters and then the last character, you can also create two formula columns

Left(:value, 5)

and

Righ(:value, 1)

Or for the number part use formula

Word(1, :Column 1, "<>")

and same right formula for the sign