SOLVED

# Set in a whole number with the help of formulas

Regular Contributor

# 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 large dataset?

Here is a attach file..

12 Replies

# Re: Set in a whole number with the help of formulas

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

# Re: Set in a whole number with the help of formulas

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

# Re: Set in a whole number with the help of formulas

It would be very complicated without VBA.

# Re: Set in a whole number with the help of formulas

Ok, Thank you so much sir

# Re: Set in a whole number with the help of formulas

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

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

# Re: Set in a whole number with the help of formulas

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

# Re: Set in a whole number with the help of formulas

Sir , can you please explain me -
what is lft rht?

# Re: Set in a whole number with the help of formulas

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)

# Re: Set in a whole number with the help of formulas

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

# Re: Set in a whole number with the help of formulas

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

# Re: Set in a whole number with the help of formulas

It is much easier to understand than the before formula. Thank you so much sir

# Re: Set in a whole number with the help of formulas

@Zan_Hanifee , you are welcome