How to remove text characters at end of string to create number

[Royalter21]

Hi all,

Looking to remove the text off the end of this string;

Credits_Detail	ClosingBalance_Detail
161,733,183.40CR	89,554,181.58DR

Any ideas? 

[JosephSerpis]

Hi @Royalter21 one way to tackle this challenge is to use Regex within a multi field tool. The Regex I used in my example will find two uppercase letters near the end of the string and replace it with everything it finds before that.

[Deano478]

hey @Royalter21 this should work

[Royalter21]

Hi Joseph,

Thanks for suggested solution - do you also have a workaround for how to then change the fields to fixed decimals? 

Thanks

[JosephSerpis]

Hi @Royalter21 you need to change the size of the data when you select fixed decimal. The length (precision) of a fixed decimal is equal to the width of the integer (left side of decimal) plus the decimal point plus the width of the scale (right side of decimal). Therefore you need to change it in your configuration. You can found out more about fixed decimal data type here. You also may have to remove the comma separtors in your data as well as that may cause some issues. However a replace formula can easily remove them.

[aatalai]

@Royalter21a) You could also use the data celanese tool and remove letters functions, that might be the easiest way, if it is always in that format you provide and it is the letters you want to get rid of.

b) again if it is the same format you could use this in the multi column tool/formula tool. left([field names],(length[field names]-2))