Skip to main content

hi dear Folks,
I’m wondering, is there any way I can generate a three alphabet code based on another field which is a long title.

For example;
A Field:
The British Wooden Bookshelf
Can I generate the B Field automatically to:
TBW

Have to consider, if A Field doesn’t have at least three words, for example;
A Field:
Red Glasses
Can I generate the B Field automatically to:
REG

or, if A Field only have one word, for example;
A Field:
Armchair
Can I generate the B Field automatically to:
ARM

Thank you.

Doing this will require 6 fields, in addition to your Field A. Here’s a screenshot of the setup, and then I will follow it with the formulas used:

Field B:

IF(
  SEARCH(
    ' ',
    {Field A}
  ),
  TRIM(
    LEFT(
      {Field A},
      SEARCH(
        ' ',
        {Field A}
      )
    )
  ),
  BLANK()
)

Field C:

IF(
  {Field B},
  TRIM(
    RIGHT(
      {Field A},
      LEN({Field A}) -
      SEARCH(
        ' ',
        {Field A}
      )
    )
  ),
  BLANK()
)

Field D:

IF(
  {Field C},
  TRIM(
    LEFT(
      {Field C},
      SEARCH(
        ' ',
        {Field C}
      )
    )
  )
)

Field E:

IF(
  {Field C},
  TRIM(
    RIGHT(
      {Field C},
      LEN({Field C}) -
      SEARCH(
        ' ',
        {Field C}
      )
    )
  )
)

Field F:

IF(
  {Field E},
  TRIM(
    LEFT(
      {Field E},
      SEARCH(
        ' ',
        {Field E}
      )
    )
  )
)

Field G:

IF(
  {Field F},
  UPPER(
    LEFT(
      {Field B},
      1
    ) &
    LEFT(
      {Field D},
      1
    ) &
    LEFT(
      {Field F},
      1
    )
  ),
  IF(
    {Field C},
    UPPER(
      LEFT(
        {Field B},
        2
      ) &
      LEFT(
        {Field C},
        1
      )
    ),
    UPPER(
      LEFT(
        {Field A},
        3
      )
    )
  )
)
Terms & ConditionsAccessibility statement