Hi all, I was wondering if you could help me create a formula that would output an alphanumeric ID based on variables in other cells in the row. I'd like to create an ID that include:First, if Cell A = xYx then add "Z" to start. If Cell A = YxY then add "ZD" to start.Then, take first letter from name and surname, and then add these. So John Smith becomes JSThen, add first 4 digits from date field (DOB). So, 14/11/1980 becomes 1411So, as an example, ZDJS1411.Thanks so much community!

Solved

Alphanumeric ID based on multiple cells

Forum|Forum|1 year ago
July 22, 2024
2 replies
27 views

+4

urnbis8786
New Participant

Hi all, I was wondering if you could help me create a formula that would output an alphanumeric ID based on variables in other cells in the row. I'd like to create an ID that include:

First, if Cell A = xYx then add "Z" to start. If Cell A = YxY then add "ZD" to start.
Then, take first letter from name and surname, and then add these. So John Smith becomes JS
Then, add first 4 digits from date field (DOB). So, 14/11/1980 becomes 1411

So, as an example, ZDJS1411.

Thanks so much community!

Best answer by TheTimeSavingCo

Does this look right?

SWITCH(
  {Cell A},
  'xYx', 'Z',
  'YxY', 'ZD'
) &
LEFT(
  {First name},
  1
) &
LEFT(
  Surname,
  1
)&
DATETIME_FORMAT(
  DOB,
  "DDMM"
)

+31

TheTimeSavingCo
Brainy
Answer
Forum|Forum|1 year ago
July 23, 2024

Does this look right?

SWITCH(
  {Cell A},
  'xYx', 'Z',
  'YxY', 'ZD'
) &
LEFT(
  {First name},
  1
) &
LEFT(
  Surname,
  1
)&
DATETIME_FORMAT(
  DOB,
  "DDMM"
)

Adam, Airtable Consultant - thetimesaving.company

Like

U

+4

urnbis8786
Author
New Participant
Forum|Forum|1 year ago
July 23, 2024

Does this look right?

SWITCH(
  {Cell A},
  'xYx', 'Z',
  'YxY', 'ZD'
) &
LEFT(
  {First name},
  1
) &
LEFT(
  Surname,
  1
)&
DATETIME_FORMAT(
  DOB,
  "DDMM"
)

Wow... Yes, that absolutely does! Thanks so much Adam.

Like

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded