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