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
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


Related:

3 responses

Blocked Profile
Dec 9, 2015 at 05:20 PM
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!
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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.
Blocked Profile
Dec 14, 2015 at 05:35 PM
and there ya go!
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jan 21, 2016 at 11:42 AM