Can some explain this formula. I know what it does, I just want to understand why

[Jarodjp42]

=LOOKUP(10^9,--MID(J43,MIN(FIND({0,1,2,3,4,5,6,7,8,9},J43&"0123456789")),{1,2,3,4,5,6,7,8}))

Here is the formula, I am confused about the 10^9 and the -- that are listed in the formula. Can someone break it down and explain the meaning behind the inputs please?
Thank you!!!

[TABLE="width: 224"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 3"]Homes for 165.99 in town[/TD]
[/TR]
[TR]
[TD="colspan: 3"]nco financial Group Inc 77.43[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Department, Inc. 113.60 ok[/TD]
[/TR]
[TR]
[TD="colspan: 3"]ncetonOne207.49San jose[/TD]
[/TR]
[TR]
[TD="colspan: 3"]d M655.72ellon hop[e[/TD]
[/TR]
[TR]
[TD="colspan: 3"]ve Boro Realty corp 97.77[/TD]
[/TR]
[TR]
[TD="colspan: 3"]ton's449.78 floats [/TD]
[/TR]
[TR]
[TD="colspan: 3"]46.27 road[/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Breaking it down using your first text string of:

Homes for 165.99 in town

The MID function needs the following MID(text,start number, num_Char)

This part of the formula [TABLE="width: 255"]
<colgroup><col width="255"></colgroup><tbody>[TR]
[TD="width: 255"]J43&"0123456789"[/TD]
[/TR]
</tbody>[/TABLE]
returns:
[TABLE="width: 255"]
<colgroup><col width="255"></colgroup><tbody>[TR]
[TD="width: 255"]Homes for 165.99 in town0123456789

Then the:
[TABLE="width: 514"]
<colgroup><col width="514"></colgroup><tbody>[TR]
[TD="width: 514"]FIND({0,1,2,3,4,5,6,7,8,9},J43&"0123456789")
returns the position of the numbers in the string:
[TABLE="width: 514"]
<colgroup><col width="514"></colgroup><tbody>[TR]
[TD="width: 514"]{25,11,27,28,29,13,12,32,33,15}[/TD]
[/TR]
</tbody>[/TABLE]

TheMIN

<colgroup><col width="514"></colgroup><tbody>
[TD="width: 514"]MIN(FIND({0,1,2,3,4,5,6,7,8,9},J43&"0123456789")):
returns the 11 from the above array which gives the stating number for the MID function.

<colgroup><col width="514"></colgroup><tbody>
[TD="width: 514"]MID(J43,MIN(FIND({0,1,2,3,4,5,6,7,8,9},J43&"0123456789")),{1,2,3,4,5,6,7,8})
This part will return an array of the num_Char that the MID needs or:

<colgroup><col width="656"></colgroup><tbody>
[TD="width: 656"]{"1","16","165","165.","165.9","165.99","165.99 ","165.99 i"}[/TD]

</tbody>

The double negative (--) will turn the above teat strings into values:
[TABLE="width: 656"]
<colgroup><col width="656"></colgroup><tbody>[TR]
[TD="width: 656"]{1,16,165,165,165.9,165.99,165.99,#VALUE!}[/TD]

</tbody>

The 10^9 is what is often referred to as a BIG NUMBER. It is a number that will be bigger than anything you would find in your lookup array, can be any large number (200 would have worked in this example).

Since LOOKUP will never find this BIG NUMBER it will return the biggest number in the array which in this example is 165.99.



[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]


[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jarodjp42]

@AhoyNC Thank you. This is a perfect example and allows me to fully understand every function in the formula. You're the best!

 

[AhoyNC]

You're welcome. Glad that helped.

 

[Jarodjp42]

@AhoyNC Could you explain why the formula would need to have J43&"0123456789" in the FIND function. I understand the cell reference, but don't understand why the function needs to have &"0123456789" for it to work. I would really appreciate the help.

 

[barry houdini]

Try taking out &"0123456789", you'll probably get an #N/A error. FIND function is trying to find each of the 10 digits, to ensure there is no error you can concatenate "0123456789" to the lookup cell. An alternative is to use IFERROR like this:

=LOOKUP(10^9,--MID(J43,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},J43),"")),{1,2,3,4,5,6,7,8}))

 

[Aladin Akyurek]

Try taking out &"0123456789", you'll probably get an #N/A error. FIND function is trying to find each of the 10 digits, to ensure there is no error you can concatenate "0123456789" to the lookup cell. An alternative is to use IFERROR like this:

=LOOKUP(10^9,--MID(J43,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},J43),"")),{1,2,3,4,5,6,7,8}))

I would not recommend that all. At the time I designed the core formula iferror was not available (lucky me). By the way, I would never use anything like 10^9.

 

[Jarodjp42]

I would not recommend that all. At the time I designed the core formula iferror was not available (lucky me). By the way, I would never use anything like 10^9.

Why would you never use anything like 10^9?? If using big data and copying the formula down it will allow me to never have to edit the formula because no number will be bigger than that.
Also, IFERROR function works perfectly. Your explanation helped me understand why that was in there and a work around with the IFERROR function. Thanks @BarryHoudini

 

[Aladin Akyurek]

Why would you never use anything like 10^9?? If using big data and copying the formula down it will allow me to never have to edit the formula because no number will be bigger than that.

The big number I'd use is one of the Excel itself, That is:

9.99999999999999E+307

So the formula you have becomes:

=LOOKUP(9.99999999999999E+307,--MID(J43,MIN(FIND({0,1,2,3,4,5,6,7,8,9},J43&"0123456789")),{1,2,3,4,5,6,7,8}))

See: https://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html

Also, IFERROR function works perfectly. Your explanation helped me understand why that was in there and a work around with the IFERROR function. Thanks @BarryHoudini

IFERROR is inefficient/costly. So what the original author (i.e. me) avoids the error condition at almost no cost.

https://www.mrexcel.com/forum/excel-questions/9830-separate-text-numbers-string-3.html#post2300995

 

[Jarodjp42]

Thank you for your response @ Aladin Akyurek. I'm curious into why you avoid using the IFERROR function at all costs. I feel like it could be useful in certain scenarios, but then again I'm not an expert of excel. If you could share your thoughts I would love to learn more about it.