Keep Left or Trim right function

[DonData]

in Client Loc Code I need to keep the first 7 characters in the string & drip the "-" as well as the numbers behind it.  I need either a trim right or a keep left function.  I've been using Alteryx for two hours now & I cant seem to wrap my head

[Kenda · Accepted solution]

Hey @DonData 

If the sting you want to keep will always be 7 characters long, you could use the substring function. It would look like this:

Substring([Client Loc Code],0,7)

This is saying keep only a substring of the field. Start at the 0 character and keep the first 7 characters. 

Otherwise, you could make this dynamic to account for any number of characters are left of the dash. In this case, I could use a RegEx function:

REGEX_Replace([Client Loc Code], "(.*)\-.*", "$1")

This is saying look for the dash and keep everything that is before it. 

The second one is a little more advanced to understand, but let us know if this works for you or you need more explanations. 

[DonData]

Hello Kenda,

Thanks for the quick response.  It seems to be keeping all the characters in the string still.  The formula makes sense to me though.  It's saying "look in this string and keep characters 0 through 7" right? (not sure if that should be 0 through 6) .  I've made another viz.

After filter (5) it still kept the dash & everything after it.  If you or anyone could show me what I'm doing wrong it would be greatly appreciated.  Thanks.

[Kenda]

Hey @DonData 

Sorry for the confusion. Are you wanting to filter your results or change the values?

To change the values, you will want to use a Formula tool.

If you are wanting to filter out any records that contain a dash, that would require this expression in your Filter tool:

contains([Client Loc Code], "-")

[DonData]

Ah!  Correct. I want to change results like these;

TBH0254-03

To these;

TBH0254

So I went to the formula tool.  I am trying;

TrimRight([Client Loc Code],"3") & it looks like its working!!  Thanks for the direction!!

[DonData]

scratch that.  It has to be keep left.

[Kenda]

@DonData  Did you try the substring expression I suggested in an earlier reply?

[grazitti_sapna · Accepted solution]

Hi @DonData,

If you want only the first 7 digits please try and write the below expression in Formula tool.

Substring([Client Loc Code],0,7)

 I can see in the Screenshots that you are using the Filter tool.

Furthermore, since you want only first 7 digits and indexing in Alteryx starts from 0 thus we are initiating our substring from '0' but the length should be 7 only hence the expression.

                                      substring([column_name,starting_index,length)

I hope this solves your issue.

[DonData]

odd, I replied on Friday.  I guess it didnt take.  in my message on Friday I stated that your first solution had worked I was just using the wrong tool & that my brain had probably checked out already.  I used the REGEX formula.