Forum Discussion

bakarpk's avatar
bakarpk
Copper Contributor
Apr 29, 2022

Formulas

3.5% Above on all T.S Rates
4% Above on all T.S Rates
Add 2.75% T.S Cost
All items 1.5% Above on T.S Estimate

 

=SUMPRODUCT(MID(0&D6, LARGE(INDEX(ISNUMBER(--MID(D6, ROW(INDIRECT("1:"&LEN(D6))), 1)) * ROW(INDIRECT("1:"&LEN(D6))), 0), ROW(INDIRECT("1:"&LEN(D6))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(D6)))/10)

 

its just result only 4, 2 1 I want 2.75 and 1.5

 


I want to get only number from this lines in MS Excel With Formula.

excel

4 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 29, 2022

    bakarpk 

    If you have Microsoft 365 or Office 2021:

    =LET(
    p,FIND("%",D6),
    l,LEFT(D6,p-1),
    s,SUBSTITUTE(l," ",REPT(" ",255)),
    r,RIGHT(s,255),
    t,TRIM(r),
    --t
    )

    If you have an older version:

    =--TRIM(RIGHT(SUBSTITUTE(LEFT(D6,FIND("%",D6)-1)," ",REPT(" ",255)),255))
    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Apr 29, 2022

      HansVogelaar 

      Thanks for your first solution.  It at least allowed me to reverse engineer the problem!  Just to continue the 'softening up' process of exposing new methods to an audience that is not necessarily convinced of the need for change, here is a 365 insider beta version:

      = LET(
    subStr,  TEXTSPLIT(@string," "),
    percent, XLOOKUP("*%",subStr,subStr,"",2),
    100*percent
  )

      working row by row, or as a dynamic range problem:

      = MAP(string,
    LAMBDA(str,
      LET(
        subStr,  TEXTSPLIT(str," "),
        percent, XLOOKUP("*%",subStr, subStr,"",2),
        100*percent
      )
    )
  )

      The key is that the formula exploits the coming TEXTSPLIT function to create an array of sub-strings.  XLOOKUP then returns the substring ending in "%" by using a wildcard search.  Multiplying the percentage by 100 coerces the conversion to a number (typically in in the range 0 to 1) and then factors up by 100.

Resources