Multi Row If Statement with Contains

[trailfarley]

Im trying to insert a contains function in a multirow if statement to cleanup doc numbers. I am having an issue where the data is adding characters at the end. For example

Doc Number

12345

12345A

I want to remove the A in the second doc number so I tried this formula:

IF CONTAINS([Doc Number],[Row-1:Doc Number])THEN [Row-1:Doc Number] ELSE [Doc Number]ENDIF

It is not producing the output I am looking for

[dhrathod]

use Data cleansing tool - remove letters from DocNumber field

[trailfarley]

Some of the doc numbers have a letter at the beginning and end

[dhrathod]

if I understand it clearly, you need to remove all the letters from DocNumber field. 

Data cleansing will remove any letter from any position from the field & just keep numbers intact.

If i have not understood the issue correctly, please provide more information & workflow if possible (use export function to save the workflow in yxzp format)

[dwstada]

@trailfarley when you use this setting it removes the A in the second row

I am not sure if this will help you for the whole data set tho, two rows is not enough sample data for that :D

are the doc numbers and/or extra characters always structured the same? then you could probably use a regex or trim function instead

[trailfarley]

I tried both the cleansing tool and the "Values for Rows that don't Exist" options with no success. The cleansing tool removes necessary letters and the other had no impact it seems. Here is a list of actual doc numbers I am using. As you can see the first instance is seen in the last two values

[neilgallen]

in your case a regular expression would work within a formula tool.

REGEX_Replace([F1],"(.*\d+?)(\D*)","$1")

the pattern looks for a string of digits, and then removes anything after the number once a non-digit is identified.

Note, this is very specific and might not handle all cases and could need to be modified.

[dwstada · Accepted solution]

@trailfarley your formula worked for me with this data set. there are some entries left with trailing letters, but they don't have a duplicate number in their row-1, so won't be recognized by the formula.

if your doc numbers should always end in a number and if the added character is always one letter, this will remove a trailing letter

[trailfarley]

Thank you everyone for your input! I have realized that my original formula worked, I had doc number checked in the Group By section of the multi row function, which caused errors