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

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

lisaash — Fri, 20 Jun 2025 07:49:40 GMT

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 < & >.

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

Victor_G — Fri, 20 Jun 2025 08:15:24 GMT

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?

jthi — Fri, 20 Jun 2025 12:05:45 GMT

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