Forum Discussion

Debbie Fuller's avatar
Debbie Fuller
Copper Contributor
Jan 24, 2018
Solved

Excel formula/function question

I've been asked to build a function to produce unique client identifiers:

The information needed is first and third letters of surname; first and fifth letters of first name, gender and date of birth. However if the surname is less than 3 letters or the forename less than 5, a "2" needs to be inserted, and if the  date of birth is 04/03/1968, the first zero need to be in the identifier.

This is the formula I have got so far which works fine but does not replace the blanks:

=LEFT(A1,1)& MID(A1,3,1)& LEFT(B1,1)& MID(B1,5,1)  &C1&D1

Thanks

  • Debbie,

     

    may be something like this.

    =LEFT(A1)&LEFT(MID(A1,3,1)&2)&LEFT(B1)&LEFT(MID(B1,5,1)&2)&C1&TEXT(D1,"MMDDYYYY"

2 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor

    Debbie,

     

    may be something like this.

    =LEFT(A1)&LEFT(MID(A1,3,1)&2)&LEFT(B1)&LEFT(MID(B1,5,1)&2)&C1&TEXT(D1,"MMDDYYYY"

Resources