Difficult Query
Closed
smuneeb
Posts
67
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017

Dec 9, 2015 at 09:50 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022  Jan 21, 2016 at 11:42 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022  Jan 21, 2016 at 11:42 AM
Related:
 Difficult Query
 How to avoid duplicate records in sql select query  Guide
 Power query desktop download  Download  Data management
3 responses
Nothing is impossible with software!
Post what you have and we can help when you are stuck. This is a VERY complex question for us to just GIVE you the solution turn key. Post your code and we can help where you are stuck!
Post what you have and we can help when you are stuck. This is a VERY complex question for us to just GIVE you the solution turn key. Post your code and we can help where you are stuck!
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Dec 14, 2015 at 10:30 AM
Dec 14, 2015 at 10:30 AM
Hi Smuneeb,
Try the following formula:
=IF(AND(ISERROR(LEFT(A1,1)+1)=TRUE,ISERROR(MID(A1,2,1)+1)=TRUE,MID(A1,2,1)<>"",LEN(A1)=11),UPPER(LEFT(A1,2)&""&RIGHT(A1,LEN(A1)2)),IF(AND(ISERROR(LEFT(A1,1)+1)=TRUE,ISERROR(MID(A1,2,1)+1)=TRUE,LEN(A1)=12),UPPER(A1),IF(AND(ISERROR(LEFT(A1,1)+1)=TRUE,LEN(A1)=10),UPPER(LEFT(A1,1))&""&RIGHT(A1,LEN(A1)1),IF(AND(ISERROR(LEFT(A1,1)+1)=TRUE,LEN(A1)=11),UPPER(A1),IF(AND(ISERROR(LEFT(A1,1)+1)=TRUE,LEN(A1)=12),UPPER(LEFT(A1,2))&""&RIGHT(A1,LEN(A1)2),UPPER(LEFT(A1,1))&MID(A1,3,1)&""&RIGHT(A1,LEN(A1)3))))))
Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Try the following formula:
=IF(AND(ISERROR(LEFT(A1,1)+1)=TRUE,ISERROR(MID(A1,2,1)+1)=TRUE,MID(A1,2,1)<>"",LEN(A1)=11),UPPER(LEFT(A1,2)&""&RIGHT(A1,LEN(A1)2)),IF(AND(ISERROR(LEFT(A1,1)+1)=TRUE,ISERROR(MID(A1,2,1)+1)=TRUE,LEN(A1)=12),UPPER(A1),IF(AND(ISERROR(LEFT(A1,1)+1)=TRUE,LEN(A1)=10),UPPER(LEFT(A1,1))&""&RIGHT(A1,LEN(A1)1),IF(AND(ISERROR(LEFT(A1,1)+1)=TRUE,LEN(A1)=11),UPPER(A1),IF(AND(ISERROR(LEFT(A1,1)+1)=TRUE,LEN(A1)=12),UPPER(LEFT(A1,2))&""&RIGHT(A1,LEN(A1)2),UPPER(LEFT(A1,1))&MID(A1,3,1)&""&RIGHT(A1,LEN(A1)3))))))
Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Jan 21, 2016 at 11:42 AM
Jan 21, 2016 at 11:42 AM
This thread is continued here:
https://ccm.net/forum/affich868522userfriendlycodeforadmissionno#p868988
https://ccm.net/forum/affich868522userfriendlycodeforadmissionno#p868988