SOLVED

Set in a whole number with the help of formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-2538010%22%20slang%3D%22en-US%22%3ESet%20in%20a%20whole%20number%20with%20the%20help%20of%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2538010%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Eif%20there%20are%20numbers%20in%20a%20column%20like%20%5B1%2C%202%2C%203%2C%2023%2C%2043%2C%2054%2C%26nbsp%3B%2034%20%2B1%2C%2045%20-%201%2C%2042%20*2%20%2C%2034%2C%2098%5D%20how%20to%20make%20this%20a%20list%20of%20whole%20numbers%20or%20how%20do%20we%20calculate%20in%20between%20if%20there%20is%20a%20large%20dataset%3F%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EPlease%20help..%3F%3F%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHere%20is%20a%20attach%20file..%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2538010%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2538078%22%20slang%3D%22en-US%22%3ERe%3A%20Set%20in%20a%20whole%20number%20with%20the%20help%20of%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2538078%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version%20(now%20a%20macro-enabled%20workbook%20since%20it%20contains%20a%20custom%20VBA%20function).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2538094%22%20slang%3D%22en-US%22%3ERe%3A%20Set%20in%20a%20whole%20number%20with%20the%20help%20of%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2538094%22%20slang%3D%22en-US%22%3ESir%2C%20is%20this%20possible%20to%20solve%20only%20function%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2538121%22%20slang%3D%22en-US%22%3ERe%3A%20Set%20in%20a%20whole%20number%20with%20the%20help%20of%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2538121%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20would%20be%20very%20complicated%20without%20VBA.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2538157%22%20slang%3D%22en-US%22%3ERe%3A%20Set%20in%20a%20whole%20number%20with%20the%20help%20of%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2538157%22%20slang%3D%22en-US%22%3EOk%2C%20Thank%20you%20so%20much%20sir%3C%2FLINGO-BODY%3E
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

@Zan_Hanifee 

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)
Solution

@Zan_Hanifee 

It would be very complicated without VBA.

Ok, Thank you so much sir

@Zan_Hanifee 

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.

 

@Zan_Hanifee 

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?

@Zan_Hanifee 

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.

@Zan_Hanifee 

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

@Zan_Hanifee , you are welcome