Skip to main content

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!

Does this look right?

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

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. 

Reply


Terms & Conditions