SOLVED

Set in a whole number with the help of formulas

Iron 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

@Excel 

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 Excel (Iron Contributor)
Solution

@Excel 

It would be very complicated without VBA.

Ok, Thank you so much sir

@Excel 

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

image.png

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

 

@Excel 

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?

@Excel 

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.

@Excel 

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))))
It is much easier to understand than the before formula. Thank you so much sir

@Excel , you are welcome

1 best response

Accepted Solutions
best response confirmed by Excel (Iron Contributor)
Solution

@Excel 

It would be very complicated without VBA.

View solution in original post