• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can the (currently working) formula be simplified?

Eloise T

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)?
 
Hi,

Assume your data starts from cell A1 then put the below in B1

MID($A1,MIN(IFERROR(FIND(ROW($10:$99),$A1),"")),2)

To be acknowledged with CTRL + SHIFT + ENTER

Sample file for ref
 

Attachments

  • Chandoo_Whatever.xlsx
    9.7 KB · Views: 11
Asheesh said:
Hi,

Assume your data starts from cell A1 then put the below in B1

MID($A1,MIN(IFERROR(FIND(ROW($10:$99),$A1),"")),2)

To be acknowledged with CTRL + SHIFT + ENTER

Sample file for ref
Click to expand...
You sir, are a true Excel Ninja! Thanks!
 
Asheesh said:
Hi,

Assume your data starts from cell A1 then put the below in B1

MID($A1,MIN(IFERROR(FIND(ROW($10:$99),$A1),"")),2)

To be acknowledged with CTRL + SHIFT + ENTER

Sample file for ref
Click to expand...

To understand what you did, I looked up the following...Do I have it correct?

ROW function gives a range of possible TV Model sizes, i.e. 10-99. Would using the COLUMN function have done the same thing? COLUMN($10:$99)

FIND command searches, in this case, cell A1 for one of the possibilities in the range given by the ROW command, i.e. 10-99. NOTE: 2 digits only

IFERROR command displays "blank" if a match is not found with the FIND function...which should never happen here?

MIN command displays the smallest value of the numbers provided.

MID command extracts from, in this case, cell $A1, a substring from that string (starting at position 1) for a length of 2.

QUESTION: How does the formula figure out to grab the first two consecutive numbers, assuming there is more than just one pair of consecutive numbers? Would that be the MID command? Once it finds the match, it quits looking?
 
Last edited:
Thank you for being so kind with your words...you are right to an extent with your explanation..but not exactly...

Shall write a detailed note by saturday..

meanwhile, why don't you try to use the evaluate functionality of excel..Shortcut ALT + T U F

Also Replace ROW function with COLUMN function..and see what happens..
 
Asheesh said:
Thank you for being so kind with your words...you are right to an extent with your explanation..but not exactly...

Shall write a detailed note by Saturday.
Click to expand...
Thank you. I will look forward to seeing it.

Asheesh said:
meanwhile, why don't you try to use the evaluate functionality of excel..Shortcut ALT + T U F
Click to expand...
OK.

Asheesh said:
Also Replace ROW function with COLUMN function..and see what happens.
Click to expand...
When I replaced COLUMN with ROW, it only worked 75% of the time, so for some reason it's not accurate/consistent. I don't understand why as I thought I understood the reasoning behind the ROW formula you created and figured COLUMN would give the same 10-99 results. {=ROW($10:$99)} always returns 10, {=COLUMN($10:$99)} always returns 1.

Mod edit: plz quote text correctly.
 
Last edited by a moderator:
Asheesh said:
Hi,

Assume your data starts from cell A1 then put the below in B1

MID($A1,MIN(IFERROR(FIND(ROW($10:$99),$A1),"")),2)

To be acknowledged with CTRL + SHIFT + ENTER

Sample file for ref
Click to expand...
Please take a look at the uploaded file. It contains the "Old" formula and your "New" formula, and using a simple IF formula, compares the two results. They are yielding the same results but the IF statement doesn't think so. It' very odd. Thanks in advance for your assistance.
 

Attachments

  • Chandoo Asheesh.xlsx
    10.9 KB · Views: 5
Hi:

Your if statement is wrong, it should be
Code: 
=IF((M2-N2)=0,"True","False")
not
Code: 
=IF(M2-N2,"True","False")

Thanks
 
Whatever said:
Please take a look at the uploaded file. It contains the "Old" formula and your "New" formula, and using a simple IF formula, compares the two results. They are yielding the same results but the IF statement doesn't think so. It' very odd. Thanks in advance for your assistance.
Click to expand...

Hi,
Result for the new formula is in Text.

add 2 hyphen after = sign, it will be converted in numeric values.

=--MID($L2,MIN(IFERROR(FIND(ROW($11:$100),L2),"")),2)

or
=MID($L2,MIN(IFERROR(FIND(ROW($11:$100),L2),"")),2)+0

Now use this in column P :
=M2=N2

Regards,
 
Khalid NGO said:
Hi,
Result for the new formula is in Text.

add 2 hyphen after = sign, it will be converted in numeric values.

=--MID($L2,MIN(IFERROR(FIND(ROW($11:$100),L2),"")),2)

or
=MID($L2,MIN(IFERROR(FIND(ROW($11:$100),L2),"")),2)+0

Now use this in column P :
=M2=N2

Regards,
Click to expand...

Just when I think I've learned all there is to the submicroscopic part of Excel that I utilize, another unknown (to me) nuance butts in and rears its ugly head.

I've never seen: =--MID or ),"")),2)+0
but apparently that forces "it" out of the text mode and into numeric mode?
How does it get into text mode in the first place and how does one know what to look for to realize it's in text mode?

THANK YOU for your assistance.
 
Mate, MID is a text function so it returns text...I personally do not use much of -- or + 0 because it confuses a lot of guys around...there is an inbuilt function in Excel i.e. N()..you could wrap the entire construction in..and would do the same job..say something like N(Mid(...................)..
Now to answer your question - since MID returns text, so it even reads numbers as text, now if you wish to compare a number with a text it definitely will not match..using the above tricks convert the text values(in this case are numbers) to numbers..however, if we apply the same trick to alphanumeric string or just some text it would return #VALUE error..

So it completely depends on the requirement but one must stay awake while using these little tricks..and that is something we all learn with practice :)

Hope this clarifies...
 
Asheesh said:
there is an inbuilt function in Excel i.e. N()..you could wrap the entire construction in..and would do the same job..say something like N(Mid(...................)..
Click to expand...

Hi Asheesh,
You mean VALUE() instead of N() ?
N() doesn't seems to be working here.

Regards,
 
Whatever said:
how does one know what to look for to realize it's in text mode?
Click to expand...
Hi,

Usually I check the result (whether it is in text mode or number) with short key like Ctrl+Shift+1 (2 decimals + thousands separator)
If the key doesn't work that means the value is in text mode. (as the text values can not be formatted)

some more ways to check:
=ISTEXT(N2)
=ISNUMBER(N2)

Regards,
 
Asheesh said:
My bad @Khalid NGO thank you for the correction

Yes in this case N() would return 0 and Value() would return the number..
Click to expand...
THANKS TO BOTH Asheesh and Kahlid re: VALUE(). That makes perfect sense!
Khalid NGO said:
Hi,

Usually I check the result (whether it is in text mode or number) with short key like Ctrl+Shift+1 (2 decimals + thousands separator)
If the key doesn't work that means the value is in text mode. (as the text values can not be formatted)

some more ways to check:
=ISTEXT(N2)
=ISNUMBER(N2)
Click to expand...
QUESTION: TV's are now over 99" although this has not affected my spreadsheets yet, but it's only a matter of time. How do I adjust the formula to allow for up to 120" screens?

=--MID($L2,MIN(IFERROR(FIND(ROW($10:$99),L2),"")),2)
The "2" at the end restricts the string to only two digits, but also the $10:$99 limits the range as well. I tried changing the range to $10:$120 and the ,2) to a three...,3) but didn't get the correct results in all circumstances.
 
Nice question - gets little tricky now...can you share the sample data, i would like to see for some patterns..
 
Asheesh said:
Nice question - gets little tricky now...can you share the sample data, i would like to see for some patterns..
Click to expand...

I only have three samples (RS120-B3 PRJSF1009 ER110WH1); however, the format is likely to be the same as it is now. Currently it is the 1st two consecutive numbers; so for screen sizes over 99, it (based on the three samples above) should be the first three consecutive numbers. As I ponder that, it doesn't seem feasible, especially when I plug the data into the attached spreadsheet. Maybe if the formula checks to see if the size is greater than the current manufactured size (now 120"), it defaults to the 2-digit sizes?

In any case, the approximate costs: 70" for $1200, $4000 for 80", 90" is $8000, 98" is $40,000, 110" is $80,000 and the 120" is a whooping $130,000! ...so I don't think too many people will be buying over 80" for a while.

I also wanted to remind you to clarify how your formula works:

ROW function gives a range of possible TV Model sizes, i.e. 10-99. Why doesn't the COLUMN function do the same thing? COLUMN($10:$99)
FIND command searches, in this case, cell A1 for one of the possibilities in the range given by the ROW command, i.e. 10-99. NOTE: 2 digits only
IFERROR command displays "blank" if a match is not found with the FIND function...which should never happen here?
MIN command displays the smallest value of the numbers provided which in this case is the starting point of the 2 consecutive numbers in the alphanumeric string.
MID command extracts from, in this case, cell $A1, a text substring from that text string (starting at position 1) for a length of 2.
"--" converts text to numeric.

THANK YOU!
 

Attachments

  • Chandoo Asheesh.xlsx
    11.4 KB · Views: 6
Last edited:
Hi Mate, Since I was little held up today..so couldn't post it earroly..however go through the explanation as requested...

E500IB13

Let us use the above example to accomplish the task.

MID function returns the characters from the text string basis the starting position and length of characters.

Let’s look at its arguments:

MID(text,start_num,num_chars)

So there are three arguments

  1. text: What is my input string(reference or hardcoded) in this case, it is

$A1


2. start_num: position in the string, i shall begin extracting from i.e. starting position. In this case

MIN(IFERROR(FIND(ROW($10:$99),A1),""))


3. num_chars: number of characters i wish to retrieve from the string, in this case

2

We shall now first try to understand the 2nd argument i.e start_num.


MIN(IFERROR(FIND(ROW($10:$99),A1),""))

Once evaluated it translates to

MID($A1,MIN(IFERROR(FIND({10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;91;92;93;94;95;96;97;98;99},A1),"")),2)

Now, ROW($10:$99) expands and gives us all the possible TV size’s ranging from 10 to 99(2 digits only)

Then, seeking help of FIND function to locate the position of all the 2 digit combinations in string.i.e. A1.

Once evaluated further, it return

MID($A1,MIN(IFERROR({#VALUE!;#VALUE!;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;2 #VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!},"")),2)

In the above return of array, we notice that we have two integer values i.e. 7 & 2. These 2 integer values are the positions of the 2 digit combinations available in the string.

In simple words, number 50 is available at position 2 and number 13 is available at position 7.( the position from the character 1 in the string)

Since, MIN function ignores the text values but not the error values. So if we remove IFERROR function here, it would return #VALUE! Error. however, as I say it ignores the text values and does its job efficiently. Therefore, I wrap the FIND function in IFERROR, where I replace #VALUE! Errors with (“ “) and excel recognizes blank as text value.

Once evaluated it looks like

MID($A1,MIN({"";"";"";7;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";2}),2)

So upon further evaluation, MIN function returns 2. As you mentioned in your query, that the first 2 consecutive digits are the TV sizes. Thus, MIN returns the first position of the 2 consecutive digits.

Making our construction look like

MID($A1,2,2)

Finally, MID now extracts the 2 digits from A1 cell from 2 as starting postion.

And yes, I could use the COLUMN function but I needed to limit my search.

COLUMN($J$1:$CU$1)

I hope it helps..
 
You must log in or register to reply here.
Back
Top