Jul 12 2021 05:16 AM
Hello Everyone,
if there are numbers in a column like [1, 2, 3, 23, 43, 54, 34 +1, 45 - 1, 42 *2 , 34, 98] how to make this a list of whole numbers or how do we calculate in between if there is a large dataset?
Please help..???
Here is a attach file..
Jul 12 2021 05:25 AM
See the attached version (now a macro-enabled workbook since it contains a custom VBA function).
Jul 12 2021 05:31 AM
Jul 12 2021 05:39 AM
SolutionIt would be very complicated without VBA.
Jul 12 2021 05:45 AM
Exactly the same if you define in name manager named function as
but the file also shall be saved as macro-enabled workbook.
Jul 12 2021 06:16 AM
As another variant formula could be
=LET(str, A1,
op, {"+","-","~*","/"},
r, MMULT(COUNTIFS(str,"*"&op&"*"),{1;2;3;4}),
pos, SUM(IFERROR(SEARCH(op,str),0)),
lft, LEFT(str, pos-1),
rht, RIGHT(str, LEN(str)-pos),
IF(r=0, str, CHOOSE(r, lft+rht, lft-rht, lft*rht, lft/rht))
)
Jul 12 2021 06:26 AM
Jul 12 2021 06:32 AM
It works only if you are on Excel 365. If you are not sure you may check opening the file attached to previous post. lft, rht, etc are names for the formulas we use inside LET function - Office Support (microsoft.com)
Jul 12 2021 06:49 AM
Jul 12 2021 10:03 AM
That could be
=IF(ISNUMBER(SEARCH("+",A1)),
LEFT(A1,SEARCH("+",A1)-1) + RIGHT(A1,LEN(A1)-SEARCH("+",A1)),
IF(ISNUMBER(SEARCH("-",A1)),
LEFT(A1,SEARCH("-",A1)-1) - RIGHT(A1,LEN(A1)-SEARCH("-",A1)),
IF(ISNUMBER(SEARCH("~*",A1)),
LEFT(A1,SEARCH("~*",A1)-1) * RIGHT(A1,LEN(A1)-SEARCH("~*",A1)),
IF(ISNUMBER(SEARCH("/",A1)),
LEFT(A1,SEARCH("/",A1)-1) / RIGHT(A1,LEN(A1)-SEARCH("/",A1)),
A1))))
Jul 12 2021 10:06 AM
Jul 12 2021 05:39 AM
Solution