Formulas

[bakarpk]

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.

[#2]

@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))

[#3]

@Hans Vogelaar 

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.

[#4]

I need answer with add any Macro @Peter Bartholomew 

[#5]

@bakarpk 

All of the solutions you have been offered are worksheet formulas. Mine are aimed at 365 insider beta because that is the only version of Excel that interests me. @Hans Vogelaar provided a solution for Excel 2021 and the general release version of 365.  However he also gave a solution that should work with legacy versions of Excel.  I suggest you try his formula.