Difficult Query [Closed]

Report
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
-
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
-
Hello,

I would like to request for a difficult query that may be not solvable logically.

If user enter az123456789 or az-123456789 it always converted into
AZ-123456789
[A-Z] {2} First Character must be any alphabet in UPPER CASE between A to Z and max strength is 2
[-] {1} After first character Hyphen symbol must be shown and max strength is 1
[0-9] {1 to 9} after hyphen symbol any numeric character between 0 to 9 where min strength could be 7 and max strength could be 9.

OR

If user enter a123456789 or a-123456789 it always converted into
A-123456789
[A-Z] {1} First Character must be any alphabet in UPPER CASE between A to Z and max strength is 1
[-] {1} After first character Hyphen symbol must be shown and max strength is 1
[0-9] {1 to 9} after hyphen symbol any numeric character between 0 to 9 where min strength could be 7 and max strength could be 9.

OR

If user enter a11234567890 or a-11234567890 it always converted into
A1-1234567890
[A-Z] {1} First Character must be any alphabet in UPPER CASE between A to Z and max strength is 1
[0-9] {1 to 9} Second Character must be any numeric character between 0 to 9 where max strength is 1.
[-] {1} After second character Hyphen symbol must be shown and max strength is 1
[0-9] {1 to 9} after hyphen symbol any numeric character between 0 to 9 where Exact, Accurate, Maximum Strength is 10 after third character i.e. hyphen.

Since 3rd requirement create confusion
If this could be solved then I have no words for Thanks


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!
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
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.
Blocked Profile
and there ya go!
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!