What formula would I use to pull text into a new column?

Kelsee Katsanes

I want to use a formula to isolate the training type into a single column. Currently, "Training Type" is a primary column so I am unable to make it a dropdown selection and automation would take too long to do (there are over 100 rows). So I want to use a formula to isolate the training type from the "Business Segment, RCM Segment, Training Type" column. I initially tried:

=IF([Business Segment, RCM Segment, Training Type]@row = "NewHire", "New Hire")

That didn't work and even switched the = to contains and that didn't work either. As you can tell there are numerous different types of training types and I want to be able to include them all in a single column formula. Any help is greatly appreciated!

Will Jeffords

Hi @Kelsee Katsanes ! I think you want to use CONTAINS formula. If you want me to demonstrate how that would work, I’m happy to…just say the word!

Best,

Will

Will Jeffords

i realized it’s helpful to say you could do nested IF( with the CONTAINS to have the formula accommodate all your Training Types…how many are there out of curiosity?

Brian_Richardson

= IF ( CONTAINS (“NewHire” , [Business Segment, RCM Segment, Training Type]@row ), “New Hire” )

Brian_Richardson

Like Will said, you can nest this:

= IF ( CONTAINS (“NewHire” , [Business Segment, RCM Segment, Training Type]@row ), “New Hire” ,
IF ( CONTAINS (“SomethingElse” , [Business Segment, RCM Segment, Training Type]@row ), “Something Else” , IF ( CONTAINS (“ThirdThing” , [Business Segment, RCM Segment, Training Type]@row ), “Third Thing” )))

And so on for as many as you need

Kelsee Katsanes

https://community.smartsheet.com/discussion/comment/436759#Comment_436759

I tried using contains and it did not work, it shows "#INCORRECT ARGUMENT SET". I have a total of 8 different training types. One of them needs to be specified as unspecified or blank if the requestor does not know.

Brian_Richardson

Incorrect argument set means you didn’t type out the formula correctly. Post it and let’s see if we can fix.

Kelsee Katsanes

https://community.smartsheet.com/discussion/comment/436814#Comment_436814

Brian_Richardson

Use the structure that I posted earlier, no = sign in the middle.

= IF ( CONTAINS (“NewHire” , [Business Segment, RCM Segment, Training Type]@row ), “New Hire” )

Kelsee Katsanes

https://community.smartsheet.com/discussion/comment/436816#Comment_436816

This popped up when I changed it

Brian_Richardson

Can you post what you entered? That error often occurs when you have an extra ) or accident tally referenced a cell instead of using @row

Kelsee Katsanes

https://community.smartsheet.com/discussion/comment/436821#Comment_436821

This is the formula I used

Will Jeffords

@Kelsee Katsanes , looks like you may be missing a comma after "…@row)"

just before the last ""New Hire")"

@Brian_Richardson thanks for jumping in here while I was "offline" this weeked!

happy to help further diagnosis if needed!

Best,

Will

Sarah Keortge

Another option would be to create a helper sheet where you have a distinct list of the values that will be in the Primary column and another column with the isolated training value related to each. Then, use an INDEX/MATCH or VLOOKUP from your sheet to retrieve the training values. A second sheet might feel inelegant at first but the advantage is that it's super easy to add new lookup values without having to adjust your formula each time.

Brian_Richardson

+1 what Sarah said if the BRT text is consistently the same/dropdown picked. It sounds, though, like you're looking for certain keywords in the BRT column that may be present in multiple entries, if that's the case you'll want to use Contains instead which is essentially a keyword search.

If you have very consistent BRT entries and want to just reference them per Sarah's suggestion so it's easier to manage or expand upon going forward, then setup a second sheet with two columns:

BRT column = list of all the options for the Business Segment,RCM Segment,Training Type column

Training Type column = the training type you want to return for each BRT

Then in your original sheet, instead of the CONTAINS / IF formula that Will and I have given, do this instead:

= INDEX ( {Training Type} , MATCH (  [Business Segment, RCM Segment, Training Type]@row , {BRT} , 0 ) )

The {Training Type} reference is a cross sheet reference. When typing the formula click the link in the popup formula helper box that says "Reference another sheet" and then browse to the new lookup sheet you created and click the Training Type column header to select the entire column. Name that reference at the top of the lookup window as "Training Type". Repeat for the {BRT} reference in the formula, selecting the BRT column in your lookup sheet and give it the name BRT.

This is an exact match, so you need to make sure that the BRT entries exactly match capitalization, spacing, commas etc.

Or stick with the CONTAINS formula and build that out and maintain it as a formula instead.