Forum Discussion
Solved
Set in a whole number with the help of formulas
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 ...
It would be very complicated without VBA.
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))
)
Sir , can you please explain me -
what is lft rht?
And Please explain this formula?
what is lft rht?
And Please explain this formula?
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)
- Is there any alternative formula?
Because i am using Microsoft Office 2019 Home and Student.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))))