Help

Alphanumeric ID based on multiple cells

Solved
Jump to Solution
543 2
cancel
Showing results for 
Search instead for 
Did you mean: 
urnbis8786
4 - Data Explorer
4 - Data Explorer

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.