Apr 29 2022 02:23 AM
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.
Apr 29 2022 09:51 AM
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))
Apr 29 2022 11:44 AM
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.
May 07 2022 03:41 AM
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.