Forum Discussion
Debbie Fuller
Jan 24, 2018Copper Contributor
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
Sort By
- Detlef_LewinSilver 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"
- Debbie FullerCopper Contributor
Thanks Detlef,
It works. I am very grateful