Set in a whole number with the help of formulas

Regular Contributor

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..

12 Replies


See the attached version (now a macro-enabled workbook since it contains a custom VBA function).

Sir, is this possible to solve only function?
best response confirmed by Zan_Hanifee (Regular Contributor)


It would be very complicated without VBA.

Ok, Thank you so much sir


Exactly the same if you define in name manager named function as


but the file also shall be saved as macro-enabled workbook.



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?


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 ( 

Is there any alternative formula?
Because i am using Microsoft Office 2019 Home and Student.


That could be

      LEFT(A1,SEARCH("+",A1)-1) + RIGHT(A1,LEN(A1)-SEARCH("+",A1)),
      LEFT(A1,SEARCH("-",A1)-1) - RIGHT(A1,LEN(A1)-SEARCH("-",A1)),
      LEFT(A1,SEARCH("~*",A1)-1) * RIGHT(A1,LEN(A1)-SEARCH("~*",A1)),
      LEFT(A1,SEARCH("/",A1)-1) / RIGHT(A1,LEN(A1)-SEARCH("/",A1)),
It is much easier to understand than the before formula. Thank you so much sir

@Zan_Hanifee , you are welcome