Skip to main content

First, thank you for the awesome support.

I’m digging on Airtable but the customizing of formulas can be a bit daunting in the beginning.


MY QUESTIONS:




  1. As part of a concatenate formula that will create SKU’s for my designs I’d like to incorporate the first five characters of the TITLE of the design. Is this possible?




  2. Is it possible to have an underscore put in the place of a space in the title when the first characters are grabbed? Or does someone have a better suggestion for how to grab the first part of the title?




So if the title for the design is “My Pitbull Is The Best” the formula would grab My_Pi



  1. I will have a single select field {LIGHT DARK} that notes whether the design has light text or dark text. I’d like to have that incorporated into the SKU. I’m assuming this can be created with IF statements?


So the SKU is made up of an

AUTO NUMBER-Five characters of TITLE-LIGHT (Lt) or DARK (Dk)


EXAMPLE:

AUTO NUMBER: 00034 (i’ve already figured out this part of the formula)

TITLE of design: My Pitbull Is The Best (this is the part I really need help with)

LIGHT-DARK: Light (I think I can figure this part out)


SKU CREATED WITH CONCATENATE FUNCTION would result in:

00034-My_Pi-Lt

Try


REPT(

    '0',

    5-LEN(

        {AutoNbr}&''

        )

    )&

{AutoNbr}&

'-'&

SUBSTITUTE(

    ' ',

    '-',

    LEFT(

        {TITLE},

        5

        )

    )&

'-'&

LEFT(

    {LIGHT DARK},

    2

    )

(That assumes the value of {LIGHT DARK} is either 'Light' or 'Dark'. If it’s something else, you’ll have to use an IF() statement, as you mentioned.)

Try


REPT(

    '0',

    5-LEN(

        {AutoNbr}&''

        )

    )&

{AutoNbr}&

'-'&

SUBSTITUTE(

    ' ',

    '-',

    LEFT(

        {TITLE},

        5

        )

    )&

'-'&

LEFT(

    {LIGHT DARK},

    2

    )

(That assumes the value of {LIGHT DARK} is either 'Light' or 'Dark'. If it’s something else, you’ll have to use an IF() statement, as you mentioned.)


Thank you for your help with this formula.


It’s almost working.


Your formula will not show the five characters of the Title

It shows up like this:

00034- -LI


I put in this formula:

REPT(

‘0’,

5-LEN(

{Auto Number}&’’

)

)&

{Auto Number}&

’-’&LEFT(

{TITLE},

5

)&

’-’&

LEFT(

{LIGHT DARK},

2

)


And get 00034-My Pi-LI


So the only part of the formula that isn’t working is replacing any space in the title with a dash

SUBSTITUTE(

’ ‘,

’-’,

Thank you for your help with this formula.


It’s almost working.


Your formula will not show the five characters of the Title

It shows up like this:

00034- -LI


I put in this formula:

REPT(

‘0’,

5-LEN(

{Auto Number}&’’

)

)&

{Auto Number}&

’-’&LEFT(

{TITLE},

5

)&

’-’&

LEFT(

{LIGHT DARK},

2

)


And get 00034-My Pi-LI


So the only part of the formula that isn’t working is replacing any space in the title with a dash

SUBSTITUTE(

’ ‘,

’-’,


Arrggh!


Sorry — My bad: I fouled the order of variables to the SUBSTITUTE() function.


Try this instead:


REPT(

    '0',

    5-LEN(

        {AutoNbr}&''

        )

    )&

{AutoNbr}&

'-'&

SUBSTITUTE(

    LEFT(

        {Title},

        5

        ),

    ' ',

    '_'

    )&

'-'&

LEFT(

    {Light Dark},

    2

    )

(That also uses an underscore instead of a hyphen to replace spaces in the extract from {TITLE}, as you originally requested.)

Arrggh!


Sorry — My bad: I fouled the order of variables to the SUBSTITUTE() function.


Try this instead:


REPT(

    '0',

    5-LEN(

        {AutoNbr}&''

        )

    )&

{AutoNbr}&

'-'&

SUBSTITUTE(

    LEFT(

        {Title},

        5

        ),

    ' ',

    '_'

    )&

'-'&

LEFT(

    {Light Dark},

    2

    )

(That also uses an underscore instead of a hyphen to replace spaces in the extract from {TITLE}, as you originally requested.)


Yaay! Awesome! It worked.


I’m so happy it didn’t work the first time. Great to know the order of how things are written can be important.


Thanks so much.

Reply


Terms & Conditions