# Need Help function has more than 64 levels of nesting

1. ## Need Help function has more than 64 levels of nesting

Hello guys,

I need help, i am creating a formula in excel using some functions. I am sure there is an easier way of doing this but I have no idea how. I just got through creating the formula but I am getting an error message that indicates I have exceeding the 64 levels of nesting.

i want to extract some number on some string of text, but I end up having some problem, i attached a spreadsheet here to make you guys understand what i want.

Any suggestions as to whether or not there is an easier way to put this together would be appreciated.

2. ## Re: Need Help function has more than 64 levels of nesting

b2=LOOKUP(99^9,MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})+0)
Try the above copy and paste towards down

3. ## Re: Need Help function has more than 64 levels of nesting

Originally Posted by samba_ravi
b2=LOOKUP(99^9,MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})+0)
Try the above copy and paste towards down

thank you for the quick reply

wow, the formula you are using sounds very foreign to me. I need time to digest and understand the purpose of the formula thou...

i already tried it and it works wonder, but how about result on row 25 to 28 to match my desired result?

thank you so much for your help, really really apreciate it

4. ## Re: Need Help function has more than 64 levels of nesting

what is your expected result for that and why

5. ## Re: Need Help function has more than 64 levels of nesting

If you must, although the answers are all text, perhaps
=(IF(OR(LEFT(A2,3)="cor",LEFT(A2,3)="Ded"),"correction of ","")&LOOKUP(99^9,MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})+0))

6. ## Re: Need Help function has more than 64 levels of nesting

Originally Posted by samba_ravi
what is your expected result for that and why

in addition to looking for numbers in a string of text, if the cell contains the words "correction", "cor", "deduction", "deduct", "addition", "add", then the result that appears is "correction of ......."

for example on cell A25 there are "Correction of 00122/TU/02/2019", so instead of 122 as result, i'd like to get "Correction of 122" as result..
and another example on cell A27 there are "Deduct 2240/LS/01/2019", so instead of 2240 as result, i'd like to get "Correction of 2240" as result..

is it possible to do that?

thank you so much

7. ## Re: Need Help function has more than 64 levels of nesting

Originally Posted by davsth
If you must, although the answers are all text, perhaps
=(IF(OR(LEFT(A2,3)="cor",LEFT(A2,3)="Ded"),"correction of ","")&LOOKUP(99^9,MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})+0))
whoaaa.... it works.. thank you so much, i didnt mind it beeing all text since i can change it to number later..

thank you so much samba_ravi and davsth for helping me out, been thinking about this all day long and you guys just solve my problem in a blink of eye.. you guys rock, thank youuu

8. ## Re: Need Help function has more than 64 levels of nesting

It was ravi who did the work really!

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1