Not really a question . . . . . . . . . .

pgc01 · Mar 5, 2008

Hi all

This is my craziest formula and one of the craziest I've seen. The equivalent vba is just a couple of statements!

The formula extracts digits out of a string. Only works in xl2007 as some functions are deeply nested. Uses an auxilliary formula or else it would be unmanageable.

Extracts up to 30 digits.

I'm sure there must be a simpler solution (like, for ex., any other solution) but it was fun to build.

<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >E</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, 610</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">01123581321345589144233377610</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">35</td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">0 Mary 123-456 had a 7890 987.654 little 321 lamb 0</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">012345678909876543210</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">31</td></tr><tr><td colspan=6 style="background:#9CF; padding-left:1em" > [ExtractDigits.xlsb]Sol xl2007</td></tr></table> 
 
<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; " >Addr</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; " >Formula</td></tr><tr><td colspan=2 style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; " >[ExtractDigits.xlsb]Sol xl2007</td></tr><tr><td rowspan=2 style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center; " >C1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em; " > =TEXT(SUM(IF(ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&E1)),1)),MID(A1,ROW(INDIRECT("1:"&E1)),1)*10^(10*E1-SUMPRODUCT(LEN(SUBSTITUTE(LEFT(A1,E1),ROW(INDIRECT("1:10"))-1,"")))-10*ROW(INDIRECT("1:"&E1))+MMULT(LEN(SUBSTITUTE(LEFT(A1,ROW(INDIRECT("1:"&E1))),TRANSPOSE(ROW(INDIRECT("1:10"))-1),"")),--(ROW(INDIRECT("1:10"))>0))))),REPT("0",10*E1-SUMPRODUCT(LEN(SUBSTITUTE(LEFT(A1,E1),ROW(INDIRECT("1:10"))-1,"")))))&(IF(LEN(A1)=E1,"",TEXT(SUM(IF(ISNUMBER(-MID(RIGHT(A1,LEN(A1)-E1),ROW(INDIRECT("1:"&LEN(A1)-E1)),1)),MID(RIGHT(A1,LEN(A1)-E1),ROW(INDIRECT("1:"&LEN(A1)-E1)),1)*10^(10*(LEN(A1)-E1)-SUMPRODUCT(LEN(SUBSTITUTE(RIGHT(A1,LEN(A1)-E1),ROW(INDIRECT("1:10"))-1,"")))-10*ROW(INDIRECT("1:"&LEN(A1)-E1))+MMULT(LEN(SUBSTITUTE(LEFT(RIGHT(A1,LEN(A1)-E1),ROW(INDIRECT("1:"&LEN(A1)-E1))),TRANSPOSE(ROW(INDIRECT("1:10"))-1),"")),--(ROW(INDIRECT("1:10"))>0))))),REPT("0",10*(LEN(A1)-E1)-SUMPRODUCT(LEN(SUBSTITUTE(RIGHT(A1,LEN(A1)-E1),ROW(INDIRECT("1:10"))-1,""))))))) </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;; " > This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.</td></tr><tr><td rowspan=2 style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center; " >E1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em; " > =IF(MAX(10*ROW(INDIRECT("1:"&LEN(A1)))-MMULT(LEN(SUBSTITUTE(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),TRANSPOSE(ROW(INDIRECT("1:10"))-1),"")),--(ROW(INDIRECT("1:10"))>0)))<=15,LEN(A1),MATCH(15,10*ROW(INDIRECT("1:"&LEN(A1)))-MMULT(LEN(SUBSTITUTE(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),TRANSPOSE(ROW(INDIRECT("1:10"))-1),"")),--(ROW(INDIRECT("1:10"))>0)),0)) </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;; " > This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.</td></tr></table>

Richard Schollar · Mar 6, 2008

It seems there isn't a 'vomiting smilie' which is what I think I need after trying to decipher that formula, Pedro

You crazy nutter!

pgc01 · Mar 6, 2008

I'm very disappointed Richard. Why don't you like my beautiful formula?

At least I hope you meant

and not

Domski · Mar 6, 2008

pgc01 said:
I'm very disappointed Richard. Why don't you like my beautiful formula?

At least I hope you meant

and not

It's like an Excel version of Joseph and His Amazing Technicolour Dreamcoat. Although thinking about it that was pretty vomit inducing too!

Oaktree · Mar 6, 2008

No morefunc add-in, pgc?

C1 {=SUBSTITUTE(MCONCAT(IF(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),""),"FALSE","")}

pgc01 · Mar 6, 2008

Oaktree said:
No morefunc add-in, pgc?

C1 {=SUBSTITUTE(MCONCAT(IF(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),""),"FALSE","")}

Hi Oaktree

No. Just excel native functions, no vba, yours or someone else's (add-in).

The problem with this type of formulas is exactly that the worksheet function Concatenate() does not accept a range or an array.

This could be done in vba in a couple of statements, but then it would be no fun.

I would not use this formula in real life but I thought it fits this thread, as it's about big and crazy formulas.

arkusM · Mar 11, 2008

=IF(OR(BG$9=0,$Z135>BG$6,$AA135<BG$6),0,IF($AD135=1,($W135-BG$8)*($U135*BG$10)*BG$11,IF($AD135=2,($W135-BG$9)*$U135*BG$11,IF($AD135=3,ROUND(IF(AND(BG$9>$Y135,$Y135>0),($Y135-BG$9)*$U135*BG$11,IF(AND(BG$9<$X135,$X135>0),($X135-BG$9)*$U135*BG$11)),2),0))))
This one is used in an internal hedge calulation, it is looking to see if it should calc the hegde position.

=IF(ISERROR(INDEX('Production Information'!$F$3:$T$15000,SMALL(IF('Production Information'!$F$3:$F$15000=($C$5&$D$5&$E17),ROW('Production Information'!$F$3:$F$15000)-ROW('Production Information'!$F$3)+1,ROW('Production Information'!$F$15000)+1),V$6),V$7)),0,INDEX('Production Information'!$F$3:$T$15000,SMALL(IF('Production Information'!$F$3:$F$15000=($C$5&$D$5&$E17),ROW('Production Information'!$F$3:$F$15000)-ROW('Production Information'!$F$3)+1,ROW('Production Information'!$F$15000)+1),V$6),V$7))

This one was made bfore Aladin enlightened me with the

Code:

 =lookup(9.9999e+307,Choose({1,2},0,Formula))

This beast goes into a data dump and pulls out specific bits of information and I'm not sure I can remember how it even works anymore, but it works. Takes about 45 seconds to run....

One more...

=ROUND(IF(AND($BK97>=I$4,$BL97>=I$5,$BK97<I$5),I$5-$BK97+1,IF(AND($BL97>I$4,$BK97<=I$4,I$5>=$BL97),$BL97-I$4+1,IF(AND(($BK97+1)>I$4,$BL97>I$4,$BL97<I$5),$BL97-$BK97+1,IF(OR(AND($BK97<I$4,$BL97>I$5),AND($BK97=I$4,$BL97=I$5)),I$5-I$4+1,0))))/I$3*$BI97,1)

This one was difficult to get to work. Mostly because it had to verify if a date was before the first of the month, or started in the month, was after the end of the month, and then calculate appropriately. Because of the stuff I have learned on the board I probably could do this more efficiently.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>

deadlyduck · Apr 29, 2008

Code:

=IF('Main Data Entry Sheet'!K157=1,SUM('Main Data Entry Sheet'!K156*'Main Data Entry Sheet'!K157,IF(AND('Main Data Entry Sheet'!K157=1,MID('Case I comp'!B263,1,1)="R"),'Case I comp'!B260*-1,0),'Main Data Entry Sheet'!K158*'Main Data Entry Sheet'!K159,IF(AND('Main Data Entry Sheet'!K159=1,MID('Case I comp'!C263,1,1)="R"),'Case I comp'!C260*-1,0)),SUM('Main Data Entry Sheet'!K156*'Main Data Entry Sheet'!K157,'Main Data Entry Sheet'!K158*'Main Data Entry Sheet'!K159))*'Main Data Entry Sheet'!B155

This formula computed the tax allowable value of Plant & machinery purchased during an accounting period under current Irish Corporation tax legislation. I had to allow for factors such as :

1. date of purchase/ use;
2. whether a maximum allowed cost was exceeded;
3. whether the company had a 'replacement option' available to it (to defer a tax charge arising on the sale of an old asset).

The data in the Excel file was used to output (via mailmerge) both an exam question and solution in minutes rather than hours! It's very clunky (I was unaware at the time of the SUMPRODUCT function). As a medium term project I hope to improve the file but am feeling a little overwhelmed at the prospect!

Long Nose · Oct 3, 2008

That Variant is way cool. I'd not seen it either. I'm in the, "I'm not worthy" category amongst the men of greatness here, but the one I'm still amazing over lately is this answer from Aladin.

You could create on every relevant sheet an additional column, say, G, with"

=MONTH(A2)

Then invoke:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!G2:G188"),MONTH($A6),INDIRECT("'"&SheetList&"'!F2:F188")))

where SheetList refers to a range housing the relevant sheet names, that is, 3107, 3207, etc.

Patience · Oct 7, 2008

One I was recently proud of:

=IF(AND(MATCH(Data!K2152,Props!$F$1:$F$211,0)<>11,
MATCH(Data!K2152,Props!$F$1:$F$211,0)<>12,MATCH(Data!K2152,Props!$F$1:$F$211,0)
<>14),CONCATENATE(K2152,RIGHT(Props!$K$26,8)),IF
(MATCH(Data!K2152,Props!$F$1:$F$211,0)=11,VLOOKUP(IF(ISNA(RIGHT(G2152,2)),
VALUE(RIGHT(G2152,2)),RIGHT(G2152,2)),Props!$J$2:$K$16,2,0),IF(
MATCH(Data!K2152,Props!$F$1:$F$211,0)=12,VLOOKUP(IF(ISNA(RIGHT(G2152,2)),
VALUE(RIGHT(G2152,2)),RIGHT(G2152,2)),Props!$J$29:$K$39,2,0),VLOOKUP(IF(ISNA(
RIGHT(G2152,2)),VALUE(RIGHT(G2152,2)),RIGHT(G2152,2)),Props!$J$19:$K$23,2,0))))

Quite straightforward really.

Not really a question . . . . . . . . . .

pgc01

MrExcel MVP

Excel Facts

Richard Schollar

MrExcel MVP

pgc01

MrExcel MVP

Domski

Well-known Member

Oaktree

MrExcel MVP

pgc01

MrExcel MVP

arkusM

Well-known Member

deadlyduck

New Member

Long Nose

Board Regular

Patience

Active Member

Forum statistics

Share this page

Not really a question . . . . . . . . . .

MrExcel MVP

Excel Facts

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

MrExcel MVP

Well-known Member

New Member

Board Regular

Active Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock