Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

How to trim anything left of a specific character?

aeulusan
7 - 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

 

11 REPLIES 11
BenBo
Alteryx Alumni (Retired)

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

BenBo
Alteryx Alumni (Retired)

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

estherb47
15 - Aurora
15 - Aurora

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

BenBo
Alteryx Alumni (Retired)

@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!).

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
aeulusan
7 - 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.

aeulusan
7 - Meteor

@estherb47thanks for sharing, it is helpful!

Kamal1406
5 - Atom

I am new to Alteryx and faced a similar situation in trimming a part of the word from right. The following worked for me and thought of sharing.

 

I had the following string data in a field called 'CATEGORY', separated by a " " (space).

 

CATEGORY

Software Technology

Hardware Technology

 

I needed to retain the 'Technology' word and trim everything else in the field. I used the below formula:

 

Fx = RIGHTPART([FIELD]," ")

 

I tried with LEFTPART as well and as long as we have a common separator (eg, the space in the above example), it seems to work fine.

 

Hope this helps.

 

 

icchung
6 - Meteoroid

Hi All,

How to trim all strings left or right of " - " (I want to remove all bolded words or characters)


TRIM LEFT
example: ${custom1} - Balances the organization’s focus between long-term strategic choices and short-term marketplace dynamics5

result: Balances the organization’s focus between long-term strategic choices and short-term marketplace dynamics5

 

TRIM RIGHT

example: SmartsheetUnable to AssessMinimal Experience/Exposure - Unable to perform all the tasks at the "Beginner" levelBeginner - Able to view files from Smartsheet owners. Download and print a file. Make updates.Intermediate - Able to change the view, Assign tasks, Create dependenciesAdvanced - Able to create Smartsheets from inception. Develop use cases. Manage user access. - ${custom2}

result: SmartsheetUnable to AssessMinimal Experience/Exposure - Unable to perform all the tasks at the "Beginner" levelBeginner - Able to view files from Smartsheet owners. Download and print a file. Make updates.Intermediate - Able to change the view, Assign tasks, Create dependenciesAdvanced - Able to create Smartsheets from inception. Develop use cases. Manage user access.

 

Labels