Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Alphanumeric ID based on multiple cells

Solved
Jump to Solution
748 2
cancel
Showing results for 
Search instead for 
Did you mean: 
urnbis8786
5 - Automation Enthusiast
5 - Automation Enthusiast

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:

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

So, as an example, ZDJS1411.

Thanks so much community!

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Does this look right?

Screenshot 2024-07-23 at 5.40.42 PM.png

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

See Solution in Thread

2 Replies 2
TheTimeSavingCo
18 - Pluto
18 - Pluto

Does this look right?

Screenshot 2024-07-23 at 5.40.42 PM.png

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.