community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

How to trim anything left of a specific character?

Meteor

Hi Community,

 

I have a column in my data set that looks like below and I want to keep only the characters right to the "-". I wanted to use formula tool and the trim function to do this, but I do not have enough experience to apply my logic. Can anyone help? Thanks in advance!

 

Somekind of ID
ARC-22
BABAHSBB-231
CAP-12b

 

I would want my end result to look like this:

 

Somekind of ID
22
231
12b

 

Alteryx
Alteryx

If your data is consistently separated by the "-" character, you could consider using a simply TextToColumns tool and specify "-" as the separator (example attached)

Alteryx
Alteryx

@aeulusan 

 

Here's an updated example that adds the Formula tool. I've enumerated steps to produce columns that show the length of the string, the character position of the "-", and operations to grab the desired characters. This is a bit over-kill, but it might help you understand some of the possibilities. You should tool the methodology that best fits your data.

Pulsar
Pulsar

Hi @aeulusan 


@BenBo 's solution is a good one, and very easy.

For a formula approach, Trim isn't the function to use. Trim will, as I understand it, remove a specific character from a field, rather than cut off a field at a particular character.

One approach with a formula tool is Right and FindString (to find the position of the -)
Right([Somekind of ID], length([Somekind of ID])-FindString([Somekind of ID], "-")-1)

Another would be to RegEx Replace (this is my preferred method). You're basically telling Alteryx to remove everything up to and including the hyphen
REGEX_Replace([Somekind of ID], "(.*?-)(.*)","$2")

Cheers!

Esther

Alteryx
Alteryx

@EstherB47 great suggestions as well. I posted another example that uses string operations logic. It's similar to your suggestion. RegEx is probably the most powerful, but it can be intimidating for those new to it (and to those who have some experience with it!).

Alteryx Certified Partner
Alteryx Certified Partner

@aeulusan ,

 

In addition to the fine solutions of @EstherB47 and @BenBo I want to toss a 3rd solution into the ring for you.  It is OLD SCHOOL.

 

Substring([Somekind of ID],FindString([Somekind of ID], "-")+1)

Inside of the nest it finds the location of the first '-' dash (adds 1 to it) and then uses a string function (SUBSTRING) to grab everything after that character.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Meteor

@BenBothanks for sharing, it worked! I actually had more than 1 "-" and I needed to parse out the characters after the last "-" occurence. So I applied your formula 2 times for those fields using a IF statement.

Meteor

@EstherB47thanks for sharing, it is helpful!

Labels