Help with Naming Formula for Server Files

Platform discussions
, ,
1

I’ve been searching around and am having a really hard time finding information on this, or maybe I don’t understand all of the formulas yet.

We have a lengthy file naming convention for our CMS and we need to be able to output into a formula column, the summary of the other columns.

The part that I’m running into is that we have a 4 letter acronym for the company followed by a hyphen and then an acronym for the business area that varies in length. (3-5 characters). Both of those are documented in a singular dropdown column.

How can I make the formula work so that it would read something like Company-Item ID-Business Area.

Is there a way to separate something based on the hyphen? Or to just remove the first 5 characters every time?

2

Hi Thomas,

I am not clear whether you want to build a Company-Item ID-Business Area string or deconstruct it.

If you want to concatenate 3 columns together separarated by hyphens, use

CONCATENATE({Company},"-"{Item ID},"-",{Business Area})

If you want to extract the first part of Company-Item ID-Business Area

LEFT({Name},SEARCH("-",{Name})-1)

What if we could break free of the Formula column? and write formulas that update any type of columns? What if a formula could update multiple columns at once? This is possible with the Advanced Formula Booster app.

3

Thank you! I’m looking to build one, but needing to deconstruct one of the columns to break it up. I used the ‘LEFT’ formula above but switched it to the right so that I could grab the ‘business area’ acronym that appears after the hyphen, but it came back with the same number of characters, and some included the hyphen and some included the acronyms from the company.

I guess a simplified version of it all is that I need a formula that can extract the first 5 characters and leave me with whatever is left. The ‘Concatenate’ formula is a huge help for the rest that I need to figure out.

4

To get the first 5 characters, use:

LEFT({Name},5)

To get the rest:

RIGHT({Name},LEN({Name})-5)