Begins with or contains

[Karl_Spratt]

Hi Community,

Need some help please, in my WF I want to add a column and I want to say IF the Item number "begins with, and I have "22" different ATO models the that it can be, and the order type = Sales Orders then select the line for that item from the list. 

I think its a IF contains, else formula but I don't know how to write it. 

Attaching the sample data, if someone can help with the logic please.

I and already bringing into my WF the ATO_Model_Line, in a Join / Union was was going to use this in the formula as the column I was going to rewrite the formula in. 

But I'm sure someone can correct that "bad Idea" 😀😀

TIA Karl. 

[echuong1 · Accepted solution]

A little different way than using an if begins with.

I started by parsing out the item, since the first part of it should correspond with the reference list. I then used the first part as my criteria for a join/vlookup to the reference list to bring in the line value. Because there was fallout (item numbers not in the reference list), I used a union to bring the two data populations back together. I then used a select to remove the parsed out fields, since I didn't need them anymore. I used a formula to update the line value to only keep the value if the order type was sales order.

Hope this helps!

[AngelosPachis]

Hi @Karl_Spratt ,

May I suggest you use a "Find & Replace" tool instead of an If statement.

What the Find & Replace tool will do is look at the Beginning of the field "Item" and search for the values contained in the column "Item Number". If it finds that value at the beginning of the "ITEM" field, it will append the field "Line"

As you can see, some records of the "Line" column will then be null; those are records that do not begin with any of the strings contained in the "Item Number" column. You can then use a filter tool to exclude those nulls and only keep the records that match.

Hope that first of all makes sense and also helps.

Cheers,

Angelos

[Karl_Spratt]

Hi Echuong1,

That worked perfectly, thank you so much for your help here.

Regards,

Karl. 

[Karl_Spratt]

Hi Angelos, 

Thank you so much for your help here.  I used another solution, but thanks for looking at this problem for me, and offering a solution. 

Regards,

Karl.