Eloise T
Active Member
This formula takes a string of characters (alpha and numbers) which is a TV model number and extracts the TV size which is always the first two consecutive numbers. It has been displayed as you see it to make it easier to understand, but it is one long string on the spreadsheet.
=IF(ISERROR(VALUE(MID(A1,1,2)))=FALSE,VALUE(MID(A1,1,2)),
IF(ISERROR(VALUE(MID(A1,2,2)))=FALSE,VALUE(MID(A1,2,2)),
IF(ISERROR(VALUE(MID(A1,3,2)))=FALSE,VALUE(MID(A1,3,2)),
IF(ISERROR(VALUE(MID(A1,4,2)))=FALSE,VALUE(MID(A1,4,2)),
IF(ISERROR(VALUE(MID(A1,5,2)))=FALSE,VALUE(MID(A1,5,2)),
IF(ISERROR(VALUE(MID(A1,6,2)))=FALSE,VALUE(MID(A1,6,2)),
IF(ISERROR(VALUE(MID(A1,7,2)))=FALSE,VALUE(MID(A1,7,2)),
IF(ISERROR(VALUE(MID(A1,8,2)))=FALSE,VALUE(MID(A1,8,2)),
IF(ISERROR(VALUE(MID(A1,9,2)))=FALSE,VALUE(MID(A1,9,2)),
IF(ISERROR(VALUE(MID(A1,10,2)))=FALSE,VALUE(MID(A1,10,2)),
IF(ISERROR(VALUE(MID(A1,11,2)))=FALSE,VALUE(MID(A1,11,2)),
IF(ISERROR(VALUE(MID(A1,12,2)))=FALSE,VALUE(MID(A1,12,2)),
IF(ISERROR(VALUE(MID(A1,13,2)))=FALSE,VALUE(MID(A1,13,2)),
IF(ISERROR(VALUE(MID(A1,14,2)))=FALSE,VALUE(MID(A1,14,2)),
IF(ISERROR(VALUE(MID(A1,15,2)))=FALSE,VALUE(MID(A1,15,2)),0)))))))))))))))
The samples below show the correct screen sizes are colored in BOLD BLUE.
E500IB13
E500IB13
UN65F6350AFXZA
M43C1
P7N9L703B
E500IB14
E40C2
EA6G500IB13
U8N75H6300AFXZA
E400IB14
E700IB14
UN65H7100AFXZA
THE QUESTION: The formula works; however, can it be simplified/shortened using some other Excel function(s)?
=IF(ISERROR(VALUE(MID(A1,1,2)))=FALSE,VALUE(MID(A1,1,2)),
IF(ISERROR(VALUE(MID(A1,2,2)))=FALSE,VALUE(MID(A1,2,2)),
IF(ISERROR(VALUE(MID(A1,3,2)))=FALSE,VALUE(MID(A1,3,2)),
IF(ISERROR(VALUE(MID(A1,4,2)))=FALSE,VALUE(MID(A1,4,2)),
IF(ISERROR(VALUE(MID(A1,5,2)))=FALSE,VALUE(MID(A1,5,2)),
IF(ISERROR(VALUE(MID(A1,6,2)))=FALSE,VALUE(MID(A1,6,2)),
IF(ISERROR(VALUE(MID(A1,7,2)))=FALSE,VALUE(MID(A1,7,2)),
IF(ISERROR(VALUE(MID(A1,8,2)))=FALSE,VALUE(MID(A1,8,2)),
IF(ISERROR(VALUE(MID(A1,9,2)))=FALSE,VALUE(MID(A1,9,2)),
IF(ISERROR(VALUE(MID(A1,10,2)))=FALSE,VALUE(MID(A1,10,2)),
IF(ISERROR(VALUE(MID(A1,11,2)))=FALSE,VALUE(MID(A1,11,2)),
IF(ISERROR(VALUE(MID(A1,12,2)))=FALSE,VALUE(MID(A1,12,2)),
IF(ISERROR(VALUE(MID(A1,13,2)))=FALSE,VALUE(MID(A1,13,2)),
IF(ISERROR(VALUE(MID(A1,14,2)))=FALSE,VALUE(MID(A1,14,2)),
IF(ISERROR(VALUE(MID(A1,15,2)))=FALSE,VALUE(MID(A1,15,2)),0)))))))))))))))
The samples below show the correct screen sizes are colored in BOLD BLUE.
E500IB13
E500IB13
UN65F6350AFXZA
M43C1
P7N9L703B
E500IB14
E40C2
EA6G500IB13
U8N75H6300AFXZA
E400IB14
E700IB14
UN65H7100AFXZA
THE QUESTION: The formula works; however, can it be simplified/shortened using some other Excel function(s)?