Forum Discussion
Formulas
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))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.
I need answer with add any Macro PeterBartholomew1
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. HansVogelaar 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.
