Difficult Query
Closed
smuneeb
Posts
77
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 January 16, 2023  Jan 21, 2016 at 11:42 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023  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
 Excel query from another sheet  Guide
 Power query type date  Guide
3 replies
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
January 16, 2023
547
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
January 16, 2023
547
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