+ Reply to Thread
Results 1 to 8 of 8

Need Help function has more than 64 levels of nesting

  1. #1
    Registered User
    Join Date
    02-27-2020
    Location
    Jakarta
    MS-Off Ver
    2016
    Posts
    4

    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.
    Attached Files Attached Files
    Last edited by phantomflares; 02-27-2020 at 06:38 AM.

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,380

    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
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Registered User
    Join Date
    02-27-2020
    Location
    Jakarta
    MS-Off Ver
    2016
    Posts
    4

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

    Quote Originally Posted by samba_ravi View Post
    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. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,380

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

    what is your expected result for that and why

  5. #5
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    1,815

    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. #6
    Registered User
    Join Date
    02-27-2020
    Location
    Jakarta
    MS-Off Ver
    2016
    Posts
    4

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

    Quote Originally Posted by samba_ravi View Post
    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. #7
    Registered User
    Join Date
    02-27-2020
    Location
    Jakarta
    MS-Off Ver
    2016
    Posts
    4

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

    Quote Originally Posted by davsth View Post
    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. #8
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    1,815

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

    obviously you need to add add!
    =(IF(OR(LEFT(A2,3)="add",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))

    It was ravi who did the work really!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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