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 |
Solved! Go to Solution.
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.
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
@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!).
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
@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.
@estherb47thanks for sharing, it is helpful!
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.
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.