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. 

Terms & ConditionsAccessibility statement