Multi Row If Statement with Contains

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

Help

Accepted answers

dwstada

@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

remove letter at the end.yxzp

All comments

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

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

Doc Numbers.xlsx

dwstada

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

remove letter at the end.yxzp

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703