Recognize the Origin

[mauriciomtzz]

I have three diferents arrays of numbers and i know wich is the origin when i see the serial, but how can idetify with a formula e.g


12AA1234

12A1A123

12A1234A


FIRST TWO DIGITS MENS YEAR 12

THREE AND FOUR DIGITS ARE A LETTERS MEANS ONE ORIGIN

THREE AND 5 DIGITS MEANS SECOND ORIGIN

THREE AND 8 DIGITS MEANS THREE ORIGIN


The first letter change acorring with the months and serial

A January B February C March etc

and the second letter change after a determinate number of serial

 

[DonMinter]

If you want to pull out the third letter (for example), you can use this code to find the third character in the text in cell A1:


=MID(A1,3,1)


If you want the third and fourth character, it becomes:


=MID(A1,3,2)


The formula means:


Look at the first variable (A1 or some hard-coded text, for instance), make this cell's value starting at the second variable, and as many characters as the third variable.


Restated: =MID("12345678",N,X) will give me X characters starting at Character N for the text.


HTH,

Don

 

[Faseeh]

Hi mauriciomtzz


Lets breakup thing and then say which part represent which thing:

[pre]

12      A         A1234
Year    Month     Serial No
What do you mean by following:

THREE AND FOUR DIGITS ARE A LETTERS MEANS ONE ORIGIN
THREE AND 5 DIGITS MEANS SECOND ORIGIN
THREE AND 8 DIGITS MEANS THREE ORIGIN

[/pre]
Any clues??


Regards,

Faseeh

 

[SirJB7]

Hi, mauriciomtzz!


Just in case you couldn't handle neither to build the formula following DonMinter's more than clear guidelines nor to interpret them, and considering the silence or your no-more-comments, I think here's the formula you were looking for. Check it out:


=CONCATENAR("1st.Orig.";SI(ESERROR(ENCONTRAR(EXTRAE(A1;3;1);"ABCDEFGHIJKL"));"NO";ELEGIR(ENCONTRAR(EXTRAE(A1;3;1);"ABCDEFGHIJKL");"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Ago";"Sep";"Oct";"Nov";"Dic"));"-";"2nd.Orig.";SI(ESERROR(ENCONTRAR(EXTRAE(A1;5;1);"ABCDEFGHIJKL"));"NO";ELEGIR(ENCONTRAR(EXTRAE(A1;5;1);"ABCDEFGHIJKL");"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Ago";"Sep";"Oct";"Nov";"Dic"));"-";"3rd.Orig.";SI(ESERROR(ENCONTRAR(EXTRAE(A1;8;1);"ABCDEFGHIJKL"));"NO";ELEGIR(ENCONTRAR(EXTRAE(A1;8;1);"ABCDEFGHIJKL");"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Ago";"Sep";"Oct";"Nov";"Dic")))


-----> in english:


=CONCATENATE("1st.Orig.",IF(ISERROR(FIND(MID(A1,3,1),"ABCDEFGHIJKL")),"NO",CHOOSE(FIND(MID(A1,3,1),"ABCDEFGHIJKL"),"Jan","Feb","Mar","Apr","May","Jun","Jul","Ago","Sep","Oct","Nov","Dic")),"-","2nd.Orig.",IF(ISERROR(FIND(MID(A1,5,1),"ABCDEFGHIJKL")),"NO",CHOOSE(FIND(MID(A1,5,1),"ABCDEFGHIJKL"),"Jan","Feb","Mar","Apr","May","Jun","Jul","Ago","Sep","Oct","Nov","Dic")),"-","3rd.Orig.",IF(ISERROR(FIND(MID(A1,8,1),"ABCDEFGHIJKL")),"NO",CHOOSE(FIND(MID(A1,8,1),"ABCDEFGHIJKL"),"Jan","Feb","Mar","Apr","May","Jun","Jul","Ago","Sep","Oct","Nov","Dic")))


Regards!


PS: I'd recommend you to give a look at DonMinter's comments in this post: http://chandoo.org/forums/topic/am-i-too-suspicious